You can use the query operation to get detailed information about a specific API entity.
You can query most of our API entities. Visit the API Explorer to see which operations each entity supports.
The query operation is similar to a pared down SQL query select statement, but with a few limitations. These limitations ensure requests don’t overload server-side resources:
Query operations don’t support:
Use the GET operation and include the select statement as a URI parameter:
Use the select statement to specify the selection criteria, entity attributes, sort order, and pagination.
1 2 3 4
Select Statement = SELECT * | count(*) FROM IntuitEntity [WHERE WhereClause] [ORDERBY OrderByClause] [STARTPOSITION Number] [MAXRESULTS Number]
Replace the following:
||All fields are returned.|
||The number of records that satisfy the query criteria. Returns a single value.|
The name of the queried entity. For example: Customer, Vendor, Invoice, etc. Case sensitive.
Tip: You can only query one entity at a time (i.e. You can’t do “Select * from Customer, Invoice.” You can either do “Select * from Customer” or “Select * from Invoice.”
Learn more about API entities
The WhereClause filters the returned data according to the value of the PropertyName.
Multiple clauses (filters) use AND.
Note: The OR operation isn’t supported.
||Wildcard character support for LIKE clauses is limited to “%” (wildcard that substitutes for 0 or more characters).|
This is only supported for IN clauses.
Note: Null is represented as the string ‘ ‘ (i.e. a space within single quotes).
Expression evaluation isn’t supported.
||The OrderByClause sorts the result.|
||Must be a positive integer.|
Server responses contain a <QueryResponse> element. It corresponds with the original request’s select statement and contains the API entities that matched the criteria.
Example sever response
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z"> <QueryResponse startPosition="10" maxResults="2"> <Customer> <Id>2123</Id> <SyncToken>0</SyncToken> ... <GivenName>Srini</GivenName> </Customer> <Customer> <Id>2124</Id> <SyncToken>0</SyncToken> ... <GivenName>Peter</GivenName> </Customer> </QueryResponse> </IntuitResponse>
||The starting point of the response for pagination.|
The number of transactions or records in the <QueryResponse> element.
The maximum number of entities that can be returned in a response is 1,000. If the result size isn’t specified, the default number is 100.
Tip: If a query returns many entities, use pagination to fetch the entities in chunks.
Tip: To determine the number of entities a query returns, use the COUNT keyword.
||Set to true if the response doesn’t return all attributes for the entity.|
If the query contains an error, the <QueryResponse> element will contain <Fault>:
1 2 3 4 5 6 7 8 9 10 11 12
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z"> <QueryResponse> <Fault type="Validation"> <Error code=”100” element="query"> <Message>OBJECT name not available </Message> </Error> <Error code="300" element="query"> <Message>Time value is incorrect</Message> </Error> </Fault> </QueryResponse> </IntuitResponse>
Reserved words, entity names, and attribute names are not case sensitive.
Attribute values are also not case sensitive.
For example, while the following two statements use different cases, they’re equivalent:
SELECT * FROM Customer WHERE GivenName = 'greg' STARTPOSITION 10 SELECT * from cUstomer where givenname = 'Greg' Startposition 10
Use backslash () to escape special characters like apostrophes (‘).
For example, to find a Customer entity for a QuickBooks Online company named “Adam’s Candy Shop,” use the following query:
SELECT * FROM Customer WHERE CompanyName = 'Adam\’s Candy Shop'
The WHERE clause determines which entities are returned by a query. Filter criteria is based on the value of the attribute.
For example, the following query returns all Invoice entities with a TotalAmt attribute greater than 1000.0:
SELECT * FROM Invoice WHERE TotalAmt > '1000.0'
To use multiple filters together, use the AND keyword.
For example, the following query returns all Invoice entities created during the specified time range:
SELECT * FROM Invoice WHERE MetaData.CreateTime >= '2009-10-14T04:05:05-07:00' AND MetaData.CreateTime <= '2012-10-14T04:05:05-07:00'
The following statement has two AND operators:
SELECT * FROM Invoice WHERE id in ('64523', '18761', '35767') AND MetaData.CreateTime >= '1990-12-12T12:50:30Z' AND MetaData.LastUpdatedTime <='1990-12-12T12:50:30Z'
If the WHERE clause is omitted, all entities up to the maximum number are returned.
If a query returns many entities, use pagination to fetch the entities in chunks.
To get all invoices for a given customer, filter using the CustomerRef attribute:
SELECT * FROM Invoice WHERE CustomerRef = '123'
To get all active and inactive customers (only list entities like Customer, Vendor, Item, etc can be Active or InActive), filter using the Active attribute:
SELECT * FROM Customer WHERE Active IN (true, false)
To get all active Customer entities:
SELECT * FROM Customer WHERE active = true
To get the Customer entity for the specified identifier, filter using the Id attribute:
SELECT * FROM Customer WHERE Id = '123456'
To get all attributes for the Customer entities whose first name starts with “K,” ends with “h”, and whose last name starts with “Palm”:
SELECT * FROM Customer WHERE GivenName LIKE 'K%h' AND FamilyName LIKE 'Palm%'
To get the header properties for the Invoice entities whose CustomerId attributes match the values of the IN clause:
SELECT * FROM Invoice WHERE CustomerId IN ('12')
To get all attributes 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
To sort query results, include the ORDERBY clause and specify an attribute as the sort field.
For example, the following query sorts Customer entities by the FamilyName attribute:
SELECT * FROM Customer ORDERBY FamilyName
The default sort order is ascending. To indicate descending sort order, include DESC:
SELECT * FROM Customer ORDERBY FamilyName DESC
To page through the results, specify STARTPOSITION (position of the entity in the query results) and MAXRESULTS (maximum number of entities in the result).
For example, suppose you have 25 invoices. The following query gets invoices 1 - 10:
SELECT * FROM Invoice STARTPOSITION 1 MAXRESULTS 10
The next statement gets invoices 11 - 20:
SELECT * FROM Invoice STARTPOSITION 11 MAXRESULTS 10
The next gets invoices 21 - 25:
SELECT * FROM Invoice STARTPOSITION 21 MAXRESULTS 10
To find out how many entities will be returned by a query, specify the COUNT keyword.
The number of entities is returned as the value of the
totalCount field of the <QueryResponse> element. QuickBooks Online data objects, such as Customers, aren’t in the response.
For example, the following query returns the total number of customers for a single QuickBooks Online company:
SELECT COUNT(*)FROM Customer