FileMaker and eCommerce Integration – Part 2

shopping-cart-3-1546160-1280x960

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:

http://www.fmsms.com/shop/

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:

New Customer Order

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:

ESS Table Occurrences

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:

ESS Orders

ESS Orders Meta

ESS Order Items

ESS Order Item Meta

The above screenshots are showing data from 4 of the main tables that are used by WooCommerce to store order details:

  1. posts: this creates a record for each online order. This table is also used to store Product details
  2. postmeta: this stores a number of records related to each order, such as the billing/shipping and currency/tax details
  3. woocommerce_order_items: this stores line item details for each order
  4. 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:

ESS Orders View

and this for Order Items:

ESS Order Items View

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:

ESS Relationship

and be able to view a complete WooCommerce online order in FileMaker:

WC ESS Order

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.


FileMaker and eCommerce Integration – Part 1

FileMaker and eCommerce Integration – Part 3

The Benefits of FileMaker Integration with your Accounting Software

integration

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:

  1. customer makes an online purchase from our webstore and a notification is sent us via email with the order details
  2. Databuzz staff manually create a new order in our FileMaker CRM system
  3. Databuzz staff then manually create the same invoice in our accounting software
  4. 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:

  1. customer makes an online purchase from our webstore and a notification is sent us via email with the order details
  2. 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:

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.

Databuzz releases fmAccounting Link (MYOB Essentials Edition) – Integrate FileMaker Pro and MYOB Essentials Accounting Software

Sydney, Australia – May 17, 2016 – Databuzz today announced fmAccounting Link (MYOB Essentials Edition), a FileMaker solution that integrates with the MYOB Essentials Accounting Software.

fmAccounting Link (MYOB Essentials Edition) allows you to upload and download data between your FileMaker solution and MYOB Essentials, the easy online accounting that has everything your clients need to take care of business, including payroll. fmAccounting Link (MYOB Essentials Edition) removes double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and MYOB Essentials.

fmAccounting Link (MYOB Essentials Edition) is completely unlocked allowing you to integrate it into your FileMaker solution. You can copy and paste examples showing you how to authenticate with the MYOB Essentials API and upload Contacts, Invoices, Payments and more at the click of a button.

fmAccounting Link (MYOB Essentials Edition) features include:

  • works with FileMaker Pro v12, v13, v14 and v15
  • completely unlocked
  • can be hosted by FileMaker Pro or FileMaker Server
  • works with Macintosh and Windows

“Many small businesses use FileMaker Pro to track their Contacts and generate Invoices and MYOB Essentials for accounting,” said Andrew Duncan, Director of Databuzz. “You no longer need to do any double data entry in both applications – with fmAccounting Link (MYOB Essentials Edition) you can push and pull data between FileMaker and MYOB Essentials at the click of a button.”

fmAccounting Link (MYOB Essentials Edition) includes examples for the following MYOB Essentials API endpoints:

  • Businesses: select from all available MYOB Essentials Businesses that you have access to
  • Contacts: download and upload Contacts (Customers and Suppliers)
  • Invoices: download and upload Invoices (including Invoice line items)
  • Items (Products): download and upload Items (Products price list)
  • Payments: upload Payments against an Invoice
  • Account Codes: download Account Codes from MYOB Essentials
  • Tax Types: download Tax Types from MYOB Essentials

 Availability, Pricing, and Compatibility

fmAccounting Link is available in a number of licenses: Company, Vertical Solution and Developer. It is available now from the Databuzz website at http://www.databuzz.com.au/fmaccounting-link-myob-essentials-edition/. Workgroup Licenses start at AUD $495.00. fmAccounting Link (MYOB Essentials Edition) requires FileMaker Pro v12, v13, v14 or v15 and a subscription to MYOB Essentials.

Media/Customer Contact:

Andrew Duncan

Phone: +61 418 468 103

sales@databuzz.com.au

About Databuzz: Databuzz is a long standing member of the FileMaker Business Alliance. We have been developing and deploying FileMaker solutions for clients in Australia and internationally since 1999. Our clients are individuals, small-medium businesses, government agencies and multi-national corporations. Databuzz was founded by Andrew Duncan, a Certified FileMaker 14 Developer. For more information please visit our website at http://www.databuzz.com.au.

