web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics 365 F & O - Knowledge sharing by B... / Advanced Computed Columns i...

Advanced Computed Columns in D365FO with X++: Complex Case Statements

Views (40)
Bharani Preetham Peraka Profile Picture Bharani Preetham Pe... 3,634 Moderator
In my previous blog, I have gone through on a general approach how to create a computed method which is so straight forward. The current writing is something more bigger. Lets dig deeper.

Problem:

As the key importance of a computed field is to improve performance and also while exporting from a Data Entity, Skip Staging is the step we do more often for faster export where computed field is still needed instead of Virtual field. The difficulty here is when working on converting a display method to a  computed field which is having many interlinked methods.

On the same note, I recently received a requirement to build an entity for Cost Objects where this form has many display methods and the client always uses this Skip Staging functionality.

Solution:

I made similar Entity for physical fields using InventSum, InventDim and InventTableModule tables.

The Average Unit Cost is the field which is basically a display method that took some time to investigate and find the right solution.

And finally made the below Computed Field.

I used a select statement and using limited resources like Item and site with some case and Grouping statements.

    private static server str getAvgUnitCost()
    {
        str avgUnitCost;
        str postedValue;
        str physicalValue;

        str itemId = SysComputedColumn::returnField(viewStr(InventSumBySiteEntity),
        identifierStr(InventSumBySite),
        fieldStr(InventSumBySite, ItemId));

        str siteId = SysComputedColumn::returnField(viewStr(InventSumBySiteEntity),
        identifierStr(InventSumBySite),
            fieldStr(InventSumBySite, InventSiteId));

        str dataArea = SysComputedColumn::returnField(viewStr(InventSumBySiteEntity),
        identifierStr(InventSumBySite),
            fieldStr(InventSumBySite, InventSumDataAreaId));

        avgUnitCost = strFmt(@'SELECT
                                CASE
                                WHEN (SUM(INVENTSUM.POSTEDQTY) - ABS(SUM(INVENTSUM.DEDUCTED)) + SUM(INVENTSUM.RECEIVED)) IS NULL
                                OR (SUM(INVENTSUM.POSTEDQTY) - ABS(SUM(INVENTSUM.DEDUCTED)) + SUM(INVENTSUM.RECEIVED)) = 0
                                THEN 0.0
                                ELSE
                                ABS(
                                (SUM(INVENTSUM.POSTEDVALUE) + SUM(INVENTSUM.PHYSICALVALUE)) /
                                (SUM(INVENTSUM.POSTEDQTY) - ABS(SUM(INVENTSUM.DEDUCTED)) + SUM(INVENTSUM.RECEIVED))
                                )
                                END AS AVGUNITCOST
                                FROM INVENTSUM
                                WHERE INVENTSUM.ITEMID = %1
                                AND INVENTSUM.INVENTSITEID = %2
                                AND INVENTSUM.DATAAREAID = %3
                                GROUP BY INVENTSUM.ITEMID, INVENTSUM.INVENTSITEID', itemId, siteId, dataArea);
        return avgUnitCost;
    }


The idea here is basically to give the Average Unit Cost of that item in that Site, which is equal to total sum of PostedValue and PhysicalValue which is divided by sum of Received Quantity and difference of Posted and Deducted Quantity. 

If we have PhysicalValue, PostedValue or Deducted values then we can leverage SysComputedColumn Class methods.

Below is the implementation for the same

    private static server str getAvgUnitCost()
    {
        str avgUnitCost;

        str postedVal = SysComputedColumn::returnField(viewStr(InventWarehouseOnHandV2Entity),
        identifierStr(InventWarehouseOnHandAggregatedView),
        fieldStr(InventWarehouseOnHandAggregatedView, PostedValue));

        str postedQty = SysComputedColumn::returnField(viewStr(InventWarehouseOnHandV2Entity),
        identifierStr(InventWarehouseOnHandAggregatedView),
        fieldStr(InventWarehouseOnHandAggregatedView, PostedQty));

        str physicalVal = SysComputedColumn::returnField(viewStr(InventWarehouseOnHandV2Entity),
        identifierStr(InventWarehouseOnHandAggregatedView),
            fieldStr(InventWarehouseOnHandAggregatedView, PhysicalValue));

        str deducted = SysComputedColumn::returnField(viewStr(InventWarehouseOnHandV2Entity),
        identifierStr(InventWarehouseOnHandAggregatedView),
            fieldStr(InventWarehouseOnHandAggregatedView, Deducted));

        str received = SysComputedColumn::returnField(viewStr(InventWarehouseOnHandV2Entity),
        identifierStr(InventWarehouseOnHandAggregatedView),
            fieldStr(InventWarehouseOnHandAggregatedView, Received));

        str totalQtyExp = strFmt('%1 - ABS(%2) + %3', postedQty, deducted, received);
        str totalValue = strFmt('%1 + %2', postedVal, physicalVal);


        avgUnitCost = SysComputedColumn::if(
            SysComputedColumn::equalExpression(totalQtyExp, SysComputedColumn::returnLiteral(0)),   // Checking if totalQtyExp is 0
            SysComputedColumn::returnLiteral(0),					                                // If 0 then return 0
            SysComputedColumn::abs(SysComputedColumn::divide(totalValue, totalQtyExp))              // Else divide both i.e., totalValue and totalQtyExp
            );

        return avgUnitCost;
    }

The actual display method can be seen at InventCostOnhandItem Form and at InventSum datasource methods with name averageCostPriceUnit(). From the UI, this can be obtained from Released Products > Manage Costs > Cost Objects > Average Unit Cost.


With this we have seen some tricky Switch statement in SQL and then used that in our X++ to write a computed method.

​​​​​​​For better view of the code, try checking in a laptop.



Happy Learning!

PS: The Entity or table names looks like standard but they aren't standard. I just used these names for demo purpose.

Comments