In the new 2020 Release 1 by Sage Intacct, a new powerful Query function has been introduced. It accepts expressions comprising of XML elements with a well defined schema and has more robust error checking. It allows building case-insensitive and strong query expressions. It provides more efficient data fetching over the legacy readByQuery function. But as of now, it can only be used for querying Standard objects.
As compared to readByQuery function, it allows you to use more operators; also supports sorting and aggregate operations such as sum or average. We can provide pagesize and offsets to fetch only specific number of rows satisfying the mentioned condition. Some complex conditions which were not feasible earlier can now be performed with this function.
Unlike readByQuery, the new Query function can help you retrieve data of related objects. It has eliminated the round trips needed to separately query the related object based on the RecordNo fetched from the response of previous request. Also, filters can be applied on related fields.
Query function comprises of :
- Object element – Integration name of the object you want to query.
- Select element – It consists of field element. Field element takes integration name of the field whose value you want to fetch. Multiple field elements can be there in a select element. Also, aggregate function elements like sum,average,count,etc. are included in select element.
- Filter element – In this element various mathematical, logical and conditional operator elements like equalto, greaterthan, like,between,and,or,isnull etc. can be written. In short the elements to perform filtering of data go in here.
- Order by element – Elements to carry out sorting of result set are mentioned here.
- Options element – Whether filter query needs to be case-insensitive or not is passed within this element.
- Pagesize element – Page size of result set to be returned.
- Transaction type (docparid) element – This is used for Transaction documents like SO,PO. Transaction type or Transaction definition is to be mentioned here.
Examples:
<query>
<object>APBILL</object>
<select>
<field>VENDORNAME</field>
<count>RECORDNO</count>
<field>VENDOR.VENDTYPE</field>
</select>
<filter>
<and>
<greaterthan>
<field>VENDOR.CREDITLIMIT </field>
<value>100</value>
</greaterthan>
<or>
<notlike>
<field>VENDOR.VENDTYPE</field>
<value>_O%</value>
</notlike>
<equalto>
<field>VENDOR.VENDTYPE</field>
<value>Wholesale</value>
</equalto>
</or>
</and>
</filter>
</query>
The above query will return records from APBILL object, satisfying the mentioned “AND” and “OR” conditions on Vendor Type and Credit limit. Count function will give the number of APBills present in APBILL object for the tagged VendorName. Wildcards( _ ,%) can also be used in filtering.
<query>
<object>ARINVOICEITEM</object>
<select>
<field>ARINVOICE.RECORDID</field>
<field>ARINVOICE.CUSTOMERID</field>
<field>ARINVOICE.CUSTOMERNAME</field>
<field>LINE_NO</field>
<field>ITEMID</field>
</select>
<offset>20</offset>
<pagesize>2000</pagesize>
<orderby>
<order>
<field>ARINVOICE.RECORDID</field>
<ascending />
</order>
</orderby>
</query>
The above query will skip first 20 records (as offset is set to 20) and return next 2000 records of AR INVOICE line items of different Invoices. Also, offsets can be set on dataset counting from backwards, by ordering the dataset in descending order.
ARINVOICE.CUSTOMERID, ARINVOICE.CUSTOMERNAME will get the Customer of the related ARINVOICE header. Sorting will be performed on basis of Invoice Number (ARINVOICE.RECORDID).
We saw how useful and easy to use this query function is. For more of such examples refer https://developer.intacct.com/web-services/queries/