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.

Selects

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.

Paging

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.