- Limit fields returned to improve performance
- Paginate results
- Supported operators
- Illegal characters in XML
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
The spaces before and after the operator are required.
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>
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
<readMore> <object>APBILL</object> </readMore>
<readMore> <resultId>7765623332WU1hh8CoA4QAAHxI9i8AAAAA5</resultId> </readMore>
Sage Intacct queries support a subset of operators available for the standard SQL
WHERE clause, as follows:
You can combine conditions using
OR operators, and you can query for an untrue condition by prefixing it with
Note: You cannot query on a field that is a join by foreign key.
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.
|Less than (
|Greater than (
The following shows examples of queries on both standard and custom objects.
||List inactive and one-time vendors.|
||List deparments with any of these IDs.|
||List contacts with the given name. Note the escaped apostrophe.|
||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.|
||List Journal Entries.|
||List bills whose state is set to posted.|
||List account balances for the given period.|
||List account balances, excluding entries with the specified zero balances.|
||List user information records of the business user type.|
Spaces before and after an operator are required.
CONTACTNAME = 'Erin\'s Software'
CONTACTNAME ='Erin\'s Software'
If you create a new boolean field on existing records, those fields will not yet have a value. Even though a
readrequest will show the value as
falseto reflect the value in the Sage Intacct UI, when you attempt to return these with a
readByQueryrequest, you need to query on the NULL value.
If you are querying a validated list that stores single characters in place of terms, you need to use the database character. For example,
usertype = 'B'queries for records of the business user type from USERINFO. See the query tables in the API documenation for details about such such database characters.
If you need information about available fields for an object, there are several places with helpful information:
Use the Sage Intacct GUI to look up object field integration names through Object Definition pages.
inspectfunction to return many fields names and types for an object, for example:
<inspect detail = "1"> <object>APBILL</object> </inspect>