E-Commerce with FileMaker
FileMaker, Inc. got wind of one of our projects, and did a write-up of it. I think it made a good write-up - this is a conceptually pretty straight forward idea, that really shows off a couple of FileMaker’s unique features.
Our client, The Fest for Beatles Fans, has a very popular web store and wanted to upgrade it, redesign it, make it a bit splashier (spoiler alert: here’s the final result). At the same time, they had been managing their product inventory and sales using FileMaker forever. So we needed a solution that would marry these separate systems in a way that made it easy for our client to keep inventory up to date, and to manage all their orders the same way, whether they were from the web, phone, mail, or their many conventions.Â
We chose to go with Zen Cart for our Web Store for several reasons:
- No-holds-barred full-featured shopping cart. We didn’t want to compromise on this point. We wanted the best cart we could get - the kind of stuff the big guys use - like “Customers who bought this, also bought these,” “Tell a Friend,” Sale Products for special customers, Best Sellers within categories, reviews and ratings, individual accounts for order tracking, etc.Â
- Open Source with wide support and depth of add-ons. So when our client needed a special calculation created for shipping, we could turn to their vast library of plug-ins. Ultimately, we worked with a Zen Cart specialized developer, Scott Wilson, to create some of our Christmas specials.
- PHP and very customizable. The system was designed to customized, to the point that you can do an upgrade to the system without messing with your customization. And PHP is a language that FileMaker supports, and one that we work with a lot.Â
- mySQL-based (this was the clincher), so FileMaker could talk directly to it using their new ESS feature (External SQL Sources). This enables FileMaker to see any SQL database as if it was part of FileMaker, giving us all the tools and features that we use for the rest of his system.
So that’s how we ended up with Zen Cart. We’ve used this for several of our clients, and the results have been great.Â
Among other things, the solution we created for them allows them to edit product information directly within FileMaker. We created some synchronization scripts to update the online version with new prices or other information. The really cool part, is they get to see the updates as they make them, exactly as it appears on the web.
We are also posting shipping information back to the web site, and emailing the customer when the order ships. To facilitate keeping this information within FileMaker, we used NRG Ship UPS for our UPS shipments, and Endicia for our US Postal Service shipments. Both of these products were pretty quick installations. We added a feature not available in either of these products: quickly compare pricing between UPS & USPS using the FileMaker web viewer. Then load your order into the correct software depending which method you choose.Â
Feel free to stop by TheFest.com and let me know what you think.Â
If you any questions about this solution, feel free to contact us anytime.
Â
NRG Assists in Painless Fulfillment……
FileMaker Pro developer Ken d’Orionzio from Paradise Partners was recently featured alongside NRGShip customer The Fest on the FileMaker, Inc. website for their use of NRGShip UPS in creating a seamless order processing workflow. Ken also goes in…
Trackback by NRG Software, LLC. — September 25, 2008 @ 5:07 pm
This story was recently covered by EcommerceTimes. The link is:
http://www.macnewsworld.com/story/65117.html
Comment by Ken d'Oronzio — November 18, 2008 @ 11:57 am
This article about using Filemaker Pro with updating the costs in the ZenCart sounds great. I have been trying to find a Database that could connect with the ZenCart for quite a while.
I have been a FileMaker Pro user since it was version 1.0, and I still to this day use it to keep track of the Plants that we grow. Since we are a farm here in Homestead, Florida, we are trying to find ways to improve product sales during a time which we find practically no sales at all.
However, by keeping our web page up to date with the same Database that we use daily to print out our hand out price sheets, then our customers would be able to verify our prices online without having to contact us with those questions. This is a big factor, since we, as farmers, have had to cut our working hours drastically to help us conserve in expenses, as well as employees.
If you could assist us in finding a link to download this type of Database for the FileMaker Pro, then we would greatly appreciate it.
Greatly Appreciated,
Richard Naranjo
Comment by Richard Naranjo — March 16, 2009 @ 7:51 am
Hi Richard.
Thanks for your comment.
I wish I could point you to a FileMaker file doing what you’d like. At this point, it’s more of a technique than a plug and play solution. The cool thing is, though, it’s not that difficult to do if you have experience building a FileMaker Pro solution. It sounds like you built your own.
The overview of the technique is to:
1. Create an ESS connection to Zen Cart.
ESS is a new feature in FileMaker Pro 9 that allows you to connect directly to mySQL databases, among others. It uses ODBC on your server, if your using one, or on your computer if you’re using a single user solution. How to set up ODBC differs depending if you’re on a PC or Mac.
2. Add table occurrences to your Relationship Graph for each Zen Cart table you want to connect to. In your case, it sounds like the “products” table might be enough. Keep in mind that Zen Cart keeps the name of the product and the description in a separate file called “product_description”. So for identification, you might want to put that on your relationship graph, too, linked on product_id.
3. Define a relationship between the Zen Cart “products” table occurrence on the graph, and your FileMaker “products” table (or whatever it’s named). In our case, we were able to use the model number for a key. If it’s not that easy for you, you can also create a field in your FileMaker Products database called “zencart_product_id” and manually link the two using the automatically assigned product_id from Zen Cart. There are a lot of ways to define this relationship, and much will depend on how your FileMaker database is set up, and how you like to work.
4. You’ve done the hard part. The rest of the setup is defining how you want to set up your update process. It could be a daily script that loops through all the records and updates all existing Zen Cart records based on the key. It might be an update button on each record. FileMaker 10 actually opens up a couple of other possibilities - script triggers can update the Zen Cart database as soon as the Product Price changes on your FileMaker database, for example. FileMaker 10 Server, can run an update script on a recurring schedule (daily, hourly, etc.) that can have the server do the work for you.
Please continue posting, if you have other questions. Of course, we’re also happy to help build this for you. But the main purpose of this Post is to help other people who are looking for a full featured shopping cart, linked to a custom in-house system.
Comment by Ken d'Oronzio — March 16, 2009 @ 10:06 am
Hi Ken,
Thanks for the inspiration, Ive been looking for something half as good as FM6 Unlimited to get live data from filemaker onto the web in a palatable form.
I’ve had great success accessing myzen data from filemaker, But creating new records seems sketchy, they either dont turn up on Zen or they are only shown in the recent products. Is the filemaker SQL zen link to add new products this way in your experience impossible, or am i doing somethign stupid? Either way im so glad i can pump live stock availabililty onto the website again!
Comment by Adrian — August 3, 2009 @ 10:16 am
Hi Adrian.
I assume you’ve upgraded to FileMaker 9 or 10 by now? That’s really the basis of this technique. Once you’ve set up the connection using ESS, you can browse the records to see what fields are required. Creating a new record in FileMaker, creates one automatically in ZenCart. You just need to make sure all the fields are filled in properly, making the product an active product, for example. And that you have a associated Description record.
The solutions we’ve done have the ability to create new products & categories, update existing ones, downloading orders, and updating order status for the customer’s account. We’ve toyed around 2-way sync, but the client prefers our interface to the point that they’ve never even used the ZenCart interface to manage the store.
We’re considering releasing a generic FileMaker-based admin system for ZenCart. What do you think?
Comment by Ken d'Oronzio — September 2, 2009 @ 1:15 pm