Deep dive into QuickBooks Online data queries

The QuickBooks Online API supports data queries via the query endpoint. Queries sent via this endpoint use a unified expression language, optimized for querying QuickBooks Online data.  This language has a proprietary, SQL-like syntax, familiar to any developer with database experience.  For example, the following statement retrieves all active customers:

SELECT * FROM Customer

In turn, the app issues an HTTP REST GET operation to the query endpoint, passing the query statement as a URL parameter. The resulting URI is as follows:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer

Make sure to URL encode these queries before sending the request down the wire to our servers. There are several online tools to help you do that. Also, check with your specific development environment for their URL encoding support. For additional information about URL encoding, see http://www.w3schools.com/tags/ref_urlencode.asp.

Additionally, surround multi word names with single quotes (‘). For example, to query for Customer objects whose company name is Wild Garden Restaurant, issue the following:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer WHERE CompanyName=’Wild Garden Restaurant’

Click here for complete details about the query endpoint and supported SELECT statement syntax.

How Proprietary is Proprietary?

The QuickBooks Online SQL-like syntax has the following limitations:

• The response set returns all properties for each object. That is, projections are not supported.
• Only those properties with values are returned in the response set.
• Wild card character support with LIKE clauses is limited to “%” (wildcard that substitutes for 0 or more characters).
• The OR operation is not supported in WHERE clauses.
• The GROUP BY clause is not supported.
• The JOIN clause is not supported.

Escape Character

Use backslash (\) to escape special characters like apostrophe (‘). For example, to find a Customer object whose company name is Adam’s Candy Shop, submit the following query:

Select statement:

select * FROM Customer WHERE CompanyName=’Adam\’s Candy Shop’

Request URI:

https://quickbooks.api.intuit.com/v3/CompanyID/query?query=SELECT * FROM Customer WHERE CompanyName= ‘Adam\’s Candy Shop’

Here, the apostrophe in “Adam’s” is escaped with a backslash (\).

Filters

Entity properties that are available for use as filters in a query are denoted with filterable in the documentation.  For example, here is an excerpt from the API reference guide showcasing two properties from the Invoice entity: Balance is filterable and DeliveryInfo is not.

You receive an error response similar to the following if you try to filter your query with a non-filterable property—in this case an attempt to filter with DeliveryInfo:

<IntuitResponse xmlns=”http://schema.intuit.com/finance/v3″ time=”2014-03-20T10:06:01.401-07:00″>
  <Fault type=”ValidationFault”>
    <Error code=”4001″>
      <Message>Invalid query</Message>
      <Detail>QueryValidationError: property ‘DeliveryInfo’ is not queryable</Detail>
    </Error>
  </Fault>
</IntuitResponse>

Filter Examples

Get all invoices for a given customer. Filter on the CustomerRef attribute from the invoice:

SELECT * FROM Invoice WHERE CustomerRef = ‘123’

Get all active and inactive customers (only list entities like Customer, Vendor, Item, and so on, support concept of Active and InActive)

SELECT * FROM Customer WHERE Active IN (true, false)

Get all active Customer entities:

SELECT * FROM Customer WHERE active = true

Get the Customer entity for the specified identifier:

SELECT * FROM Customer WHERE Id = ‘123456’

Get all properties for the Customer entities whose first name starts with “K” and ends with “h”, and whose last name starts with “Palm”.

SELECT * FROM Customer WHERE GivenName LIKE ‘K%h’ AND FamilyName LIKE ‘Palm%’

Get the header properties for the Invoice entities whose CustomerId properties match the values of the IN clause.

SELECT * FROM Invoice WHERE CustomerId IN (’12’)

Get all properties for the Invoice entities with transaction dates between Jan. 1, 2011 and the current date.

SELECT * FROM Invoice WHERE TxnDate > ‘2011-01-01’ AND TxnDate <= CURRENT_DATE

Active Name List Entities

Name list entities such as Account, Class, and Customer are either active or inactive as indicated by their <Active> attribute setting.  They are never deleted.  As such, the query

SELECT * FROM Customer 

returns only active customer objects by default. It’s as if the following quere were issued:

SELECT * FROM Customer WHERE Active = true

To retrieve inactive objects you must explicitly filter with the Active property, for example:

SELECT * FROM Customer WHERE Active = false

To retrieve all customers, both active and inactive, issue this query:

SELECT * FROM Customer WHERE Active IN (true,false)

Summary

Have fun with your queries! If there are additional questions I can cover for you, please leave a comment below.