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
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.
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
Using the result ID ensures that your paged results are from the same originating
readByQuery call, which is important if there might be multiple
readByQuery calls on the same object or data changing behind the scenes.
<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:
When doing NULL comparisons, you can use
IS NOT NULL or
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.
like operator supports wildcards:
%) to match zero, one, or multiple characters
_) to match a single character
The operands are the integration name of a field and a corresponding target value (or values) for that field.
String operands are case-sensitive.
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 ( || |
|Ampersand ( || |
The following shows examples of queries on both standard and custom objects.
| || ||List inactive and one-time vendors (by querying on boolean values).|
| || ||List departments 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.|
| || ||List custom |
| || ||List custom |
| || ||List vendors with vendor IDs other than |
| || ||List employees whose IDs start with the letter |
| || ||List employees whose IDs include the letter |
| || ||List employees whose IDs have the letter |
If you create a new boolean field on existing records, those fields will not yet have a value. Even though a
read request will show the value as
false to reflect the value in the Sage Intacct UI, when you attempt to return these with a
readByQuery request, 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 documentation 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.
inspect function to return many fields names and types for an object, for example:
<inspect detail = "1"> <object>APBILL</object> </inspect>
If you need to get information about related or owned objects, see the relevant FAQ.
If you need to query for an object based on a joined field, see the relevant FAQ.