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 .NET examples.
• You successfully ran the getting started example—in particular, you set up the credentials.ini file required by Bootstrap.cs.
• 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 Run function in Query.cs. 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 creates a list of two filters that will be used in the query, then creates an OrOperator that is based on those two filter conditions:

List<IFilter> filterList = new List<IFilter>();
filterList.Add((new Filter("CUSTOMERID")).SetLike("c%"));
filterList.Add((new Filter("CUSTOMERID")).SetLike("1%"));
OrOperator filter = new OrOperator(filterList);

Next the code creates an OrderBuilder that sorts results based on descending customer ID.

OrderBuilder orderBuilder = new OrderBuilder();
IOrder[] orders = orderBuilder.Descending("CUSTOMERID").GetOrders();

This is followed by the SelectBuilder, which 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.

SelectBuilder selectBuilder = new SelectBuilder();
ISelect[] fields = selectBuilder.
    Fields(new[] {"CUSTOMERID","CUSTOMERNAME"}).
    Sum("TOTALDUE").
    GetFields();

The QueryFunction 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:

QueryFunction query = new QueryFunction()
{
    SelectFields = fields,
    FromObject = "ARINVOICE",
    Filter =  filter,
    CaseInsensitive = true,
    PageSize = 100,
    OrderBy = orders
};

Run the example

1. Build and run the project, either from your IDE or the command line. For example, from the command line:

   cd Intacct.Examples
   "C:\Program Files\dotnet\dotnet.exe" build Intacct.Examples.csproj
   "C:\Program Files\dotnet\dotnet.exe" bin/Debug/netcoreappX.X/Intacct.Examples.dll
   

   The console menu appears:

    Available examples:
    1 - Getting started
    2 - List AR invoices
    3 - List vendors (legacy)
    4 - CRUD customer
    5 - Custom object function
    6 - Exit program
   

2. Type 2 to choose the example and press enter.

3. 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 across all their invoices.

   Success! Number of ARINVOICE objects found: 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.
   

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

   // Filter =  filter,
   

5. Comment out the Sum aggregate operator and the associated console message and build/run the query again.

   // Sum("TOTALDUE").
   

    // Console.WriteLine("SUM.TOTALDUE: " + json.First["ARINVOICE"]["SUM.TOTALDUE"].Value);
   

   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.

6. Open the generated log file, for example, Intacct.Examples\bin\Debug\netcoreappX.X\logs\intacct.log, and examine the entries.

Extra credit

Try a different object

Modify Query.cs 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.cs file, change all occurrences of CUSTOMERID and CUSTOMERNAME to VENDORID and VENDORNAME.

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

What’s next?

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

Provide feedback