Query operations and syntax

You can use the query operation to get details 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:

• Server responses return all attributes for each API entity
• Server responses only return attributes with values
• Wildcard character support for LIKE clauses is limited to “%” (wildcard that substitutes for 0 or more characters)

Query operations don’t support:

• Projections
• OR operations in WHERE clauses
• GROUP BY clauses
• JOIN clauses
• Special characters

Use a GET operation and include the select statement as a URI parameter:

1

https://quickbooks.api.intuit.com/v3/company/<realmId>/query?query=<select_statement>

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]

Example query select statement

1

GET  https://quickbooks.api.intuit.com/v3/company/1234/query?query=SELECT%20FROM%20Customer%20WHERE%20Metadata.LastUpdatedTime%20%3E+%272011-08-10T10%3A20%3A30-0700%27

Server responses contain a <QueryResponse> element. This corresponds with the original request’s select statement and contains the API entities that matched its 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>

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:

1
2

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:

1

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:

1

SELECT * FROM Invoice WHERE TotalAmt > '1000.0'

To use multiple filters together, use the AND keyword. The OR operation isn’t supported.

For example, the following query returns all Invoice entities created during the specified time range:

1

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:

1

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:

1

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 “in active”), filter using the Active attribute:

1

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

To get all active Customer entities:

1

SELECT * FROM Customer WHERE active = true

To get the Customer entity for the specified identifier, filter using the Id attribute:

1

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”:

1

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:

1

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:

1

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:

1

SELECT * FROM Customer ORDERBY FamilyName

The default sort order is ascending. To indicate descending sort order, include DESC:

1

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:

1

SELECT * FROM Invoice STARTPOSITION 1 MAXRESULTS 10

The next statement gets invoices 11 - 20:

1

SELECT * FROM Invoice STARTPOSITION 11 MAXRESULTS 10

The next gets invoices 21 - 25:

1

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:

1

SELECT COUNT(*)FROM Customer