Overview

You can query the data in a Sage Intacct company to return a list of objects that match a given condition. For example, the following query matches APBILL objects with the given VENDORNAME.

Queries are interpreted as part of a readyByQuery function call in the content element of an XML request:

<content>
    <function>
        <readByQuery>
            <object>APBILL</object>
            <fields>*</fields>
            <query>VENDORNAME = 'Regal Services'</query>
            <pagesize>100</pagesize>
        </readByQuery>
    </function>
</content>

You can provide an empty query element to return all APBILL objects (up to the limit specified with pagesize). See Paginate results to access results beyond the specified limit.


Limit fields returned to improve performance

If you are querying for many objects, you can improve performance by limiting the fields returned to only those you care about:

<readByQuery>
   <object>APBILL</object>
   <fields>RECORDNO,RECORDTYPE</fields>
   <query></query>
   <pagesize>25</pagesize>
</readByQuery>

Paginate results

You might want to get query results in batches instead of all at once. Note that the query in the previous example provided a pagesize of 25. Assuming there are more than 25 results, you can get the next batch of 25 with readMore. Provide either the object name or the result ID (from the readByQuery response) for the child element as shown. When you get to the last set of results, numremaining="0" is shown in the listtype attribute of the data element.

<readMore>
    <object>APBILL</object>
</readMore>
<readMore>
   <resultId>7765623332WU1hh8CoA4QAAHxI9i8AAAAA5</resultId>
</readMore>

Supported operators

Sage Intacct queries support a subset of operators available for the standard SQL WHERE clause, as follows: <, >, >=, <=, =, like, not like, in, not in.

You can combine conditions using AND and OR operators, and you can query for an untrue condition by prefixing it with NOT.

Note: You cannot query on a field that is a join by foreign key.


Operands

The operands are the integration name of a field and a corresponding target value (or values) for that field.

String operands are case-sensitive.


Illegal characters in XML

When composing your queries, be aware that the less than (<) and ampersand (&) characters are illegal in XML. The greater than (>) character is legal, but it is a good practice to replace it with the XML entity reference.

Character Entity reference
Less than (< ) &lt;
Greater than (> ) &gt;
Ampersand (& ) &amp;

Examples

The following shows examples of queries on both standard and custom objects.

Object Query Purpose
VENDOR STATUS = 'T' AND ONETIME = 'F' List inactive and one-time vendors.
DEPARTMENT DEPARTMENTID IN ('10','20','15','11','12','8','3','4','100','99') List deparments with any of these IDs.
CONTACT CONTACTNAME = 'Erin\'s Software' List contacts with the given name. Note the escaped apostrophe.
ACTIVITYLOG OBJ_ID &gt; 10000 AND CREATED_AT &gt;= '04/19/2016 12:00:00' AND CREATED_AT &lt; '04/20/2012 12:00:00' List audit trail logs for all records on April 19, 2016. When working with dates, the greater than operator means more recent than. Note the use of the entity references for the greater than and less than operators.
GLBATCH JOURNAL = 'GJ' List Journal Entries.
APBILL STATE='A' List bills whose state is set to posted.
GLACCOUNTBALANCE PERIOD = 'Month Ended June 2016' List account balances for the given period.
GLACCOUNTBALANCE PERIOD = 'Month Ended June 2016' AND NOT (TOTDEBIT = 0 AND TOTCREDIT = 0 AND TOTADJDEBIT = 0 AND TOTADJCREDIT = 0 AND FORBAL = 0 AND ENDBAL = 0) List account balances, excluding entries with the specified zero balances.
USERINFO usertype = 'B' List user information records of the business user type.
depreciation_schedule depreciation_amount &lt;= 50 AND Rasset = 'Thinkpad T61' AND posting_date &gt;= '02/01/2017' List custom deprecation_schedule objects that meet the conditions. Note the use of the entity references for the less than and greater than operators.
depreciation_schedule STATUS IS NULL List custom deprecation_schedule objects whose status is set to NULL.

Tips


Provide feedback