FileMaker and Xero Integration Webinar
Are you using FileMaker to run your business and also Xero for your accounting software? Would you like to see how they can be integrated together so you can streamline your workflow and protect your sensitive Xero data at the same time?
Join Andrew Duncan from Databuzz and David Borgnis, Business Development Manager, APAC at FileMaker Inc. for an informative and explorative 60 minute presentation. The webinar details are:
Date: Thursday, 3 November, 2016
Time: 12 noon (AEST)
Duration: 60 minutes
Register now
Integrating FileMaker and Xero allows you to remove double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and Xero. Xero was recently named the most-loved accounting software for a second year in a row, beating MYOB and Intuit Quickbooks, scoring five stars across seven criteria. It was named the preferred accounting software of Australian accountants – as you can see from the chart below it was by a significant margin:
In the webinar will demonstrate:
- how to setup the authentication between FileMaker and Xero without giving your staff direct access to Xero
- how to download data from Xero into FileMaker (Chart of Accounts, Tax Rates, Inventory Items etc)
- how to push a Sales Invoice from FileMaker to Xero (and create a Contact in Xero at the same time)
- how to push a Payment for a Sales Invoice from FileMaker to Xero
We hope you can join us for the webinar – if you have anything you would like addressed in the webinar please leave a comment below. For further details on fmAccounting Link (Xero Edition), our FileMaker solution for integrating with Xero please visit the fmAccounting Link (Xero Edition) product page.
Update: thanks to everyone who was able to attend the webinar. If you weren’t able to make the live webinar the recording is now available here.
FileMaker and eCommerce Integration – Part 3
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. In Part 2 we showed how you eliminate any double data entry by making your online store visible to your FileMaker solution by using the ESS (External SQL Data Sources) feature of FileMaker Pro/Server, allowing users to see online orders on a normal FileMaker layout.
Not all FileMaker Pro solutions will be able to take advantage of the ESS feature however for a variety of reasons, including:
- your online store doesn’t use a supported ESS data source
- your online store hosting provider doesn’t allow remote SQL access
- your company firewall won’t allow ESS access to the hosting provider
- you wish to avoid the expense of purchasing ODBC drivers
There are a number of alternatives to having a “live” view of your online orders using ESS which can be broadly defined as either a push or pull approach, whereby data is either pushed from the online store to your FileMaker solution or pulled/downloaded from your online store by your FileMaker solution. Like all solutions there are pros and cons to each approach and the particulars of how your FileMaker solution is hosted will determine which options are available to you.
In the following examples we’re going to be using the WooCommerce plugin for WordPress as it offers both a push and pull API and is a popular eCommerce store, powering over 37% of all online stores at the time of writing.
Push Online Orders to FileMaker – the push approach is usually considered the most optimal solution as it is only invoked when there is new data to transfer, thus reducing the number of unnecessary requests to the online store to check for new orders. In WooCommerce you implement a push solution through the use of Webhooks – Webhooks are are very common form of server event notifications which trigger an action by sending a request to a URL that you specify. WooCommerce has a number of Webhooks that you can activate, for example each time a new order is created.
We’ve helped many customers over the years implement a Webhook solution that works as follows:
- a new order is created on the customer’s online store
- a Webhook is triggered which sends the details of the new order as JSON encoded data to a URL (a PHP page) on the customer’s FileMaker Server
- the PHP file uses the FileMaker PHP API to convert the JSON encoded data into a new customer record, order record and order line items
The customer also receives an email for each new order, which prompts them to open their FileMaker database and review the order details and ship any required products then push the invoice to their accounting software (Xero, MYOB etc). The customer hasn’t had to do any double data entry or query the online store for new orders – everything is pushed through as it happens. It does require the customer to have FileMaker Server with Custom Web Publishing enabled and allow external access to the PHP file hosted on their server.
Pull Online Orders to FileMaker – if the push approach is not a viable option WooCommerce also offers a REST API that you can also use with FileMaker Pro. The WooCommerce REST API allows you to query your WooCommerce online store and retrieve details about customers, orders, products etc, as well as being able to push data from FileMaker to WooCommerce if necessary. A typical solution using the WooCommerce REST API works as follows:
- customer receives an email notification from the WooCommerce store about a new order
- customer then clicks a button in their FileMaker solution to query the online store for any new orders since a timestamp (typically the last time they checked for new orders)
- FileMaker sends a REST API request for any new order details and receives a JSON encoded response from the WooCommerce REST API with details about each order
- the response is parsed out to create new customer, order and order item records
Once again the customer has been able to eliminate any double data entry and simply has to click a button in FileMaker to get all the new order details.
As we have illustrated in this series there are typically a number of options available when it comes to integrating your online store with your FileMaker CRM, whether that’s a direct live view using ESS or having new orders pushed or pulled into your FileMaker solution. With an integration into your accounting software such as Xero, MYOB AccountRight or MYOB Essentials you can completely eliminate any double data entry for the entire order and sit back and watch as the data flows from your online store to FileMaker and then to your accounting software.
If you would like to discuss integrating your online store with your FileMaker CRM please get in touch for a free initial consultation.
FileMaker and eCommerce Integration – Part 1
fmAccounting Link (Xero Edition) Update
Over the past few months we’ve been busy adding new examples and features to our fmAccounting Link (Xero Edition) solution based on customer requests and our own list of features we’d like to see. We now have new examples for the following Xero API endpoints:
- Purchase Orders
- Receipts
- Expense Claims
- Users
- Currencies
- Branding Themes
- Credit Notes
We’ve also added the ability to download the Xero Invoice PDF as well as the Invoice Online URL – this allows you to include these in emails that you send to your customers (e.g. a ‘pay now’ link). We’re currently working on Overpayments and then will tackle Prepayments. If there’s any examples that we don’t currently have that you would like to see please get in touch and let us know.
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.
Wells Fargo Now Sharing Data with Xero
Last week Xero announced that it had entered into a data sharing agreement with Wells Fargo, one of the top US banks. Xero users will now be able to get bank feeds directly from Wells Fargo into Xero without having to manually export and import files. Xero is the first major technology company to partner with Wells Fargo to help their common customers share data between their two platforms.
Xero recently surpassed more than 700,000 subscribers around the world and North America is their fastest growing market with more than 62,000 subscribers. In the UK, with the addition of Barclays earlier this year, Xero now has five of the top six banks already running direct feeds to Xero giving them coverage for over 90% of UK’s small businesses.
We’ve noticed an increase in the number of enquires about FileMaker and Xero integration from North America over the past 12 months, particularly from customers looking for an alternative to QuickBooks. We can expect more major US banking integrations in the coming months, similar to what we have seen in Australia, New Zealand and the UK.
If you’re looking for a “do it yourself” option for FileMaker Xero integration check out fmAccounting Link (Xero Edition) – it includes examples for authenticating, uploading and downloading Contacts, Invoices, Payments and more. It’s 100% unlocked allowing you to integrate the functionality into your existing FileMaker solution, helping your company save time and money by removing the double data entry between FileMaker and Xero.
This article in Inc. magazine has an interesting perspective on what the Wells Fargo Xero agreement will mean for small business customers in the US.
fmAccounting Link (Xero Edition) Update
We’ve just released an update to fmAccounting Link (Xero Edition) – v1.86. You can read about the changes in the version history notes but here are some of the highlights:
- added a <Reference> field for Invoices POST calc so you can set the customer reference number
- updated the uploading of an Invoice to Xero to display any ApiException errors if present (more meaningful error messages)
- Accounts: added portals to Accounts Form layout showing related Invoice Items, Bill Items and Payments
- Invoices: added script to GET the Xero online invoice url (the URL that customers can click on the Xero Invoice to make an online payment)
- Invoices: added script/button to update the status from Xero for the current Invoice. This allows you to periodically check the status of any outstanding invoices from FileMaker to see if they have been paid in Xero, which is particularly helpful when you only record payments in Xero
- Invoices: added script to GET the Xero Invoice PDF. This lets you download the same PDF that Xero generates to a FileMaker container field, allowing you to file it or email it etc.
- Invoices: added a Mark as Sent flag for the Invoice and added SentToContact to XeroInvoicePOSTCalc field. If you’re using Xero’s new email reminders feature this is required to let Xero know the Invoice has been sent to the customer
This update is once again free to all existing customers – just download the latest version from the same link from your order email. We’re working on the next round of changes which will include examples for the following Xero API endpoints:
- Expense Claims
- Purchase Orders
The Benefits of FileMaker Integration with your Accounting Software
The start of a new financial year is a great time to assess how your business has performed over the previous 12 months and start making plans for the next financial year. It’s also a good time to assess your business processes and put in place systems that help streamline the way your business works and contribute to the bottom line in the next fiscal year.
For Australian businesses the new financial year starts in a few weeks (1 July), for New Zealand and the United Kingdom is was on 1 April, for Canada it was 1 January and for the United States it was 1 October.
If you’re planning to make any changes to your accounting software and systems the start of a new financial year is the perfect time to implement these changes. You can use the weeks (and months) leading up to the new financial year to decide on any changes you wish to make, then develop and test these changes during the end of the current financial year before going live at the start of the new financial year. If you can’t wait for the start of a new financial year the start of a new quarter is usually the next best time.
Some of the questions you might wish to consider include:
- does my Customer Relationship Management (CRM) software “talk” to my accounting software?
- are my staff doing double data entry of Customers, Invoices, Payments etc in my CRM and my accounting software?
- if I could eliminate any double data entry would that free up my staff to work on more productive tasks?
The ability to integrate CRM software with Accounting software has become much easier thanks to the rise of APIs. An API is an Application Programming Interface that a company makes available to other software developers – essentially it is a series of instructions for what you need to do to get your software talking to their software. In the small business space accounting software vendors such as Xero, MYOB and Saasu all provide well documented and reliable APIs that you can use to integrate your CRM software with their accounting software.
Having an API allows the accounting software applications to create an ecosystem of “add ons” that extend the functionality of usefulness of the accounting software, for example having transactions from a point of sale system automatically uploaded to the accounting software at the end of the day or being able to accept online payments for an invoice.
If you’re using FileMaker as your CRM application to track your customers, sales, invoices and payments there are a number of advantages of integrating it with your accounting software, including:
- no more double data entry in FileMaker and the accounting software. Enter the invoice once in FileMaker, click a button and the invoice is pushed to your accounting software in a few seconds
- less errors between your CRM system and your accounting system. Being able to push invoices, payments etc from your main system to your accounting system means there is less likelihood of data entry errors between the 2 systems
- free up staff time for more productive work. Rather than having staff spend countless hours each week doing double data entry they can be freed up for more productive (and enjoyable) work, such as following up on sales leads, customer service, marketing etc.
- it also allows staff to push data to the accounting software without giving them direct access to the accounting software
Having implemented a number of FileMaker/CRM and accounting integrations over the past 3 years we’ve seen the benefits first hand. One customer that processed hundreds of sales every fortnight has now freed up 1-2 hours of time each day for staff that were responsible for the double data entry of invoices. Now they enter the sale once into FileMaker, click a button and a few seconds later get confirmation that the invoice has been uploaded.
We’ve also integrated FileMaker with our accounting software in our business. Before the integration the workflow for processing an online sale was something like this:
- customer makes an online purchase from our webstore and a notification is sent us via email with the order details
- Databuzz staff manually create a new order in our FileMaker CRM system
- Databuzz staff then manually create the same invoice in our accounting software
- Once the invoice has been created we then manually add the payment details
Now that we have integrated our FileMaker CRM with our online webstore and our accounting software the process is this:
- customer makes an online purchase from our webstore and a notification is sent us via email with the order details
- Databuzz staff review the order in our FileMaker CRM which can show webstore orders live, then click a button to push that sale to our CRM and online accounting software, including the Customer, Invoice and Payment details
That saves around 5-10 minutes of data entry time per order – multiple this by tens or hundreds of orders and you can see the time savings add up quickly.
If you would like to discuss integrating your FileMaker solution with either Xero, MYOB AccountRight or MYOB Essentials please contact us for a free initial consultation to discuss your requirements. We also have a number of products which allow you or your in-house/external FileMaker developer to do the integration yourself:
- fmAccounting Link (Xero Edition)
- fmAccounting Link (MYOB AccountRight Edition)
- fmAccounting Link (MYOB Essentials Edition)
A typical integration usually takes around 4-8 hours – in that time we can have your FileMaker system authenticating with your accounting software, uploading Contacts, Invoices, Payments and more. Imagine how much time and money your business could save by integrating your CRM with your accounting software.
fmAccounting Link (Xero Edition) and FileMaker Pro v15
We’re pleased to report that fmAccounting Link (Xero Edition) v1 is compatible with FileMaker Pro/Pro Advanced v15 that was released today. We haven’t encountered any issues so far in our testing and have been able to authenticate, download from Xero to FileMaker and upload from FileMaker to Xero successfully.
If you encounter any issues with fmAccounting Link (Xero Edition) v1 and FileMaker Pro v15 please let us know.