AX / D365FO – Using Aggregate functions (Count, Max, Avg…) in a View

Suppose that you want to create a form that show a list of customers with the total count of their sales orders

The result should be something like that..

But how to do that?

You just need to follow these steps :

• Create an AOT Query
• Create an AOT View linked to the previous query
• Add a computed column to the view
• Add a Count aggregate function to the computed column
• Create a simple list form to show results

Create an AOT Query

Create a VS Project

Add a Query object and give it “CustTableWithGroupedOrders” name

Add CustTable to Data Sources

Set Dynamics Fields property : YES

Add SalesTable to the CustTable Data Sources sub node

Set :

• Dynamics Fields property : YES
• Use relations property : YES

Drag AccountNum field from CustTable and drop into GroupBy section

Save the Query

Create an AOT View

Add a new AOT View and give it CustTableWithGroupedOrdersView name

Set Query property : CustTableWithGroupedOrder

Expand View Metadata section, drag AccountNum filed of the CustTable and drop into View Fields section

Add an integer computed column

GIve it “CountSalesOrders” name

Create a new method

The method must be private static server str CountRecords()

    private static server str CountRecords()
    {

        str         viewField;
        DictView    dictView;
        str         translationSQLStr;

        dictView = new DictView(tableNum(CustTableWithGroupedOrdersView));

        viewField = dictView.computedColumnString(tableStr(CustTable),fieldStr(CustTable, AccountNum),FieldNameGenerationMode::FieldList,true);

        translationSQLStr = strFmt('count(%1)' , viewField);

        return translationSQLStr;
    }

Now return to the computed column and access property tab.

Insert the previously created method into ”View method” property of the computed field

Build and syncronize the database

Create a Simple List Form

Create a Form and give it CustTableWithGroupedOrdersForm name

Add all missing object required by the Simple list pattern

Add the view CustTableWithGroupedOrdersView as form Data Source

Drag and drop AccountNum and CountSalesOrders fields to the form grid

Build the project

To test the form you can open it from a menu or add it at startup object and press Start button