We had a scenario to add a new field in the OData entity, which is the result of the calculation of another field; this calculation should work only for certain warehouses.
- Warehouse WH001 – 70% of the available on-hand value
- Warehouse WH004 – 70% of the available on-hand value
- Warehouse WH005 – 70% of the 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 the view method for multiplication
2) How to use the view method with nested if-else conditions
High-level resolution steps
- Add a calculated real field in the entity extension
- Write an 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 the SQL level. If you want to add it as part of the view field, you can create a calculated method and make the output part of the field list.
We had a requirement to calculate 70% of the on-hand quantity for three warehouses (WH001, WH004, and WH005), and for the rest of the warehouses, the 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 an extension for the entity InventWarehouseOnHandV2Entity and added a new calculated field.
Step-2: Create extension class
Then create a new extension class for the 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
