We have been getting a lot of questions lately about constructing queries for QBO V3 Services. This article showcases some of the more popular inquiries of late. For full documentation about constructing queries and submitting them to data services, consult Query Operations in the QuickBooks Online documentation.
QBO V3 data services supports queries via a unified expression language, optimized for querying CRM 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 a 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’
How Proprietary is Proprietary?
The QBO V3 data services SQL-like syntax has the following limitations:
- The response set returned for an object is limited to full complement of properties.
- Projections are not supported.
- Only those properties with values are returned for the object.
- Joins are not supported.
- Wild card character support with LIKE clauses is limited to “%”.
- The OR operation is not supported in WHERE clauses.
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 * FROM Customer WHERE CompanyName=’Adam\’s Candy Shop’
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 (\).
Documentation for each entity clearly denotes those properties available to be used as filters in query WHERE clauses. 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 Taxable:
<IntuitResponse xmlns=”http://schema.intuit.com/finance/v3″ time=”2014-03-20T10:06:01.401-07:00″>
<Detail>QueryValidationError: property ‘Taxable’ is not queryable</Detail>
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)
Have fun with your queries! If there are additional questions I can cover for you, please leave a comment below.