Previously, we've talked about how to integrate FileMaker and QuickBooks Online by first creating an access token and then a refresh token to maintain a secure connection between the applications. These are necessary steps to prepare for the integration, but now we'll turn our attention to moving data between the FileMaker-based solution and QuickBooks Online. But, of course, that's the whole point of the integration anyway.
Let's say for example that we want to view a list of customers who were last updated no later than March 1, 2015, from our Sandbox app. Because our example might introduce several new concepts that we'll repeatedly utilize throughout our integration, we will take them step by step. QuickBooks Online API uses URLs to connect to our online data. We need to construct these URLs correctly to push and pull the correct data to and from the appropriate place.
"https://sandbox-quickbooks.api.intuit.com/v3/company/xxxxxxx/query?query=select * from Customer Where Metadata.LastUpdatedTime > '2015-03-01'&minorversion=63"
The first section of the URL refers the API to the base location. There are two options here, either the sandbox or the production environment. We are using the sandbox, and you should too, until you feel comfortable enough with the API to start messing about with your QuickBooks actual data.
The second part of the URL (https://sandbox-quickbooks.api.intuit.com/v3/) tells the API what company we want to pull the data from, in this case, our sandbox company.
The next portion, "/company/realmId," is the real gist of the thing, and that is the specifics of what we're dealing with on the QuickBooks Online side of things; QuickBooks calls this a query.
"query=select * from Customer Where Metadata.LastUpdatedTime > '2015-03-01"
The QuickBooks query language is based on SQL. Since this post is about the interaction with QuickBooks Online via its API and not a tutorial on SQL, we won't go into the weeds about what all that means. If you're interested in getting deeper into this – and you will need to in order to get the most out of your integration – there are many resources on the web and in QuickBooks Online's own API documentation. Having said that, we will at least break down what this example query is doing.
The first part, "select *," says get all the field values. The second part, "From Customer," tells the query which QuickBooks Online data table to search. The third part, "Where Metadata.LastUpdatedTime > '2015-03-01," which, although fairly simple in our example, can be the most complex, is the Find criteria. It's akin to putting "> 3/1/2015" in a field called Metadata.LastUpdate during a FileMaker find. The last little bit of the URL is just some housekeeping tags. In this case, we're calling out what minor version of the API we are utilizing "&minorversion=63"
Once we've assembled this URL, we need to gather some information from the results of our past authentication steps. We will pass this information to the API via the "cURL Options."
" -X GET"
-H "Content-Type: application/json"
-H "Accept: application/json"
-H "Authorization: Bearer XXXXXX"
The first part of this list is the "action" we will be using to interact with the QuickBooks Online API. In our example, we aren't deleting, creating, or modifying any data in QuickBooks but merely viewing, so we use a simple "Get" command. There are other operations we might encounter as we interact with QuickBooks Online and other REST APIs:
- Post: Create Data
- Delete: Remove Data
- Update: Modify Data
These actions are not comprehensive or complete, but it does give you an idea of the capabilities. There isn't an established convention as some APIs use these operations differently, and some will not even support these. It all depends on the API that we're using.
The second two items on the list tell the API the format of the data we're sending and expect to receive. In our case, and most cases nowadays, it's JSON, but some APIs give you the option for XML or Raw.
The last on the list is the security part, and that is the token we created in previous scripts outlined in previous posts on this subject.
When we pack this stuff up and send it to the QuickBooks Online API using the FileMaker Insert from URL script step, we get a list of customers and their details that match the search criteria specified in the query. Although we haven't outlined it in this post or done it in the script, we can utilize FileMaker's many JSON functions to parse this data out into our FileMaker table fields to suit our needs.
We hope this FileMaker and QuickBooks Online series has given you a taste of what you can do with FileMaker and the QuickBooks Online API to share data between these essential applications in your organization. Feel free to check out our sample file to explore the integration further.
Integrations make it easy to extend and connect applications that ultimately streamline workflows. In addition, we have options when incorporating FileMaker custom software solutions with other services, whether it's through native connections, APIs, or middleware solutions such as FileMaker Connect or Zapier.
We've integrated FileMaker business solutions with other applications such as Slack, HubSpot, and Eventbrite. Feel free to contact us if you need any assistance with a FileMaker integration project.
This article is also published on FileMakerProGurus.com.