Coming Soon – fmEcommerce Link (WooCommerce Edition)

Earlier this year we published a series of articles about FileMaker and eCommerce Integration, highlighting our use of External SQL Data Sources (ESS) to make our online store orders visible in our internal FileMaker CRM. We’ve been using this method of integration successfully for many years and it has saved us countless hours by not having to manually re-enter online orders in our main FileMaker business app and our accounting software (Xero).

That was until recently when we started getting this error message every time we navigated to the online orders layouts:odbc-error

Suddenly we could no longer see our online order records! Everything was still working on the website and orders were still being submitted successfully (we receive an email for each order) so we started troubleshooting this to try and get to the bottom of it. After many frustrating hours we still could not establish the ODBC connection to our server, so we made contact with our web host and opened a ticket explaining our issue and describing how this had previously worked fine for many years. We were then informed that they had disabled remote MySQL access for security reasons and there were no exceptions – talk about a great way to annoy your customers by switching off access and not informing your customers about this!

It was still possible to connect but you had to create an SSH tunnel first – we then wasted another couple of days setting up the SSH tunnel which would work but then drop out, and we had issues with automating this so it could run when the server wasn’t logged in. After many days of frustration and lost productivity I decided to abandon the SSH tunnel efforts as it was proving too unreliable and too look at other options. Databuzz specialises in FileMaker integration and having recently written an article on working with eCommerce APIs and Webhooks I revisited my article and knew what needed to be done.

Our website stores are built with WooCommerce, a popular plugin for WordPress that powers over 39% of all online stores. WooCommerce offer both a push and pull API so you can have new orders automatically pushed to a server, and also download new orders on demand (e.g. get all new Orders today). The push option is the more complicated of the two options as it requires FileMaker Server, Customer Web Publishing, PHP pages and Webhooks to be setup, so we decided to focus initially on the WooCommerce REST API as that allows us to query WooCommerce for any new Orders and download the data directly into FileMaker. This can be run regardless of whether the file is hosted with FileMaker Server or just using FileMaker Pro, and we can also setup server side schedules to run each night and download new data. The REST API also allows us to push data from FileMaker to WooCommerce, such as Product updates, which is something customers have requested in the past.

Fast forward a few months and we are in the final stages of development and testing of our latest product – fmEcommerce Link (WooCommerce Edition):

preview-home

fmEcommerce Link is our solution to connecting FileMaker to WooCommerce when you can’t make a direct ESS/ODBC connection – you use the fmEcommerce Link file to query your WooCommerce store for new orders, product inventory changes, new customers and also to push data back to WooCommerce such as new Products. You can link the fmEcommerce Link file to your existing FileMaker solution to push orders from WooCommerce into your main FileMaker business app, or recreate the same functionality inside your FileMaker business app as the fmEcommerce Link will be 100% unlocked for you to explore.

Here’s some screenshots showing data that we have downloaded from our test WooCommerce site:

preview-order-details

preview-orders-list

preview-products-list

I’ll be posting some videos demonstrating the core functionality of fmEcommerce Link over the coming days and would love to hear any feedback from existing FileMaker/WooCommerce users about any features you would like to see in the product – just leave a comment below.

fmAccounting Link (Xero Edition) Updates – Reports and Invoice Attachments

We love hearing back from our customers about new features/examples they would like to see in the core fmAccounting Link (Xero Edition) file. We recently had some requests for examples showing how to upload files/attachments from FileMaker to an Invoice and the ability to download a Report from Xero to FileMaker.

We’ve had the Files API on our list of future enhancements as well as the Reports endpoint for a while, so we took this opportunity to dive in and get some of the basic functionality for these developed so our customers can start using them now.

For the Reports endpoint we’ve started with the Trial Balance report – we’ve created a new Reports module in the fmAccounting Link file that we will add to over time with additional reports that are available via the Xero API. The Trial Balance report lets you specify the date to run the report as at, and then the report is downloaded into the fmAccounting Link (Xero Edition) file and looks like this:

trial-balance-report

For the Invoice Attachments we’ve added a new TAB to the Invoice details layout allowing you to select a file to upload to Xero and attach it to the current Invoice:

invoice-attachments

You can upload up to 10 attachments (each up to 3mb in size) per invoice, once the invoice has been created in Xero. To keep things simple we’re not storing the selected file in a container field – we’re just storing the path to the file (you can change this behaviour in your version of the fmAccounting Link file). You can upload the common file formats, such as PDF, JPG, Word, Excel and PNG files, and you can specify for each attachment whether to include that in the Online Invoice so that the attachment appears when a user clicks on the online invoice link. A full list of file types that you can upload is available on the Xero website.

One limitation of the Attachments API at the moment is that you cannot currently delete an attachment in Xero from FileMaker – you will need to login to Xero to delete any attachments that have been uploaded to an Invoice.

We’re working on the Prepayments endpoint next and should have that finished in December, and then we’ll start adding more of the Reports that are available via the Xero API. 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. Further details on fmAccounting Link (Xero Edition) including the full list of examples are available on the fmAccounting Link (Xero Edition) product page.

FileMaker and Xero Integration Webinar Recording

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:

afr-chart-1-1_800x480_acf_cropped

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.

Are you still processing credit card payments manually?

american-express-89024_640

Do you process credit card transactions manually and wonder if there is a better way? The good news is that there is a better more automated way that can integrate with your custom FileMaker solution and save your staff time and your business money.

