BTerrell Group Blog

Tips on Writing Queries for the Readbyquery() Method

Posted by Joe Zhou on Mon, Mar 09, 2015

The Readbyquery() method is one of the most used Intacct API calls, because it allows you to retrieve records from a certain object based on a query. The query syntax used in this method is actually a subset of standard SQL where clause. Therefore, it can be confusing as to which standard SQL syntax is applicable to Intacct and which is not. I listed some tips I gathered from my trial and errors.  I hope you find them helpful.

  1. It is a good habit to enclose your query in parenthesis.
  2. Supported SQL operators include, but are not limited to:

<, >, <=, >=, =, like, not like, in, not in, and, or, is null, is not null, not

  1. Because Intacct API calls are XML based, the operators ‘<’ and ‘<=’ are illegal as a result. Both operators should be replaced with ‘&lt;’ and ‘&lt;=’ instead.
  2. Use the escaped form on these characters.
ampersand & -> &amp;
apostrophe ' -> &apos;
quotation mark " -> &quot;
  1. Use IS NOT NULL if you want to exclude null values.
  2. Use IS NULL if you only want to return null values.
  3. If you want to express NOT EQUAL TO, use these expressions.
  • (RECORDID NOT IN (‘92’))
  • (NOT RECORDID = ‘92’)
  1. Use IN operator to query within an array. For example, (RECORDNO IN ('11','22','33','44','55','66','77','88','99'))
  2. Use ‘&lt;=’ and ‘&gt;=’ on a date range.

For example, (WHENDUE &lt;= '02/28/2015' AND WHENDUE &gt;= '02/01/2015')

10. Use the LIKE operator and a wildcard character such as % to substitute for zero or more characters to perform START WITH, END WITH or CONTAIN type of queries.

For example, (VENDORNAME LIKE 'Code%')

 

I hope you find these useful, please let me know if you have other tips for Readybyquery().

Tags: Intacct Customization