FileMaker tips, techniques, and news.
Intuit’s QuickBooks Online is a powerful tool with over 4 million subscribers that allows for easy management of invoices, payments, and other accounting tasks. Many companies find themselves with both QuickBooks and FileMaker and end up having to do double data entry from one system to the next. Claris Connect is a “no-code” platform from Claris International, Inc. that allows organizations to rapidly connect multiple cloud services together, including FileMaker and QuickBooks Online. See our articles overviewing Claris Connect and integrating FileMaker with Quickbooks Online on our website. Here we will show you how quick and easy it can be to integrate your FileMaker solution with QuickBooks Online using the new Claris Connect toolset.
In this example will we be initiating our sync from FileMaker and transmitting new or updated Customer and Invoice data to our QuickBooks Online account. Claris Connect also offers the ability to begin a sync process based on actions taken in QuickBooks Online and we invite you to explore those further on your own. For this walkthrough, the first thing you want to do is create a new flow in your Claris Connect project and select the trigger as FileMaker. You can use either Cloud on On-Prem servers as an option and the configuration is very similar; here, we will be connecting with an on-prem server. Simply enter your server URL, user credentials, and filename (do not include the “.fmp12” extension) and click continue.
Note: The user you provide must have the “fmrest” extended privilege enabled in FileMaker or you will get an error here.
You will then be presented with a callback url specific for your flow and instructions on how to connect your FileMaker Pro file. Follow these instructions to connect your database to your new flow and click Save Trigger, and you are now ready to begin.
Any time you initiate a sync from FileMaker you want to be sure you are delivering all the data that you might need throughout the entire flow up front. Create a sync script in FileMaker which contains a JSON payload being delivered to Claris Connect and contains all customer data such as name, address, email, phone, and existing QuickBooks ID as well as invoice information such as amount, line item description, and line item service/product category. Once you have your file connected and your “initiate sync” script created, enable your flow and run the script for an example invoice so that your trigger step will now contain all the potential data points you may need to set through your flow.
Note: In order to test syncing to QuickBooks Online you will need an active QBO company account to connect to. At this time Claris Connect does not allow you to authenticate with Intuit Developer Sandbox companies, however you can create a 30 day free trial account with QBO and connect to that for testing purposes.
Any time we sync an invoice to QuickBooks Online we must provide the Customer’s internal QuickBooks ID number (QBID) to tie them together, which means we must verify the customer exists already in our QuickBooks account. Here you will want to go through some steps in your flow to confirm the existing customer, find a matching customer, or create a new one if necessary to acquire the customer QBID we need. First, use a “Set Variable” flow step (one of the standard utility actions) to create a new variable called customerQBID and set it to the value of the customer’s QBID you delivered in your trigger payload from FileMaker. It is entirely possible that this will be blank if it’s a customer you have never synced with Quickbooks before. Next use an “If / Then” flow step to check if your variable is empty, and if it is then you want to perform a “Get customer by query” flow step to check for a matching customer in QuickBooks using the name sent from FileMaker similar to this:
Next use another “If/ Then” flow step to see if your query returned a CustomerID. If it did, then that means we have a name match already existing in QuickBooks so you can set your customerID variable to that value and we are ready to move on. If not, use the “Create Customer” flow step to generate a new customer in QuickBooks first and then capture the customerID returned from that step into your variable. Lastly, run a “Get customer by ID” flow step using your customerID variable and confirm that it returns a result, which verifies for us that the ID we are using matches an existing customer in QuickBooks and you are good to go. If you want, you can also run an “Update Customer” flow step here to make sure that all the customer data in QuickBooks is synced and matches the information you delivered from FileMaker.
It is highly recommended that you use On Error flow steps to capture any failures that may have occurred when executing QuickBooks functions, and then delivering those errors back to FileMaker using “Execute Script” and “Stop Flow” steps to prevent unwanted changes and return issue details back to your database. You can also use the History tab in your flow to view all the request and response messages for every single step, which can be extremely beneficial when troubleshooting errors.
Note: In the screenshot above I only show a single On Error trap as an example to save space, but you will want to ensure you error trap all QuickBooks flow steps to capture any issues.
Creating invoices via Claris Connect is still somewhat limited in the amount of custom data you can deliver to QuickBooks, but if the goal is reducing double entry you can definitely automate this process. Continue your previous flow from the customer steps above by adding a new step for “Create Invoice”. You will notice that the only options here are to enter the Customer ID, which is your customerQBID variable from before, and information for invoice line items. QuickBooks will automatically populate customer data on the invoice based on the Customer provided, but the line items can be a bit trickier particularly if you might have a dynamic number of potential lines all using various product/service categories. You can perform a simple sync by previously combining the amounts of all your line items into one total and providing a generic description from FileMaker such as “Products and Services”. Doing so allows you to add those values passed in from your trigger to the Line Item options here and create a simple invoice with the proper amount.
Note: QuickBooks requires the QBID of an Item (product/service category) for any invoice lines, but unfortunately Claris Connect does not yet provide the option to query for accounts/items by name. In order to provide this information, you may need to use Intuit’s API Explorer or another method to retrieve the QBID’s of items you wish to use and store those in FileMaker so you can deliver them in your trigger.
Currently Claris Connect does not support a dynamic number of invoice line items, so if you have more than one you will need to hardcode them in your flow step. You can still make this work by pre-populating variables for a set number of line items in your JSON package and then using “If/Then” flow steps to create invoices with various numbers of line items based on a count of the total. This can get somewhat messy though, so if possible you are better off combining invoice details into a single line item at this time.
Be sure to end your flow by calling a script in your FileMaker database to return pertinent information such as the customerQBID and invoiceQBID. You will want to be sure and store these values on their related records in FileMaker for use on any subsequent syncs so as not to create duplicate data in QuickBooks.
It can be extremely rewarding to integrate your FileMaker solution with QuickBooks Online using the Claris Connect platform due to the speed and minimal coding requirements necessary. Although the flexibility may not be as complete as a native integration, for setting up a quick sync to automate tasks, eliminate double date entry, and reduce user error you could hardly ask for a more simple solution. Feel free to contact us if for any additional assistance or to discuss getting your QuickBooks system integrated with FileMaker using Claris Connect.
Did you know we are an authorized reseller for Claris Connect?
Contact us to get a competitive quote on a Claris Connect License.
Brandon is an energetic FileMaker developer with a natural ability to connect with people.