FileMaker and eCommerce Integration – Part 2
In Part 1 of our series on FileMaker and eCommerce integration we outlined the challenges many small businesses face when they go live with an online store and the new workflow challenges that can create, leading to the prospect of having to do double data entry in multiple places.
As a small business ourselves we also experienced this pain when we went live with our own online stores. Our first online store was for our oldest product fmSMS which allows you to send/receive SMS messages from the FileMaker platform – this has always had it’s own dedicated website/domain so it made sense for the store to live on the same site:
A few years ago we also started selling the first of our fmAccounting Link products for the Xero accounting platform and it made sense to sell this via a store on our main Databuzz website:
http://www.databuzz.com.au/shop
So we now currently have 2 online stores located at different domains, but we will eventually merge these together to simplify things. As both stores were built using the WooCommerce plugin for WordPress and hosted with the same web hosting provider, we knew that any integration solution for one of the stores would work for both stores.
For each order that came through the store we need to perform the following actions:
- check for an existing customer in our company FileMaker CRM and if no match is found create a new Contact record
- create a new Invoice and associated Invoice Items
- create a Payment record against the Invoice
- push a copy of the Invoice to our accounting software (Xero in our case)
- add the purchaser to a mailing list in MailChimp for future email newsletters/updates
The process starts with an email from the online store letting us know a new order has arrived:
We would then have to copy and paste all the details into our FileMaker CRM, push the Invoice to Xero using our fmAccounting Link (Xero Edition) integration, then add the customer to the appropriate mailing list in in our MailChimp account. When you’re only dealing with a couple of orders a month you can probably cope with doing things manually, but once you start to get several orders a day you are then impacted by the time it takes to do all of these takes which are also prone to data entry errors. Like us you probably start wondering if there is a better way and can this process be automated.
The good new is that it can and having helped tens of customers in the past overcome similar FileMaker/eCommerce integration challenges so we knew where to start – ESS. ESS is the External SQL Data Sources feature that was first introduced way back with FileMaker Pro v9 and allows you to establish a live two-way connection between FileMaker Pro and the top SQL data sources. ESS originally supported these SQL data sources:
- Microsoft SQL Server
- MySQL
- Oracle
FileMaker Pro v15 introduced 2 new data sources:
- IBM DB2
- PostgreSQL
Most of the popular eCommerce stores are using one of the following backend databases to drive the store:
- MySQL (used by WordPress/WooCommerce)
- SQL Server
- PostgreSQL
These are also supported ESS data sources so you can use the ESS feature to get your FileMaker CRM talking to your online store. ESS allows you to view your SQL data from within FileMaker – it appears just like normal FileMaker tables. You can create new layouts to view the data, create relationships from your FileMaker tables to your ESS tables, access the SQL data from FileMaker scripts and more (there are some limitations and it does require setting up ODBC drivers – see the Accessing External SQL Data Sources (ESS) Overview and Troubleshooting for more details.
Once you have installed the appropriate ODBC driver and setup the System DSN you can then add an ESS table occurrence to your FileMaker relationships graph, just like you would for your normal FileMaker tables:
You will need to have a basic understanding of your external SQL data source structure so you know which tables to add to your FileMaker graph and how they relate – details about WooCommerce can be found here. Once you’ve added your ESS table occurrences you can create new layouts based on each of these and start to view your online store data live in your FileMaker CRM. Here’s some examples showing some of the WooCommerce/WordPress tables that store online order details:
The above screenshots are showing data from 4 of the main tables that are used by WooCommerce to store order details:
- posts: this creates a record for each online order. This table is also used to store Product details
- postmeta: this stores a number of records related to each order, such as the billing/shipping and currency/tax details
- woocommerce_order_items: this stores line item details for each order
- woocommerce_order_itemmeta: this stores meta data about each order line item
As you can see by looking at these ESS tables in FileMaker we can see all the data about each order but it is located in at least 4 different tables, making aggregating the details each order so we can easily view the complete order challenging. We could create a number of FileMaker calculation fields to extra details about each order based on the meta_key for Orders and Order Line Items, but that would end up adding a lot of table occurrences and relationships to the graph and create another layout of unnecessary complexity.
There is a better way however that avoids all that unnecessary clutter on the graph – we can use SQL Views to create a more structured view of the SQL data we require. ESS fortunately also supports SQL Views which allow us to create a predefined SQL query that we then add to the relationship graph. We created 2 SQL views for Orders and Order Line Items to gather all the related meta data about each order and order item. When we add these to the graph and view them from a FileMaker layout here’s what we see for Orders:
and this for Order Items:
Much better! For each Order we now get 1 record showing all the Order/Customer details, and for each Order Line Item we now get 1 record showing all the details about the Order Line Item, including the Product Price and SKU (the SKU is the same one used in Xero so it’s important that we can pass that through to Xero). We can then create a relationship between these 2 ESS table occurrences to relate an Order to its Order Items by the order_id value:
and be able to view a complete WooCommerce online order in FileMaker:
We now have a FileMaker layout showing all the details for a single WooCommerce/online order, including Customer Details, Line Item Details and related Product Details. From here’s a simple case of FileMaker scripting to move the data from the ESS tables to the native FileMaker tables (first checking for any existing Customers with the same name) and from there into Xero. We add a button to the Online Order layout to push the online order into out FileMaker CRM which handles all of these tasks, saving us around 15 minutes per online order (we have customers that are getting tens of orders every day so they time savings really start to add up).
If you’re not familiar with ESS it’s important to be aware of the following:
- you will need to install ODBC drivers
- if you’re hosting your file with FileMaker Server you can install the ODBC driver once on the FileMaker Server machine for all FileMaker Pro clients to use, which makes deployment a breeze
- depending on your ESS data source and whether you are on Mac or Windows you may need to purchase the ODBC driver. There’s a full list of compatible ODBC drivers in the FileMaker Knowledge Base
- you will need to get some documentation that explains how your SQL data source tables are structured so you know which tables to add to the relationship graph
- when working with ESS tables it’s best to use a “read only” account that won’t let you edit any of the SQL data in case you accidentally edit/delete any of the online order records
- your company firewall will need to allow access to the ODBC data source port
- if you’re accessing a MySQL data source you will typically have to setup Remote Access to the MySQL database via your web hosting company (e.g. via cPanel).
In Part 3 of this series we’ll look into the options when you can’t use ESS and how you can still go about integrating your online shop with your FileMaker CRM. In the meantime if you would like to discuss integrating your online store with your FileMaker CRM please contact us.
Thanks Andrew, good stuff. What do you mean by “ESS fortunately also supports SQL Views which allow us to create a predefined SQL query that we then add to the relationship graph”? I know how to use ESS in FileMaker, but I’m not familiar with “SQL Views”, and I don’t understand how you’re using them on the FileMaker file’s relationships graph. How did you arrive at what is shown in your “ESS-Orders-View.png” and “ESS-Order-Items-View.png” screen shots? Can you post more details about that part of the process?
Hi Jeff,
Think of a View like a virtual table. You use a standard Select statement to construct the view but it allows you to combine data from multiple tables into the one virtual table, so you can grab record data from posts and posts_meta and have this displayed in the one row. The Create View statement looks something like this:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
You can use phpMyAdmin to create the View, or one of the SQL editors on the Mac like Navicat or Sequel Pro (or Windows equivalents). There’s plenty of information out there on the Internet about the Create View statement, here’s one to get started with:
http://www.w3schools.com/sql/sql_view.asp
Once you’ve created your View it appears as one of the “tables” that you can select from the ODBC data source when adding a new table occurrence to your FileMaker relationship graph.
Gotcha. It’s in the SQL db where you create the SQL query/view. Then just add that to your FM graph the same way you add the other external SQL tables using ESS. Makes perfect sense, thanks.
Hi Andrew,
Good post thank you. Can you do this using REST api instead and would it be preferable ?
https://docs.woocommerce.com/document/woocommerce-rest-api/
http://v2.wp-api.org/
Hi Hamish,
In Part 3 I’m going to be looking at Webhooks and API calls etc, so yes this is possible. ESS and API calls both have their pros and cons – hopefully should have Part 3 online in a few more weeks. If you can’t use ESS/ODBC (e.g. it’s blocked) then Webhooks/API calls would be the fallback option.
regards,
Andrew