###

FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners.

fmAccounting Link (MYOB AccountRight Edition) and FileMaker Pro v15

We’re pleased to report that fmAccounting Link (MYOB AccountRight 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 AccountRight to FileMaker and upload from FileMaker to AccountRight successfully.

If you encounter any issues with fmAccounting Link (MYOB AccountRight Edition) v1 and FileMaker Pro v15 please let us know.

fmAccounting Link (MYOB Essentials Edition) Preview Video

We’ve just uploaded our first preview video for fmAccounting Link (MYOB Essentials Edition). This video demonstrates the following:

  • authenticating against the MYOB Essentials API
  • downloading a list of available MYOB Essentials Businesses
  • downloading Inventory Items, Tax Types and Chart of Accounts from MYOB Essentials
  • uploading a Contact from FileMaker Pro to MYOB Essentials
  • uploading an Invoice from FileMaker Pro to MYOB Essentials
  • uploading a Payment from FileMaker Pro to MYOB Essentials

You can watch the video below or directly on YouTube via this link.

We should be releasing fmAccounting Link (MYOB Essentials Edition) in the next couple of weeks – please Contact Us if you have any questions in the meantime.

Databuzz releases fmAccounting Link (MYOB AccountRight Edition) – Integrate FileMaker Pro and MYOB AccountRight Accounting Software

Sydney, Australia – April 12, 2016 – Databuzz today announced fmAccounting Link (MYOB AccountRight Edition), a FileMaker solution that integrates with the MYOB AccountRight Accounting Software.

fmAccounting Link (MYOB AccountRight Edition) allows you to upload and download data between your FileMaker solution and MYOB AccountRight, the powerful accounting software with business management capabilities that allows you to work off or online. fmAccounting Link (MYOB AccountRight Edition) removes double data entry and human errors saving your company significant time, money and hassle by automating the exchange of data between FileMaker and MYOB AccountRight.

fmAccounting Link (MYOB AccountRight Edition) is completely unlocked allowing you to integrate it into your FileMaker solution. You can copy and paste examples showing you how to authenticate with the MYOB AccountRight API and upload Contacts, Invoices, Payments and more at the click of a button.

fmAccounting Link (MYOB AccountRight Edition) features include:

  • works with FileMaker Pro v12, v13 and v14
  • completely unlocked
  • can be hosted by FileMaker Pro or FileMaker Server
  • works with Macintosh and Windows
  • works with MYOB AccountRight running in the Cloud or on the Desktop (online and offline)
  • works with MYOB AccountRight Live 2013, 2014, 2015 and 2016

“Previous integrations between FileMaker and MYOB AccountRight have involved manual exports and imports of multiple text files or a Windows only ODBC connection,” said Andrew Duncan, Director of Databuzz. “These are now a thing of the past – with fmAccounting Link (MYOB AccountRight Edition) you can push and pull data between FileMaker and MYOB AccountRight at the click of a button.”

fmAccounting Link (MYOB AccountRight Edition) includes examples for the following MYOB AccountRight API endpoints:

  • Company Files: select from all available MYOB AccountRight Company Files that you have access to
  • Contacts: download and upload Contacts (Customers and Suppliers)
  • Invoices: download and upload Invoices (including Invoice line items)
  • Items (Products): download and upload Items (Products price list)
  • Payments: download and upload Payments against an Invoice
  • Employees: download and upload Employees
  • Account Codes: download Account Codes from MYOB AccountRight
  • Tax Codes: download Tax Codes from MYOB AccountRight
  • Categories: download Categories from MYOB AccountRight

Availability, Pricing, and Compatibility

fmAccounting Link is available in a number of licenses: Company, Vertical Solution and Developer. It is available now from the Databuzz website at http://www.databuzz.com.au/fmaccounting-link-myob-accountright-edition/. Company Licenses start at AUD $495.00. fmAccounting Link (MYOB AccountRight Edition) requires FileMaker Pro v12, v13 or v14 and MYOB AccountRight Live 2013, 2014, 2015 or 2016.

Media/Customer Contact:

Andrew Duncan

Phone: +61 418 468 103

sales@databuzz.com.au

http://www.databuzz.com.au

About Databuzz: Databuzz is a long standing member of the FileMaker Business Alliance. We have been developing and deploying FileMaker solutions for clients in Australia and internationally since 1999. Our clients are individuals, small-medium businesses, government agencies and multi-national corporations. Databuzz was founded by Andrew Duncan, a Certified FileMaker 14 Developer. For more information please visit our website at http://www.databuzz.com.au.

###

FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners.

fmAccounting Link (MYOB AccountRight Edition) Preview Video

We’ve just uploaded our first preview video for fmAccounting Link (MYOB AccountRight Edition). This video demonstrates the following:

  • authenticating against the MYOB AccountRight API
  • downloading a list of available AccountRight Company Files
  • selecting the AccountRight Company File and entering the credentials for the selected Company File
  • uploading a Contact from FileMaker Pro to AccountRight
  • uploading an Invoice from FileMaker Pro to AccountRight
  • uploading a Payment from FileMaker Pro to AccountRight
  • downloading Chart of Accounts, Tax Codes, Categories and Inventory Items from AccountRight to FileMaker

The video also demonstrates how you can integrate with MYOB AccountRight (a Windows only application) from a Mac OS X computer.

You can watch the video below or directly on YouTube via this link.

We should be releasing fmAccounting Link (MYOB AccountRight Edition) in the next couple of weeks – please Contact Us if you have any questions in the meantime.

Coming Soon – fmAccounting Link (MYOB AccountRight Edition)

Ever since we released our FileMaker to Xero integration solution – fmAccounting Link (Xero Edition) – in 2014 we’ve been planning to release a version for the MYOB Accounting platform. We ended up focussing solely on the Xero version for most of 2015, adding new features as Xero expanded their API and in response to customer requests.

We’ve finally been able to focus some resources on the MYOB version and while we’re not quite ready to release it to the general public we’re very happy with the progress that we’re making and wanted to share some details with you. We’re planning on releasing the following versions:

  1. fmAccounting Link (MYOB AccountRight Edition) – this will be the first version released and will work with the MYOB AccountRight Live API. There are a number of versions of AccountRight available, including Standard, Plus and Premier – as long as you’re using AccountRight Live (2015.x versions or later) you will be able to use our solution to integrate FileMaker with MYOB. Development of this version is largely completed – we still need to do more testing and create the documentation and videos etc, but we’re planning for a March 2016 release.
  2. fmAccounting Link (MYOB Essentials Edition) – this will be the second version released and will work with the MYOB Essentials Accounting API. MYOB Essentials is MYOB’s browser based accounting solution. We don’t have a release date for this at the moment but we’re hoping we can leverage a lot of the development for the AccountRight edition and have this ready in the April/May timeframe.

The initial release will allow you to perform the most requested integration functions between FileMaker and MYOB, including:

  • upload a Contact from FileMaker to MYOB
  • download Contacts from MYOB to FileMaker
  • upload an Invoice from FileMaker to MYOB
  • upload a Payment from FileMaker to MYOB

fmAccounting Link (MYOB AccountRight Edition) will work on both Windows and Macintosh platforms and work with FileMaker Pro v12, 13 and 14. It will be completely unlocked so you can integrate it into your existing FileMaker solution in whatever way suits you or your developer best.

We’re very excited about this release as FileMaker MYOB integration has always been clunky and involved lots of exporting and importing of .tab files or working with their ODBC driver which was Windows only and had it’s own set of limitations. The new MYOB APIs allow for true integration without having to export or import any data and can run on both Windows and the Macintosh platforms – in fact you can use your Mac to upload invoices to your MYOB AccountRight file, even though there is no native Mac OS X version of MYOB AccountRight!

Unfortunately there’s no developer API for AccountEdge, the Mac version of MYOB so we can’t do the same integration as we can with AccountRight and Essentials. Hopefully MYOB will release API access to AccountEdge in the future.

Please contact us if you have any questions about fmAccounting Link (MYOB AccountRight Edition) – if you would like to be notified when we release fmAccounting Link (MYOB AccountRight Edition) please join our mailing list here.