List AR Invoices

• Prerequisites
• Examine the code
• Run the example
• Extra credit
  • Try a different object
• What’s next?

This example uses the new Query function to list AR invoices based on attributes of associated customers.

In addition, the query uses a sum aggregate operator to provide information about how much a given customer owes across all invoices.

Prerequisites

• You have downloaded or cloned the Sage Intacct SDK for Node.js examples.
• You successfully ran the getting started example—in particular, you set up the credentials.ini file required by bootstrap.js.
• The company you are using has multiple AR invoices, and you have list and view permissions for AR invoices and customers.

Examine the code

Look at the try block in query.js. Here we build up a query function that will list AR invoices based on attributes of associated customers. Specifically, we’ll look for AR invoices where the customer ID starts with c or 1.

The code first constructs an OrOperator, seeking customer IDs that start with c or 1.

let filter = new IA.Functions.Common.NewQuery.QueryFilter.OrOperator();
filter.addFilter(new IA.Functions.Common.NewQuery.QueryFilter.Filter("CUSTOMERID").like("c%"));
filter.addFilter(new IA.Functions.Common.NewQuery.QueryFilter.Filter("CUSTOMERID").like("1%"));

Next, the OrderBuilder sets a a descending sort order based on the value of CUSTOMERID.

let orderBuilder = new IA.Functions.Common.NewQuery.QueryOrderBy.OrderBuilder();
orderBuilder.addDescending("CUSTOMERID");
const orders = orderBuilder.orders;

The SelectBuilder chooses two fields to return, CUSTOMERID and CUSTOMERNAME, and includes the sum aggregate operator for TOTALDUE. When used in a Query of AR invoices, this adds the values of the TOTALDUE parameters across all invoices. When using aggregate functions, the fields in the select element are used to group the results. So, the results will be grouped by CUSTOMERID first.

let selectBuilder = new IA.Functions.Common.NewQuery.QuerySelect.SelectBuilder();
selectBuilder.addFields(["CUSTOMERID", "CUSTOMERNAME"])
    .addSum("TOTALDUE");
const selects = selectBuilder.selects;

The Query constructor pulls the pieces together and specifies the target of the query, ARINVOICE. The constructor also specifies a case insensitive filter and a pagesize of 100:

let query = new IA.Functions.Common.NewQuery.Query();
query.selectFields = selects;
query.fromObject = "ARINVOICE";
query.filter = filter; // Comment out this line to see all invoices without any filtering
query.caseInsensitive = true;
query.pageSize = 100;
query.orderBy = orders;

Run the example

1. Run the query.js file in a terminal:

   node query.js
   

2. Examine the output, which gives the total number of results and prints the first one.

   The following sample result shows that YellowHammer (CUSTOMERID 10003) owes 15016.44.

   Success! Total number of results: 2
   
   First ARINVOICE result found: {"CUSTOMERID":"10003","CUSTOMERNAME":"YellowHammer","SUM.TOTALDUE":"15016.44"}
   See the log file (logs/intacct.log) for the complete list of results.
   

3. If you don’t get any results, comment out the filter in the Query constructor so that all ARINVOICE objects will be returned and rerun the the example.

   // query.filter = filter;
   

4. Comment out the addSum aggregate operator run the query again.

   // .addSum("TOTALDUE");
   

   The number of results will increase because the AR invoices are not being categorized into groups according to the customer ID. Instead, each AR invoice is returned.

5. Open the generated logs/intacct.log file in an HTML browser and review the entries.

   Note that the log file was created by the logger set up in the bootstrap.php in the project root.

Extra credit

Try a different object

Modify query.js to perform a different query. For example, you might want to calculate the total amount due to a given vendor across all the bills.

1. In the query.js file, change all occurrences of CUSTOMERID and CUSTOMERNAME to VENDORID and VENDORNAME.

2. Similarly, change all occurrences of ARINVOICE to APBILL.

3. Run the file:

   node query.js
   

4. Examine the output, which will look something like this:

    Success! Total number of results: 1
       
    First ARINVOICE result found: {"VENDORID":"20001","VENDORNAME":"PMZ Consultants","SUM.TOTALDUE":"128844.63"}
    See the log file (logs/intacct.log) for the complete list of results.
   

5. (Optional) Update the the console.log statements in the if/else blocks so that they are correct for your updated example.

What’s next?

• Try an example that creates, reads, updates, and deletes a customer.
• Browse the reference documentation for the SDK.

Provide feedback