Calculating Delivered Quantities Using SysComputedColumn

In Dynamics 365 for Finance and Operations, customizing data entities to include computed columns can significantly enhance the analytical capabilities of your business processes. In this article, we’ll explore how to extend the XTSSalesOrderLineV2Entity to calculate the total delivered quantity using the SysComputedColumn.

Understanding SysComputedColumn

The SysComputedColumn framework in Dynamics 365 allows developers to define computed columns in data entities. These columns can contain calculations or aggregations based on other data fields, enabling more sophisticated data analysis and reporting.

Scenario

We need to calculate the total delivered quantity for sales order lines. This includes summing up the quantities from both the custInvoiceTrans and custPackingSlipTrans tables where the InventTransId matches the sales line's InventTransId.

Implementation

public static server str DeliveredInTotal()
{
    DataEntityName dataEntityName = tablestr(SalesOrderLineV2Entity);
    str _inventTransId;
    str qry;

    _inventTransId = SysComputedColumn::returnField(DataEntityName, identifierstr(SalesLine), fieldstr(SalesLine, InventTransId));

    qry = strFmt('select sum(QtyPhysical) from custInvoiceTrans where custInvoiceTrans.InventTransId = %1', _inventTransId);

    qry += strFmt('select sum(Qty) from custPackingSlipTrans where custPackingSlipTrans.InventTransId = %1', _inventTransId);

    return qry;
}        

Explanation

1. DataEntityName Initialization: The DataEntityName is set to the SalesOrderLineV2Entity.
2. InventTransId Extraction: Using SysComputedColumn::returnField, the InventTransId from the SalesLine table is retrieved.
3. Query Construction: Two SQL queries are constructed using strFmt to sum the quantities from custInvoiceTrans and custPackingSlipTrans tables where the InventTransId matches.
4. Returning the Query: Finally, we return the constructed query string.