Connect to Your Own File
Before you begin we recommend you read the article on Backup and Demos. It is very important.
If you want to use your own solution with the fmQBO you have two options. The first is to use the all the scripts and logic written into the sync engine in the fmQBO2.fmp12 Starter File. The second is to do everything from scratch as laid out in the Advanced Documentation.
Before you begin, you should read the article on Matching Existing Data, if you have data in both FileMaker and in QBO that needs to line up.
Use fmQBO2.fmp12 Starter File
With this method, you will use all the sync logic that we already have built for Customers, Items, and Invoices. You will simply re-point table occurrences, and re-connect some script steps. In the Steps below we will walk through how this done with the Items table. The other tables are analogous, although Invoices has Line Items so it has a couple of extra steps which we'll discuss later.
All of the sync code will remain in fmQBO2.fmp12. We'll access the table in your solution via External File References.
The code in the fmQBO.fmp12 is organized in such a way to make it relatively easy to repoint the code at another file. Here is how it laid out.
Each Table has a Folder of scripts that is intended for you to edit. The other scripts elsewhere in the system will not Require you to edit them, but as the file is unlocked you can poke around as much as you want. Here is what that folder structure looks like:
The three sections outlined with the red boxes are the sections pertaining to each of the Tables you are connecting to. In the "Steps" section below will walk through what todo in each of these scripts.
Each Table you want to connect to Connect to QuickBooks Online is represented on the graph by a Table Occurrence (TO). Out of the box those TOs are connected to the tables in the fmQBO.fmp12 file. Your job, further detailed below in the Steps section, is to re-point them ( ie Re-connect ) them to your own tables.
Here is what the pertinent section of the graph looks like.
Note the four TOs outlined in red. These are the only ones we are discussing in this guide. The others, like QBOSync_Term and QBOSync_Accounts, are optional. The starter file pulls these downs so just for value lists purposes. You can leave them in the starter file, and just use the Values lists from there. Or you can bring them into your own file, using the same technique we are discussing here.
Steps For Connecting Items
- Test Connection
First make sure that you can connect to your QuickBooks Online Account. Follow the instructions on how to do that. Once you have a sync working in that scenario, you can move on to the next step.
- Copy in Fields
Copy qbo_id and qbo_send from the Item table in the fmQBO.fmp12 starter solution, and past them into YOUR items table in your solution
- Repoint QBOSync_Item
Open up the relationship Graph, and double click on the Table Occurrence QBOSync_Item. Add an External file Reference for your solution it isn't there. And chose the table that represents your Items. Close the Table Occurrence. In the image below, we are Re-pointing the QBOSync_Item TO to the "Products" table of the Invoices File.
- Fix Relationship
The relationship from your Item table to QBOSync is now almost certainly broken, now. Edit the relationship and make sure that the primary Key of your Items table is connected to QBOSync::id_Item. In this case the primary key has a funny name "PRODUCT ID MATCH FIELD"
Here is the beforeHere is the after
- Edit the "Items Key Fields" Script
Open the "Items Key Fields" script. There will likely be <Field Missing>s, or mismatches. Follow the instructions in that script to make sure to connect the right fields. Note we renamed the QBOSync_Item TO to Products to make the point that it is different. Renaming is optional.
Here is the before.Here is the after
- Edit the "Items Map: Send" Script
This script handles reading the data from tables into variables so the sync engine can send it up to QBO. When you open the script you'll see <Field Missing>'s and possibly some Mismarches in the "Read Data Into Variables" Section. Your job is to correct those mis-mtaches and <Field Missing>'s by connecting them to the correct fields from your table.
Before ( note: only showing part of the script )AfterNOTE: If you don't have a field in your system and don't think you'll need it, just disable the step. Also disable the step in the corresponding step below. Look down the script you'll see the same variable name used.
- Edit the "Items Map: Get" Script
In this step you'll do something very similar to the step above. When you open the script, you'll see mis-mtaches and <Field Missing>'s in the bottom part of the script, in the SET DATA INTO FIELDS section. Your job is to correct them, just like you did above.
Before ( note: Not showing all the steps for brevity. Keep going )After
Repeat For Invoices and Customer
You'll do the same thing with both Customers and Invoices. Note that Invoices has two extra Scripts for handling the Invoice Line Items. You'll need to do those as well.