X++ code using SysComputedColumn to use max and min aggregate functions in D365FO

Hi,

In this post, we will view the code to use max and min aggregate functions of SysComputedColumn in D365FO. 

Step 1: Created a view DAXCustomerTransMeasures(namely) and added tables CustTable and CustTrans as its datasources.

Added fields AccountNum, TransType, NumberOfTrans, MaxAmount and MinAmount and group by on fields AccountNum and TransType.

Step 2: Created a method "computeMaxAmount" to display the maximum amount for a customer for different transaction types using Max method of SysComputedColumn.

public static str computeMaxAmount()
    {
        TableName   viewName      = tableStr(DAXCustomerTransMeasures); //Entity name
        str         accountNum    = SysComputedColumn::returnField(viewName, identifierStr(CustTrans), identifierStr(AccountNum));
        str         amountCurStr  = SysComputedColumn::returnField(viewName, identifierStr(CustTrans), identifierStr(AmountCur));
        
        str amountCurTotal = SysComputedColumn::max(amountCurStr);

        return SysComputedColumn::if(SysComputedColumn::isNullExpression(amountCurTotal),
									 SysComputedColumn::returnLiteral(0),
									 amountCurTotal);
		
    }

Assigned view method "computeMaxAmount" to the field "MaxAmount".

Step 3: Created a method "computeMinAmount" to display the minimum amount for a customer for different transaction types using Min method of SysComputedColumn.

public static str computeMinAmount()
    {
        TableName   viewName      = tableStr(DAXCustomerTransMeasures); //Entity name
        str         accountNum    = SysComputedColumn::returnField(viewName, identifierStr(CustTrans), identifierStr(AccountNum));
        str         amountCurStr  = SysComputedColumn::returnField(viewName, identifierStr(CustTrans), identifierStr(AmountCur));
        
        str amountCurTotal = SysComputedColumn::min(amountCurStr);

        return SysComputedColumn::if(SysComputedColumn::isNullExpression(amountCurTotal),
                                     SysComputedColumn::returnLiteral(0),
                                     amountCurTotal);
  
    }

Assigned view method "computeMinAmount" to the field "MinAmount".

Step 4:  Created a method "computeRecordCount" to display the record count for a customer for different transaction types using count method of SysComputedColumn.

public static str computeRecordCount()
    {
        return SysComputedColumn::count('DISTINCT '  
                    SysComputedColumn::returnField(viewStr(DAXCustomerTransMeasures), identifierStr(CustTrans), fieldStr(CustTrans, RecId)));
    }

Assigned view method "computeRecordCount" to the field "NumberOfTrans".

Step 4: Build the solution. In the table browser able to see the maximum amount, minimum amount and records count for a customer for different transaction types.

Output:

Regards,

Chaitanya Golla