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


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?

Provide feedback