Logo
Home

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.

 


Posted

in

by

Comments

13 responses to “Deep dive into QuickBooks Online data queries”

  1. Theodore Avatar
    Theodore

    Hello Patty, I’m looking for a way to select all Payments a Customer has made. I had hoped that SELECT * FROM Payment WHERE CustomerRef = ’61’ would work. It generates an error.

    1. Patty Avatar
      Patty

      Hi Theodore,
      Thank you for your question. A similar query to my sandbox was successful in returning the Payment object. What error did you get? The CustomerRef must reference an active customer object. Did you confirm that Customer.Id=61 is an active customer object (Customer.Active=true)?
      As a next step, please file a support ticket and someone will help you further.
      -patty

  2. Wayne Avatar
    Wayne

    Hello Patty,
    I currently have a String variable, $invoiceString, that has a couple hundred invoice numbers in it, each one comma separated and surrounded by single quotes. I then have a query: SELECT * FROM Invoice WHERE DocNumber IN $invoiceString MAXRESULTS 1000. This query works perfectly up to a certain point, but at some unspecified String length, it seems to stop working. Is there a maximum character length in which a query like this will not work? I’m sorry if this is a bit unclear. Let me know if you need clarification on something.

    1. Patty A Avatar
      Patty A

      Hi Wayne,
      Thanks for your question. You may be facing a limitation on the overall URL length. Please file a support ticket and someone will be able to help you in more depth. -patty

  3. rama Avatar
    rama

    i tried to filter query for customer by phone i got error like this QueryValidationError: property ‘PrimaryPhone’ is not queryable
    how to i do the query .

  4. James Avatar
    James

    I tried below queries but no luck so far:

    1. `SELECT * FROM Vendor WHERE Id NOT IN (‘1’, ‘2’, ‘3’)` don’t work
    2. `SELECT * FROM Vendor WHERE NOT (id=’1′ OR id=’2′ OR id=’3′)` don’t work
    3. `SELECT * FROM Vendor WHERE id!=’1′ OR id!=’2′ OR id!=’3′ don’t work

    Any suggestion please, I don’t want to query already imported data again for performance reason.

    1. Patty A Avatar
      Patty A

      Hi James,
      Our query mechanism does not support negative keywords.
      -patty

  5. Jerry Avatar
    Jerry

    Hi. Are there any TxnDate special values supported other than CURRENT_DATE?

  6. Francis Avatar
    Francis

    How would one query custom fields ?

    1. Patty A Avatar
      Patty A

      Hi Francis, custom fields are not available for query. Only a subset of fields for a given resource are available for queries. These are denoted as “filterable” on the resource’s attribute table.

    2. admin Avatar
      admin

      Custom fields are not filterable, so there is no way to query entities by custom fields.

  7. Nate Avatar
    Nate

    Why does the ‘OR’ operator not work in Intuit API queries? For example, this returns a record:

    select Id from customer where DisplayName = ‘9733-Smith’

    … but this throws an error:

    select Id from customer where (DisplayName = ‘9733-Smith’ or FullyQualifiedName = ‘9733-Smith’)

    QueryParserError: Encountered “(” “(” at line 1, column 31

    I tried it without the parens and I get a different error:

    QueryParserError: Encountered <OR> “or” at line 1, column 58

    It seems very odd that you wouldn’t support a simple “or” statement in a where clause. How can this be done?

  8. Jerry Avatar
    Jerry

    I need to query all the parent customers whose ParentRef is null. How can i query all the parent customers and then query all the child customers?
    Parentref is the only property that differentiates parent and child.

Leave a Reply

Your email address will not be published. Required fields are marked *