D365FO: computed columns

This was my first attempt ever to use computed columns and I spent quite a few hours trying to understand how to implement them properly. There is an article called "Computed columns and virtual fields in data entities" on Microsoft docs, however for me it seems a bit unclear and outdated.

Computed columns are typically used for reading data. They have a good performance because computed columns saved as Views in SQL database.

In order to create a computed column in data entity, a static method should be made, which will return a text representation of a View. This static method will not be executed as standard X++ code, but rather used one time during DBSync to create a View in SQL server. Typically a helper X++ class SysComputedColumn is used to get proper data source and fields references in text format.

Example

Consider the following simplified table setup with important fields in brackets:

• Legal Entity (defaultCurrency)
• Sales Order Headers (currency)
• Sales Order Lines (currency, salesLineType)
• Sales Order Lines Additional Data (amount)

All tables, except the last one, have a field for Currency. The "Sales Order lines additional data" table has amount, the Currency for this one is not saved, but calculated dynamically based on salesLineType field from Sales Order line.

Goal

Create data entity for Sales Order Lines Additional Data table and return currency for the amount. High performance is important.

Solution

Create data entity and add required data sources, configure data source relations. Create new unbound text field, link it to a new static method, which will return string representation of a View. Simplified X++ code below:

private static str formatCurrency()
{
    str dataEntityName = dataentityviewstr(myDataEntity);
    Map valueMap = new Map(Types::String,Types::String);

    // this field contains definition where the currency should be used from
    str salesLineType = SysComputedColumn::returnField(dataEntityName, dataEntityDataSourceStr(myDataEntity, DataSource1), fieldstr(DataSource1, TrueupCurrency));

    str legalEntityCurrency = SysComputedColumn::returnField(dataEntityName, dataEntityDataSourceStr(myDataEntity, DataSource2), fieldstr(DataSource2, CurrencyCode));
    str salesOrderCurrency = SysComputedColumn::returnField(dataEntityName, dataEntityDataSourceStr(myDataEntity, DataSource3), fieldstr(DataSource3, CurrencyCodeInvoicing));
    str salesOrderLineCurrency = SysComputedColumn::returnField(dataEntityName, dataEntityDataSourceStr(myDataEntity, DataSource4), fieldstr(DataSource4, currencyCode));

    valueMap.insert(int2Str(salesLineType::LegalEntity), legalEntityCurrency);
    valueMap.insert(int2Str(salesLineType::SalesOrder), salesOrderCurrency);
    valueMap.insert(int2Str(salesLineType::SalesOrderLine), salesOrderLineCurrency);
    
    str result = SysComputedColumn::switch(salesLineType, valueMap, SysComputedColumn::returnLiteral('Unknown')); 

    return result;
}

The method will return a string similar to the one below:

CASE T7.TRUEUPCURRENCY WHEN 0 THEN T4.CURRENCYCODEINVOICING WHEN 1 THEN T4.CURRENCYCODE WHEN 2 THEN T3.CURRENCYCODE ELSE N'Unknown'

Later on this string will be converted to a SQL View during DBSync.


Last note

Do not use DataEntityName EDT to define "dataEntityName" string variable from the Microsoft's article, mentioned in the beginning of this post, if your data entity name is longer than 40 characters. Name will be truncated and later on DBSync will fail. Bug submitted and accepted by Microsoft.