Chances are if you’re a small business that sells goods or services your customers are going to want to pay by credit card, and having options and making it easy for customers to pay your invoices is a good thing for your business. Once you start accepting credit card payments however you need to comply with the PCI DSS (Payment Card Industry Data Security Standard) to help protect card data and prevent payment data theft. Small businesses are increasingly at risk for payment data theft – nearly half of cyberattacks worldwide in 2015 were against businesses with less than 250 workers according to cybersecurity firm Symantec.

The easiest way to protect against data breaches is to not store card data at all, however that isn’t always practical, especially if you’re selling an ongoing service that requires ongoing payments (e.g. a monthly subscription service). Whilst you definitely should not be storing unencrypted credit card data in your FileMaker solution that any employee can access, you can take advantage of encryption and tokenisation technologies that allow you to store an “alias” or token in your FileMaker solution and use that to processes future charges. Here’s how it works:

  1. customer provides credit card details to pay an Invoice
  2. you send an encrypted HTTPS request to a PCI DSS compliant credit card gateway who store the credit card details (as they are PCI DSS compliant) and send you back a token and a masked version of the credit card number (e.g. 512345…346)
  3. you store this token in your FileMaker transaction record. As this is a token and not a real credit card number it’s completely useless if stolen. You can also store the masked version of the card number in case you need to confirm with the customer which card number you are charging
  4. when you need to process a payment in the future you make another HTTPS request to the credit card gateway requesting a payment and referencing the token
  5. the credit card gateway returns a response indicating whether the transaction was processed successfully or if there was an error (e.g. declined, insufficient funds etc)

This can all be automated in a FileMaker solution allowing staff to process a payment or tokenise a card at the click of a button. We’ve worked with many PCI DSS compliant credit card gateways such as Stripe, eWay, BPOINT and Authorize.Net to help customers automate the process of processing credit card payments securely in their FileMaker solutions. If you’re currently storing credit card numbers in your FileMaker solution and would like to tokenise these we can also help you batch process these.

If you would like to discuss implementing a secure credit card processing system for your FileMaker solution plesae get in touch for a free initial consultation. For more information on how you can protect card data the Payment Card Industry has a number of guides for small businesses, including:

Update: we’ve published a short video demonstrating how you can use FileMaker to tokenise a credit card and then process a transaction by referencing that token.

FileMaker and eCommerce Integration – Part 3

puzzle-1152793_1280

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

FileMaker and eCommerce Integration – Part 2

FileMaker and eCommerce Integration – Part 3

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

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

FileMaker and eCommerce Integration – Part 1

puzzle
Many of our small business customers have both a physical presence and an online presence when it comes to selling their goods and services. They  might have:

  • a physical retail store where customers can come and purchase goods in person
  • a mail order store where they take orders by mail, phone and fax and send the goods to the purchaser once they have paid
  • an online store which takes orders, processes the credit card transactions and notifies the business to fulfil the orders
  • a popup store/market store that runs every month or for short periods of time

When the business is starting up, or when they are adding a new method of selling goods and services, the tendency is for each of these stores to operate as their own silo. This ultimately leads to a lot of data duplication/double entry in multiple systems as the business deals with orders coming in from multiple store presences. Quite often we see the following workflow:

  • physical and mail order/phone orders are processed in the central office FileMaker CRM (Customer Relationship Management) solution
  • online/eCommerce orders are processed via the online store attached to the customer’s website. The business is notified of new orders via email and these are then entered into the central office CRM system by customer service staff who then notify the accounts department to create an accounting entry in Xero or MYOB etc.

We wouldn’t generally recommend to customer’s that they use FileMaker to run their online eCommerce store, so there’s no problem with having the physical presence and the online presence separate (and it’s highly advisable from a security standpoint). However this leads to the problem of scattered information and ad hoc processes – wouldn’t it be better if all the information was in one place and you could see all the physical orders and online orders from the central FileMaker CRM? Could you save time and increase productivity by not having to manually re-enter all the online orders in both the main FileMaker CRM as well as your accounting software?

We’ve been helping customers for many years now integrate their online stores with their FileMaker CRM solution so they can view everything in the one place and automate the transfer of online orders into their office CRM, and then push that into their accounting software without having to re-type anything.

Here’s a list of online orders from a popular shopping cart as it appears in the web browser admin view:

WooCommerce FM List 2

 

Wouldn’t it be great if you could also view these same orders live in FileMaker:

WooCommerce FM List

and instead of re-entering each order manually, including the line item details:

WooCommerce Order WP

you could see all the online order details live in FileMaker:

WooCommerce FM Details

and with the click of a button you can push the order details from the online eCommerce system into your FileMaker CRM and apply some business rules at the same time, such as:

  • check for an existing customer in the 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 your accounting software such as Xero or MYOB
  • add the purchaser to a mailing list in MailChimp or Campaign Monitor etc
  • send a thank you email to the customer with a discount coupon

Here at Databuzz we recently faced the same challenges that we’ve been helping our customers with for many years – we opened our first online store last year and have been working on integrating this with our internal FileMaker CRM every since. In Part 2 and 3 of this series we’ll cover some approaches to eCommerce and FileMaker integration based on our experiences as a small business that uses FileMaker and Xero to run their business.


FileMaker and eCommerce Integration – Part 2

FileMaker and eCommerce Integration – Part 3

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.