Building Smarter with Intuit: Supercharge your queries

Have you ever felt stuck in your development journey, unsure how to implement a certain capability? You are not alone. In a new series of articles around best practices for developing applications with Intuit APIs we’re calling “Building Smarter with Intuit”, we’ll share our insights and tips on optimizing everything from authorization to APIs to advanced platform capabilities. Stay tuned for more articles in this series designed to help you level up your app development on the Intuit developer platform.

This first article is centered on everything queries. Let’s start with a quick recap. Our platform accepts SQL-like syntax to GET respective data. If you’ve ever interacted with our reports APIs, you will have likely used queries to do so.

To begin, we need to understand the accepted syntax for the SELECT statement as well as the basic URI format. They are:

SELECT statement syntax:

SELECT 
    * 
FROM IntuitEntity

WHERE [WhereClause]
 	ORDERBY [OrderByClause]
   	STARTPOSITION [Number] MAXRESULTS [Number]

Basic query URI:

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

As an example, if we wanted to GET all active customers in a given QBO company, you might construct a statement like the following:

SELECT
	    *
FROM Customer

WHERE Active = true
ORDERBY MetaData.LastUpdatedTime DESC

Put together, a fully populated query request with URL encoding would look like this:

https://quickbooks.api.intuit.com/v3/company/1234567890/query?query=SELECT%20*%20FROM%20Customer%20WHERE%20active%20=%20true%20ORDERBY%20metadata.lastupdatedtime%20DESC

Now that we have a fundamental understanding of how queries work, there are a few items to be aware of:

• 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 the WHERE clause, GROUP BY clauses, JOIN clauses, and special characters

With this knowledge in hand, let’s go through some sample use cases and how you would use query operations to facilitate them.

Use case 1: query all data for a given entity

We’ll start with a very common use case – querying all data for a given entity. Let’s say we want to grab all data/attributes related to Bills.

An example query is as follows:

SELECT
	    *
FROM Bill

This query works well to get you the data you need. However, you likely will have to carry out additional manipulation once you receive the response. Let’s say instead your app only needs the last year’s worth of bills. You will need a variable in your code that dynamically looks at the current date and then subtracts 365 days. You can then use the output of that and program it into your query using a WHERE clause:

SELECT
	    *
FROM Bill

WHERE TxnDate >= ‘2022-06-01’

Now, you’ll likely need to do far less manipulation once you receive the response.

There’s still one item we need to consider and that is overflow errors. For bigger customers with larger amounts of data, you may encounter this. To get ahead and make your app more agile, you can make use of pagination. This involves two additional clauses – STARTPOSITION and MAXRESULTS. Whenever you make a query against Intuit’s servers, there’s an order to the results. This order can be interfaced with using these two clauses. STARTPOSITION dictates where in the list you will be starting your query from. MAXRESULTS dictates how many records will be present in the response. A good place you may want to start is paging through chunks of 100 records. Put together, our query now looks like this:

SELECT
	    *
FROM Bill

WHERE TxnDate >= ‘2022-06-01’

STARTPOSITION 1 MAXRESULTS 100

You would then increment based on your declared value of MAXRESULTS:

SELECT
	    *
FROM Bill

WHERE TxnDate >= ‘2022-06-01’

STARTPOSITION 101 MAXRESULTS 100

You now have a query that gets you the data you need in a structured format that requires less additional manipulation and is scalable across both small and large customers alike.

Use case 2: get data when IDs are known

For this use case, we’ll assume you have a list of known IDs for a given entity and are looking for additional data. Let’s use Accounts for this example. When you only have a handful of IDs, you could consider using the Read operation. When you move beyond a handful of records, it can become tedious and add unwanted server strain by carrying out a Read operation on all respective records. Instead, we can make use of the query operation and specifically use the IN clause.

An example query is as follows:

SELECT
	    *
FROM Account

WHERE Id IN (‘83’, ‘27’, ‘5’, ‘61’, ‘62’)

From here, we may want to be more selective about which attributes we are requesting:

SELECT
	    Id, FullyQualifiedName, Classification, AccountType, CurrentBalance
FROM Account

WHERE Id IN (‘83’, ‘27’, ‘5’, ‘61’, ‘62’)

Finally, let’s introduce an ORDERBY clause to add more structure to the records:

SELECT
	    Id, FullyQualifiedName, Classification, AccountType, CurrentBalance
FROM Account

WHERE Id IN (‘83’, ‘27’, ‘5’, ‘61’, ‘62’)

ORDERBY Id DESC

You now have an optimized and lean query that practices good data governance through and through.

Use case 3: count the number of records for a given entity

Let’s say your app wants to populate a widget that shows the total number of open invoices. You could of course query for all invoices and then calculate the number of open invoices after you receive a response. However, doing so wouldn’t be as efficient as dictating criteria in the query operation itself. This saves you from needing to do additional transformation after receiving the response.

An example query is as follows:

SELECT
	    COUNT(*)
FROM Invoice

WHERE Balance != ‘0’

Returns:

"QueryResponse": {
  "totalCount": 214
 }

Beyond using the output (214) directly, since you know how many records are in the dataset you’re querying against, you could make use of pagination to return records in chunks. Additionally, this can help in validating that you have captured all possible records.

Best practices & tips

• You can only query one entity in a single statement. If you need to query multiple entities, use separate statements.
• GET only the data you need. Instead of SELECT *, consider selecting only certain attributes/fields. This will help with response times as well as being onside of data stewardship principles.
• Make use of the WHERE clause to only return records conditionally rather than having to do additional parsing on your end.
• Wherever possible, make use of ORDERBY to return more structured datasets.
• The default sort order is ascending – use DESC in your ORDERBY clause to sort in descending order.
• Entities, attributes, and reserved words are not case sensitive.
• Use backslash ( \ ) to escape special characters such as apostrophes ( ‘ ). For example: ‘Bob\’s Bakery’.
• The supported operators are: =, !=, <, >, <=, >=, LIKE, IN
• The maximum number of records that can be returned in a response is 1000. Consider using pagination to fetch records in chunks.
• To filter on a child attribute, use a ‘.’ between the parent and child attribute. E.g. MetaData.CreateTime.
• If you are trying to filter based on a customer, vendor, or entity reference, you don’t need to dictate the child attribute. E.g. instead of WHERE CustomerRef.value = ‘15’, it would just be CustomerRef = ‘15’.

We hope this helps you supercharge your use of queries on the Intuit developer platform. Keep an eye out for the next article in our “Building Smarter with Intuit” series. In the meantime, you might want to read a similar article on webhooks, which you can find here.