Matching Existing Records in Both Systems

This article is for people who already have data in both FileMaker and QuickBooks Online that needs to match up. If you have that situation and you are using either our Starter File or your own completely custom scripts, this document will help you understand what you need to do if you have data already in both places. 

Making the Match

In order for FileMaker and QuickBooks online to sync there needs to be something on each side that matches.  We use a FileMaker field called qbo_Id to hold the QuickBooks Online record ID. If you have data in both FM and QBO then you will want to make sure that the correct QBO ID is in each record. 

Getting the QBO IDs

You'll likely want to get the data from QuickBooks Online into a FileMaker table so that you can create some relationships and scripted routines to match the records you can, and place the correct value into the QBO_id field.  There are a couple of ways of doing that.

Using the Simple Starter Solution

You can user the Simple Starter file ( fmQBO2.fm12 ) to get the IDs. Even if you don't intend to user the Simple Starter file at all, you can still set it up and use to do a full sync.  After thats done, the tables in that file will have all the records with all the data including the QBO Id. Now you have the data in a FileMaker table.

Export API

You can export your Quickbooks Online using our Export CSV API.  It will give you a csv file with enough data to do the matching.  Convert or import that csv into a FIleMaker file and now you have enough information to do the matching.  

You can use a browser or anything else that can make a GET request.  Here is the format for getting "Items".

https://exportcsv.fmqbo.com/Item.csv?apiKey=<your api key>

Note the capital "I" in "Item" and no "s".

Here is the one for getting Customers.

https://exportcsv.fmqbo.com/Customer.csv?apiKey=<your  api  key>

Matching Automatically

Frankly, it's unlikely you'll be able to match everything, but you'll probable be able to match some or most of them.  The simplest is by creating a relationship between your actual Table and the one that contains the exported from QBO data.

For example, in Items you may have a SKU field in FileMaker that almost always matches the SKU field in Quickbooks Online. If you create a realtionship between the two SKU fields you will be able to easily line up those two records and move the QBO Id over into the qbo_id field, using a Replace, or some other scripted process.

Still need help? Contact Us Contact Us