Data entity view – Multiplication and Nested IF Conditions

We had scenario to add a new field in OData entity which is the result of calculation of another field, moreover this calculation should work only for certain warehouses.

· Warehouse WH001 – 70% of available on hand value

· Warehouse WH004 – 70% of available on hand value

· Warehouse WH005 – 70% of available on hand value

· Other warehouses should return 100% value

During the course of this blog, you will learn the following:

1) How to use view method for multiplication

2) How to use view method with nested if-else conditions


High level resolution steps

· Add a calculated real field in the entity extension

· Write entity level view method for calculating performing multiplication / if condition and return

OData URL: DynamicsURL/data/WarehousesOnHandV2?cross-company=true

Entity name: InventWarehouseOnHandV2Entity


Detailed resolution steps

View methods are compiled at SQL level, any fields you want to add as part of the view field, you can create a calculated method and make the output part of the field list.

We had a required to calculate 70% of on hand quantity for three warehouses (WH001, WH004 and WH005) and for rest of the warehouses system should display the 100% on hand quantity.

Here are the steps followed.

Step-1: Add a new real calculated field in entity extension

Created extension for the entity InventWarehouseOnHandV2Entity and added a new calculated field.


Step-2: Create extension class

Then create a new extension class for data entity method and add following codes.


    
    

///   Created for calculating available quantity with 70% and 100%


[ExtensionOf (dataentityviewstr(InventWarehouseOnHandV2Entity))]

internal final class SCC_InventWarehouseOnHandV2Entity_Extension

{


/// Based on the warehouse type   inputs modifies the percentage


/// Calculated available quantity

public static server str getSCCAvailableQty()

{

return SysComputedColumn::if(

SysComputedColumn::equalExpression(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, InventoryWarehouseId)),

SysComputedColumn::comparisonLiteral(“WH001”)),

SysComputedColumn::multiply(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, AvailableOnHandQuantity)), ‘0.7’),

SysComputedColumn::if(

SysComputedColumn::equalExpression(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, InventoryWarehouseId)),

SysComputedColumn::comparisonLiteral(“WH004”)),

SysComputedColumn::multiply(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, AvailableOnHandQuantity)), ‘0.7’),

SysComputedColumn::if(

SysComputedColumn::equalExpression(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, InventoryWarehouseId)),

SysComputedColumn::comparisonLiteral(“WH005”)),

SysComputedColumn::multiply(SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, AvailableOnHandQuantity)), ‘0.7’),

SysComputedColumn::returnField(dataentityviewstr(InventWarehouseOnHandV2Entity),

identifierStr(InventWarehouseOnHandAggregatedView), fieldStr(InventWarehouseOnHandAggregatedView, AvailableOnHandQuantity)))));

}

}
    
    
    

Step-3: Output: Run the OData to check result

Post that run the following OData URL in the browser to get the output JSON.

· DynamicsURL/data/WarehousesOnHandV2?cross-company=true