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
Related posts
D365 Sending Email with Customer Account Statement SSRS report as attachment using X++
D365 Sending Email with Customer Account Statement SSRS report as attachment using X++ custTable _custTable; &...
clicking link on info message X++ to Open form
Message::AddAction() method can be used to embed an action within a message sent to the message bar. This method supports adding a singl...
D365 F&O - Create custom workflow in Dynamics 365
This guide will explain every step needed to create your custom workflow in Dynamics 365. Create Enum The base enum is used to define the ...
D365 FO Finance and Operations : AOT Browser
In Dynamics 365 Finance and Operations the AOT is not accessible from the user interface It is only available in a development bo...
D365 FO - Extensible Data Security (XDS)
This post covers D365 FO's Extensible Data Security (XDS) system, which enables the creation of flexible data-security policies beyond the def...
Power BI with D365FO
If you're looking to integrate Power BI with D365FO, this video tutorial is a great resource to get you started. The video tutorial explains t...
D365FO - SQL get security details (such as Security roles, duties and privileges)
Learn how to obtain detailed information about security roles, duties, and privileges in D365FO through SQL queries in this post. Whether you ...
D365 F&O LCS Generate SAS link to Download FASTER large bacpac file
If you've encountered difficulties with downloading large backup .bacpac files from LCS, this tutorial may come in handy! The author shares a ...
Form Control Event Handler Methods in Dynamics 365
For example, In Dynamics 365 for Operations you can react to the OnClicked event by copying the event handler method for the event and p...
D365 F&O setup email notifications on workflows
How to setup email notifications on workflows in D365 F&OToday we’re going to see how to manage workflow notifications in D365 Suppl...