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
