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 moreWe can help you with D365 FO Computed column in View ( Returning Field in View )
If you want help implementing, troubleshooting, or improving this product, contact us and we’ll point you in the right direction.
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 FO - Send Email with attachments by X++
Go to System administration >> Setup >> Email >> System email templatesCreate New template [ExtensionOf(clas...
D365 FO Data entity Export using X++
This post provides information on exporting data entity through X++ in D365 FO. The post includes a code snippet written in X++ that can be us...
D365 FO Encryption And Decryption Using A Symmetric Key(AES) using X++
If you're looking to encrypt and decrypt data using a symmetric key in D365 FO, this tutorial is the right place to be. The post details the s...
Get multiple selected rows using D365 X++
This blog post offers a valuable insight into D365 X++, offering a detailed guide on how to get multiple selected rows. Multi-select for Butto...
D365 Solution for Cloud Dev machine show error ' The remote certificate is invalid according to the validation procedure. '
You are getting 'The remote certificate is invalid according to the validation procedure.'Error show with all reports and tired Ro...