fmQBO Querying Quickbooks Online
QUERYING QUICKBOOKS ONLINE
In FileMaker terms, a “Query” is a “Find”. You query QuickBooks Online to find data that matches your search criteria. You can also tell it to only give you back the fields you are interested in. Intuit has some documentation on how this works. It’s worth reading, but in brief here are a couple of topics that will help you start to get a feel for it. But before we get to that it is worth pointing out that fmQBODeveloper can help you write these queries.
QuickBooks Online uses a syntax that is very similar to SQL. For example
SELECT * FROM Invoices
Will get you all the records and all the fields from Invoices. This one will get you all the records but only the ‘Id’ field.
SELECT Id FROM Invoices
You can also use WHERE clauses to filter the data. The following SELECT statement will find all the invoices that have a `TotalAmt` over 1000 dollars
SELECT * FROM Invoice WHERE TotalAmt > '1000.0'
It is important to note, that not all fields are filterable. You will get an error if you try to filter on a field that is not filterable. Intuit’s docs on what fields are filterable are not great. But most are noted in the API Reference.
If your query results in more then 100 records being returned you may have to deal with paging. Paging means that you will have to go back to the API and ask for another “pages'” worth of data. You do this by setting to STARTPOSITION and MAXRESULTS values in the SELECT Statement. For example to get the first 10 results of any invoice query you would do something like this.
SELECT * FROM Invoice STARTPOSITION 1 MAXRESULTS 10
To get the next 10, you would do:
SELECT * FROM Invoice STARTPOSITION 11 MAXRESULTS 10
Other Query Options
Ituit’s documentation has one section that lays out all the options, including the ones we covered here, plus ones we didn’t like ORDERBY. It’s at the bottom of this page.