Queries
You can query the data in a Sage Intacct company to return a list of objects that match given conditions.
Overview
There are two functions for performing queries on standard and custom objects:
query
is a newer function that accepts query and filter expressions composed of XML elements with a well-defined schema. This function supports complex queries with multiple conditions.readByQuery
is a legacy function that accepts a simple string for the query definition.
Here’s a comparison of the two functions performing the same query to find all bills for a vendor that is specified by name:
Advantages of query
The query
function provides several advantages over the readByQuery
function:
- More operators are supported.
- You can get the values for fields on related objects and/or use them in filters.
- You can return the queryable fields and relationships with the
lookup
function. - You can perform case-insensitive queries.
- Building complex query statements is less error-prone and error checking is more robust.
- You can sort results.
- You can perform aggregate operations such as sum or average.
- You can get more objects in a response.
Using query
Let’s take a closer look at the query that lists AP bills where the vendor name is Regal Services
.
<query>
<object>APBILL</object>
<filter>
<equalto>
<field>VENDORNAME</field>
<value>Regal Services</value>
</equalto>
</filter>
<select>
<field>RECORDNO</field>
<field>VENDORNAME</field>
</select>
</query>
- The type of object that you’re looking for is specified by the
object
element, which isAPBILL
in this example. - The values that you want to find are listed in the
filter
element. This example usesequalto
to find all APBILL objects in which theVENDORNAME
field is equal toRegal Services
. - The fields to include in the response are specified in the
select
element.
The response might look like this:
<data listtype="APBILL" totalcount="2" offset="0" count="2" numremaining="0">
<APBILL>
<RECORDNO>208</RECORDNO>
<VENDORNAME>Regal Services</VENDORNAME>
</APBILL>
<APBILL>
<RECORDNO>227</RECORDNO>
<VENDORNAME>Regal Services</VENDORNAME>
</APBILL>
</data>
Building more complex queries
While simple queries like the one shown above are useful, you can use the query
and lookup
functions to create more complex queries based on field values and object relationships.
Finding an object’s queryable fields and values
Use the lookup
function to find all of an object’s queryable fields, their data types, valid values (for enums) and relationships to other objects. You can use any of those within the select
and/or filter
elements of a query. lookup
works with both standard and custom objects.
The following shows an excerpt from the response of a lookup
on the journal entry object, GLBATCH. Note all the information provided for the fields, including any valid values for enum fields. Relationships that can be queried are listed after the fields.
<lookup>
<object>GLBATCH</object>
</lookup>
Response:
<Type Name="GLBATCH" DocumentType="">
<Fields>
<Field>
<ID>RECORDNO</ID>
<LABEL>Record number</LABEL>
<DESCRIPTION>Record Number</DESCRIPTION>
<REQUIRED>false</REQUIRED>
<READONLY>false</READONLY>
<DATATYPE>INTEGER</DATATYPE>
<ISCUSTOM>false</ISCUSTOM>
</Field>
...
<Field>
<ID>STATE</ID>
<LABEL>State</LABEL>
<DESCRIPTION></DESCRIPTION>
<REQUIRED>true</REQUIRED>
<READONLY>false</READONLY>
<DATATYPE>TEXT</DATATYPE>
<VALIDVALUES>
<VALIDVALUE>Draft</VALIDVALUE>
<VALIDVALUE>Submitted</VALIDVALUE>
<VALIDVALUE>Partially Approved</VALIDVALUE>
<VALIDVALUE>Approved</VALIDVALUE>
<VALIDVALUE>Posted</VALIDVALUE>
<VALIDVALUE>Declined</VALIDVALUE>
<VALIDVALUE>Reversal pending</VALIDVALUE>
<VALIDVALUE>Reversed</VALIDVALUE>
</VALIDVALUES>
<ISCUSTOM>false</ISCUSTOM>
</Field>
...
</Fields>
...
<Relationships>
<Relationship>
<OBJECTPATH>JOURNAL</OBJECTPATH>
<OBJECTNAME>JOURNAL</OBJECTNAME>
<LABEL></LABEL>
<RELATIONSHIPTYPE>MANY2ONE</RELATIONSHIPTYPE>
<RELATEDBY>JOURNAL</RELATEDBY>
</Relationship>
...
</Relationships>
</Type>
Query on a field value
After using lookup, you can easily compose a query based on the output. Continuing with the example, you can now query for posted entries using one of the valid values for the STATE
field:
<query>
<object>GLBATCH</object>
<filter>
<equalto>
<field>STATE</field>
<value>Posted</value>
</equalto>
</filter>
<select>
<field>RECORDNO</field>
<field>STATE</field>
</select>
</query>
Query on a field value of a related object
You can access the value of a field on a many-to-one related object that was returned by lookup
.
Continuing with the example, assume you want to know the journal symbol for each listed journal entry. After running a separate lookup
on the JOURNAL object, you learn that one of its queryable fields is SYMBOL
.
To access the value of the journal’s SYMBOL
field from within the GLBATCH
query, provide the OBJECTPATH
value of the related object (from the GLBATCH lookup
output above), followed by the dot operator (.), then the name of the field. In our example, this is JOURNAL.SYMBOL
:
<query>
<object>GLBATCH</object>
<select>
<field>RECORDNO</field>
<field>STATE</field>
<field>JOURNAL.SYMBOL</field>
</select>
<filter>
<equalto>
<field>JOURNAL.SYMBOL</field>
<value>APJ</value>
</equalto>
</filter>
</query>
Response:
<data listtype="GLBATCH" totalcount="97" offset="0" count="97" numremaining="0">
<GLBATCH>
<RECORDNO>2624</RECORDNO>
<STATE>Posted</STATE>
<JOURNAL.SYMBOL>APJ</JOURNAL.SYMBOL>
</GLBATCH>
<GLBATCH>
<RECORDNO>2625</RECORDNO>
<STATE>Posted</STATE>
<JOURNAL.SYMBOL>APJ</JOURNAL.SYMBOL>
</GLBATCH>
...
</data>
You can query across multiple relationships using multiple dot operators in many cases. For example, the following expression resolves correctly when querying a vendor object:
<field>PAYTOCONTACT.MAILADDRESS.ZIP</field>
For hierarchical relationships, such as a customer to a parent customer, you can query across one relationship:
<field>CUSTOMER.PARENT.NAME</field>
Querying across more than one hierarchical relationship is not allowed. The following results in an error:
<field>CUSTOMER.PARENT.PARENT.NAME</field>
Support for custom objects and relationships
You can query across custom relationships:
- Between two custom objects
- Between one custom and one standard object, or vice versa
Querying across a custom relationship between two standard objects is not supported.
Query capabilities
Let’s look at the top-level elements of a query to get an idea of everything you can do with this function.
Examples
The following examples range from simple queries to complex ones that combine multiple queries. Click to view the individual examples or expand them all.
Lists paid bills, returning the record number, vendor name, and state for each.
Lists bills where the total due is greater than 500 and sort the results from the highest amount to the lowest.
Lists bills where the related vendor's credit limit is greater than 50,000. Note the use of the dot operator (.
) to access the related field.
Lists bills, showing the due dates for the terms for the related vendors:
Lists bill lines where the department ID is in the given set.
Lists bills where the vendor name is Acme Supply
and the bills were created after 04/26/2021.
Lists bills where the vendor name starts with the letter B
.
Lists bills that are due between the given dates. Note that the first date value must be the earlier date:
Lists bills where the third letter of the vendor name is n
(note the two underscores before the n
).
Lists bills with empty descriptions.
Lists bills with empty descriptions, skipping the first five results.
Lists bills where the total due is between 10 and 500 dollars, inclusive.
Lists bills with empty descriptions where either the total due is over 5000 or the payment priority is urgent (or both).
Lists customers where the value of the CUSTOM_HEALTH
custom field is Good
.
Lists invoices where the customer ID starts with the letter c
or C
and provides a sum of the total due by customer ID. Results are grouped by the first two fields.
Lists Order Entry transactions where the transaction definition (docparid
) is Sales Order
.
Lists the number of bill records for each vendor.
Lists the task ID as well as related field values for each task where the parent project's end date is in the given range.
Lists AR invoice items where the item ID starts with the letter B
and the customer has a credit limit within the given range.
Lists MCA_attendee custom objects where the related customer (via R_attendee_customer relationship) owes more than 5,000.
Schema definition
The following shows a graphical representation of the schema for the query
function. The actual XSD in provided after that.
Under the filter
element, only one top-level operator is allowed. For complex queries, use an and
/or
operator at the top level to build expressions with as many conditions as you need.
Complete schema (XSD file)
Elements of the query
Object
Use object
to specify which object to query, for example, APBILL
. Both standard and custom objects are supported.
<object>APBILL</object>
Select
Use select
to specify the fields to return as well as any aggregate functions to use.
Fields
Use field
to specify each field on the object to return in the results.
<select>
<field>RECORDNO</field>
<field>VENDORNAME</field>
<field>STATE</field>
</select>
Fields on related objects
The dot operator (.
) lets you access the values of fields on a related object that has a many-to-one relationship to the object being queried. You can use the dot operator in fields you supply in the select
or filter
tags.
The Nexus section of the Platform/Customization Services catalog can help you identify the related objects whose fields can be queried. For example, the Nexus section for AP bill shows that the related vendor object has a many-to-one relationship to AP bills.
Accordingly, you can access fields on the related vendor from the AP bill, for example:
<field>VENDOR.CREDITLIMIT</field>
Aggregate functions
Aggregate functions perform operations on the values of fields. When using aggregate functions, the fields in the select
element are used to group the results.
Operator | Description |
---|---|
count | Return the number of fields |
avg | Return the average of fields |
min | Return the smallest value |
max | Return the largest value |
sum | Return the sum of the fields |
For example, the following provides the total number of ARINVOICE
records for the given CUSTOMERID
:
<query>
<select>
<field>CUSTOMERID</field>
<count>RECORDNO</count>
</select>
<object>ARINVOICE</object>
</query>
Results such as the following are returned:
<ARINVOICE>
<CUSTOMERID>C-00004</CUSTOMERID>
<COUNT.RECORDNO>18</COUNT.RECORDNO>
</ARINVOICE>
<ARINVOICE>
<CUSTOMERID>C-00023</CUSTOMERID>
<COUNT.RECORDNO>20</COUNT.RECORDNO>
</ARINVOICE>
See the tip for using aggregate functions.
Filter
Note: Use of custom field filters on large datasets may slow query execution. This is because custom fields are not indexed. Using standard, indexed fields for queries ensures better query performance over time. To ensure the best possible query performance, follow these recommendations for filters:
- Prioritize standard fields in filters.
- Incorporate custom fields only as supplemental filters.
Qualifies the records to return based on their field values. You use operators and conditions to build your filter.
<filter>
<greaterthan>
<field>TOTALDUE</field>
<value>500</value>
</greaterthan>
</filter>
Comparison operators
Operator | Description |
---|---|
equalto |
Equal to |
notequalto |
Not equal to |
lessthan |
Less than |
lessthanorequalto |
Less than or equal to |
greaterthan |
Greater than |
greaterthanorequalto |
Greater than or equal to |
isnull |
Is a null value |
isnotnull |
Is not a null value |
<filter>
<equalto>
<field>STATE</field>
<value>Paid</value>
</equalto>
</filter>
Logical operators
Operator | Description |
---|---|
between |
Value is within the given range |
in |
Value is one of the given values |
notin |
Value is not one of the given values |
like |
Value matches a given pattern |
notlike |
Value does not match a given pattern |
The like
and notlike
operators support wildcards:
- Percent sign (
%
) to match zero, one, or multiple characters - Underscore (
_
) to match a single character
The following example matches any name that begins with the letter B
:
<filter>
<like>
<field>VENDORNAME</field>
<value>B%</value>
</like>
</filter>
Conditions
Conditions let you combine operator statements for complex queries.
Condition | Description |
---|---|
and |
All conditions must be true |
or |
One or more conditions must be true |
<filter>
<and>
<greaterthanorequalto>
<field>WHENCREATED</field>
<value>04/19/2023</value>
</greaterthanorequalto>
<lessthanorequalto>
<field>WHENCREATED</field>
<value>04/26/2023</value>
</lessthanorequalto>
<equalto>
<field>VENDORNAME</field>
<value>Acme Supply</value>
</equalto>
</and>
</filter>
Order by
Specifies the order for results based on the chosen field.
Values: ascending
, descending
<orderby>
<order>
<field>TOTALDUE</field>
<descending />
</order>
</orderby>
If not specified, there is no default and the order of results is undefined.
Options
Response data format
The default format for query responses is XML, but you can use the returnformat
option to have data returned in CSV or JSON format.
<options>
<returnformat>csv</returnformat>
</options>
Valid values are xml
, csv
, and json
. Note that CSV and JSON responses do not include any surrounding XML elements such as <control>
and <authenticatiohn>
; they only include the response data.
Here is a sample response containing two vendor objects with three fields each in all three formats:
XML
<?xml version="1.0" encoding="UTF-8"?>
<response>
<control>
<status>success</status>
<senderid>*******</senderid>
<controlid>1234567890</controlid>
<uniqueid>false</uniqueid>
<dtdversion>3.0</dtdversion>
</control>
<operation>
<authentication>
<status>success</status>
<userid>joe.user</userid>
<companyid>Goliath Corp</companyid>
<locationid></locationid>
<sessiontimestamp>2022-02-07T20:21:01+00:00</sessiontimestamp>
<sessiontimeout>2022-02-08T20:21:01+00:00</sessiontimeout>
</authentication>
<result>
<status>success</status>
<function>query</function>
<controlid>a92cb391-6647-494d-bfca-c52d677be181</controlid>
<data listtype="VENDOR" totalcount="18" offset="0" count="18" numremaining="0">
<VENDOR>
<RECORDNO>1010</RECORDNO>
<PAYTOCONTACT.CONTACTNAME>Ackman, Alice</PAYTOCONTACT.CONTACTNAME>
<TOTALDUE>3797</TOTALDUE>
</VENDOR>
<VENDOR>
<RECORDNO>1009</RECORDNO>
<PAYTOCONTACT.CONTACTNAME>Halpert, Jim</PAYTOCONTACT.CONTACTNAME>
<TOTALDUE>2987</TOTALDUE>
</VENDOR>
</data>
</result>
</operation>
</response>
CSV
RECORDNO,PAYTOCONTACT.CONTACTNAME,TOTALDUE
1010,"Ackman, Alice",3797
1009,"Halpert, Jim",2987
JSON
[
{
"RECORDNO": "1010",
"PAYTOCONTACT.CONTACTNAME": "Ackman, Alice",
"TOTALDUE": "3797"
},
{
"RECORDNO": "1009",
"PAYTOCONTACT.CONTACTNAME": "Halpert, Jim",
"TOTALDUE": "2987"
}
]
Case sensitivity
Queries are case sensitive by default, but you can choose a case-insensitive query with the options
element.
Values: true
, false
<options>
<caseinsensitive>true</caseinsensitive>
</options>
Query into private entities
By default, in a multi-entity company, queries from the top-level entity do not access data in private entities. You can set the showprivate
option to true
if you want to query data in private entities.
Values: true
, false
<options>
<showprivate>true</showprivate>
</options>
Page size
Specifies the number of results per returned set.
Default value: 100
Maximum value: 2000
<pagesize>200</pagesize>
If you have more than 2000 total records, see offsets below.
Offset
If you have more than 2000 total records (which is the maximum page size), use offsets to return sets of results until there are no more.
Note: Each call with a different offset is a new query and not a continuation of any previous query. Any changes to the underlying data that happen between calls may affect query results. For example, if there is a query that returns 2000 records, and then new records are added, and then there is another query request with an offset of 2000, records may be missed due to the new records changing the result set.
For example, the following skips the first 10 results.
<offset>10</offset>
Transaction type (docparid)
Specifies the transaction definition type (or document type) for SODOCUMENT
, PODOCUMENT
, or INVDOCUMENT
records. You must use this to take advantage of any custom fields on the transaction definition.
<query>
<select>
<field>RECORDNO</field>
<field>STATE</field>
</select>
<object>SODOCUMENT</object>
<docparid>Sales Order</docparid>
</query>
Tips
When using aggregate functions, avoid using record number in the select
element. For example, consider the following query:
...
<query>
<select>
<field>CUSTOMERID</field>
<field>RECORDNO</field>
<count>RECORDNO</count>
</select>
<object>ARINVOICE</object>
</query>
The results will provide a separate entry for each record in order to include its record number, so your count does not work as expected. (Each record has an entry with a count of 1):
<ARINVOICE>
<CUSTOMERID>C-00019</CUSTOMERID>
<RECORDNO>122</RECORDNO>
<COUNT.RECORDNO>1</COUNT.RECORDNO>
</ARINVOICE>
<ARINVOICE>
<CUSTOMERID>C-00019</CUSTOMERID>
<RECORDNO>128</RECORDNO>
<COUNT.RECORDNO>1</COUNT.RECORDNO>
</ARINVOICE>
Using readByQuery (legacy)
Let’s look at a simple readByQuery
example. The following 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>
Use rownum to specify the rows that readByQuery returns
When querying custom objects, you might want to limit your query to rows that you specify. The rownum
condition returns a number for each row that readByQuery
returns, so you can reduce the amount of data returned to just what you need. For example, use rownum
to verify if a query result will contain expected data by returning a set of 5 rows instead of all rows. Or you might want to target rows in your query that were added later; if so, you can select rows greater than a specified rownum
.
In the following example, four fields of the depreciation_schedule
object are queried using the specified criteria; rownum
is used to limit the query to the first row found.
<function controlid="2194e274-ceca-4779-bffa-779166f52122_204175">
<readByQuery>
<object>depreciation_schedule</object>
<fields>Rasset,Rasset_class,posting_date,depreciation_amount</fields>
<pagesize>10</pagesize>
<query>id in (347839,347840,347841,347842) and (status = 'posted' OR status = 'summary_posted') and rownum = 1 </query>
</readByQuery>
</function>
The following filter conditions return only the rows specified:
- rownum = 1 returns row 1
- rownum < 5 returns rows 1 - 4
- rownum > 5 returns rows 6 and greater.
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. 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.
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
When doing NULL comparisons, you can use IS NOT NULL
or IS NULL
.
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.
The like
operator supports wildcards:
- Percent sign (
%
) to match zero, one, or multiple characters - Underscore (
_
) to match a single character
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 (< ) |
< |
Greater than (> ) |
> |
Ampersand (& ) |
& |
Examples
The following shows examples of queries on both standard and custom objects.
Object | Query | Purpose |
---|---|---|
VENDOR |
STATUS = 'F' AND ONETIME = 'T' |
List inactive and one-time vendors (by querying on boolean values). |
DEPARTMENT |
DEPARTMENTID IN ('10','20','15','11','12','8','3','4','100','99') |
List departments with any of these IDs. |
CONTACT |
CONTACTNAME = 'Erin\'s Software' |
List contacts with the given name. Note the escaped apostrophe. |
ACTIVITYLOG |
OBJ_ID > 2000 AND CREATED_AT >= '04/19/2021 12:00:00' AND CREATED_AT < '04/20/2021 12:00:00' |
List audit trail logs for all records on April 19, 2021. 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 2021' |
List account balances for the given period. |
GLACCOUNTBALANCE |
PERIOD = 'Month Ended June 2021' 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 <= 50 AND Rasset = 'Thinkpad T61' AND posting_date >= '02/01/2022' |
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. |
VENDOR |
NOT VENDORID = 'V1234' |
List vendors with vendor IDs other than V1234 . |
EMPLOYEE |
EMPLOYEEID like 'b%' |
List employees whose IDs start with the letter b . |
EMPLOYEE |
EMPLOYEEID like '%b%' |
List employees whose IDs include the letter b at any position. |
EMPLOYEE |
EMPLOYEEID like '_b%' |
List employees whose IDs have the letter b as the second character. |
Tips
-
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 asfalse
to reflect the value in the Sage Intacct UI, when you attempt to return these with areadByQuery
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:
-
Consult the API documentation for the object of interest. Look under the
readByQuery
heading and consult a secondary query table (if present). See Account Balances for an example. -
Use the Sage Intacct GUI to look up object field integration names through Object Definition pages.
-
Use the
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.