D365 FO Computed column in View ( Returning Field in View )
The technique to add a computed column begins with you adding a static method to the view. The method must return a string. The system automatically concatenates the returned string with other strings that the system generates to form an entire T-SQL create view statement.
The method that you add to the view typically has at least one call to the DictView.computedColumnString method. The parameters into the computedColumnString method include the name of a data source on the view, and one field name from that data source. The computedColumnString method returns the name of the field after qualifying the name with the alias of the associated table. For example, if the computedColumnString method is given the field name of AccountNum, it returns a string such as A.AccountNum or B.AccountNum.
1 - Create Your View
2- Add a Static Method to the View
Here is an example which illustrates how can we add computed columns to return Year , Month and substring from field in view
private static server str ProjectID()
{
#define.CompView(SG_BudgetLineView)
#define.CompDS(DimensionAttributeValueCombination)
#define.DisplayValue(DisplayValue)
str LedgerAccount;
LedgerAccount = return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #DisplayValue));
return "SUBSTRING(" + LedgerAccount + ", 10 , 5)";
}
private static server str Year()
{
str yearId;
yearId = SysComputedColumn::returnField(identifierStr("BudgetLineView"),identifierStr("BudgetTransactionLine"),identifierStr("Date"));
return "YEAR(" + yearId + ")";
}
private static server str compTotalCostPrice()
{
#define.CompView(SWProjForecastCost)
#define.CompDS(ProjForecastCost)
#define.QtyCol(Qty)
#define.PriceCol(CostPrice)
return SysComputedColumn::multiply(
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompDS),
fieldStr(#CompDS, #PriceCol)
),
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompDS),
fieldStr(#CompDS, #QtyCol)
)
);
}
Computed column; Returning Enum Value in Viewpublic static server str compGeneralTransType()
{
return SysComputedColumn::returnLiteral(Transaction::ProjectInvoice);
}
Computed column; Returning Field in Viewpublic static server str compAmount()
{
#define.CompView(SWProjForecastCost)
#define.CompDS(ProjForecastCost)
#define.CostPrice(CostPrice)
return SysComputedColumn::returnField(tableStr(#CompView), identifierStr(#CompDS), fieldStr(#CompDS, #CostPrice));
}
Computed column; Case Statement in Viewpublic static server str TransType()
{
#define.CompDS(ProjForecastCost)
#define.CompView(SWProjForecastCost)
str ret;
str ModelId = SysComputedColumn::returnField(identifierStr(SWProjForecastCost), identifierStr(ProjForecastCost), identifierStr(ModelId));
ret = "case " + modelId +
" when 'Sales' then 'Forecast Sales' " +
" when 'Orders' then 'Forecast Orders' " +
" when 'Latest' then 'Forecast Latest' " +
" end";
return ret;
}Case Statement for this view looks like in SQL server as below;
CASE T2.MODELID WHEN 'Sales' THEN 'Forecast Sales' WHEN 'Orders' THEN 'Forecast Orders' WHEN 'Latest' THEN 'Forecast Latest' END
Published on:
Learn more