With the Sage Intacct 2020 Release 4, the new Query function can now be used for querying Custom Objects as well. In our previous blog we saw how Query Function works on Standard Objects.

In this blog we shall see Query function examples on Custom Objects and how we can get values for fields on related Custom Object and how to use them in filters.

Easy and complex queries including both the standard and custom objects by accessing the relationship can be used to seek the records from both objects. See the example below. “ar_invoice_detail_commission” is a custom object related to standard object “ARINVOICE” with many-to-one relationship type.

To know the fields available for querying, we can get the fields as well as relationships with the lookup function. The lookup function returns a list of all the fields and relationships for standard or custom objects. Only those fields or relationships can be used to query data from that object. The relationship type must be Many-to-One to be able to retrieve the data of the related object, whether it is a standard or a custom object.
The below query will return all the fields and relationships for custom object ar_invoice_detail_commission.

As seen in the previous blog we can add filter sets with different aggregate and summation functions, and offsets for pagination as well. Below is an example for using filters.

The above query will seek all the custom object records along with the records’ related object field (R10374.RECORDID) for mentioned customer id (CUST-0012).

The new Query function is indeed very powerful and time saving. It reduces round trips by fetching data from related objects at once.