In Dynamics 365 Finance and Operations, data entities often require fields that aren't directly mapped to a physical database table. These are known as unmapped fields, and they come in two flavors: Virtual Fields and Computed Columns
While both serve unique purposes, computed columns are generally preferred for read operations because they are calculated at the SQL Server level, whereas virtual fields are calculated row-by-row in X++
In this post, we will walk through the steps to create a computed column using X++
1. Create an Unmapped Field
• Open your Data Entity in the Visual Studio designer.
• Right-click the Fields node and select New > String Unmapped Field (or the data type appropriate for your needs)
2. Configure the Properties
In the Properties pane for your new field, set the following:
• Is Computed Field: Set this to Yes.
• DataEntityView Method: Enter the name of the X++ method you are about to create
3. Write the Method
You must now create a static method on the Data Entity that returns a string. This string is the actual T-SQL expression that SQL Server will use.
Example -
public static str languageId()
{
TableName viewName = tableStr(InventItemIdLookup);
str translatedLanguageIdField = SysComputedColumn::returnField(viewName, tableStr(EcoResProductTranslations), fieldStr(EcoResProductTranslations, LanguageId));
return SysComputedColumn::if(
SysComputedColumn::isNullExpression(translatedLanguageIdField),
SysComputedColumn::returnLiteral(''),
translatedLanguageIdField);
}
4. Build and Synchronize
This is the most critical step. Since computed columns exist in the SQL view, the column will not function and may cause errors until the database is synchronized.
Real-World X++ Method Examples -
Example 1 -
public static str GetWeightDimension()
{
str DefaultDimension = SysComputedColumn::returnField(
tableStr(VendProductReceiptHeaderEntity),
identifierStr(PurchTable),
fieldStr(PurchTable, DefaultDimension));
str weightDimension = '(select di.DISPLAYVALUE from DIMENSIONATTRIBUTEVALUESETITEM di ' +
'JOIN DimensionAttributeValue ON di.DimensionAttributeValue = DimensionAttributeValue.RecId ' +
'JOIN DimensionAttribute ON DimensionAttribute.RecId = DimensionAttributeValue.DimensionAttribute ' +
'where di.DIMENSIONATTRIBUTEVALUESET = ' + DefaultDimension + ' and DimensionAttribute.NAME = \’Weight\’)’;
return weightDimension;
}
Example - 2
public static str getTrade()
{
str trade;
str orderAccount = SysComputedColumn::returnField(
tableStr(VendProductReceiptHeaderEntity),
identifierStr(VendPackingSlipJour),
fieldStr(VendPackingSlipJour, OrderAccount));
str siteId = SysComputedColumn::returnField(
tableStr(VendProductReceiptHeaderEntity),
identifierStr(PurchTable),
fieldStr(PurchTable, InventSiteId));
str locationId = SysComputedColumn::returnField(
tableStr(VendProductReceiptHeaderEntity),
identifierStr(PurchTable),
fieldStr(PurchTable, InventLocationId));
trade = strFmt("(select top 1 trade from TradeSettingsSetup where vendorAccount = %1 and Site = %2 and WH = %3)",
orderAccount, siteId, locationId);
return trade;
}
Example - 3
public static str calculateSystemCurrencyAmount(
Name _viewName,
Name _dataSourceName,
Name _accountingCurrencyAmountFieldName,
Name _currencyDataSource,
Name _accountingCurrencyCodeFieldName,
Name _exchangeRateTable = tablestr(ExchangeRateEffectiveViewToday),
Name _exchangeRate = fieldStr(ExchangeRateEffectiveViewToday, CrossRate),
Name _systemParameters = tablestr(SystemParameters),
Name _systemCurrencyCode = fieldStr(SystemParameters, SystemCurrencyCode))
{
List accountingMatchSystemCurrency = new List(Types::String);
accountingMatchSystemCurrency.addEnd(SysComputedColumn::equalExpression(
SysComputedColumn::comparisonField(
_viewName,
_currencyDataSource,
_accountingCurrencyCodeFieldName
),
SysComputedColumn::comparisonField(
_viewName,
_systemParameters,
_systemCurrencyCode))
);
return SysComputedColumn::if(
SysComputedColumn::and(accountingMatchSystemCurrency),
SysComputedColumn::returnField(
_viewName,
_dataSourceName,
_accountingCurrencyAmountFieldName),
SysComputedColumn::divide(
SysComputedColumn::multiply(
SysComputedColumn::returnField(
_viewName,
_dataSourceName,
_accountingCurrencyAmountFieldName),
SysComputedColumn::returnField(
_viewName,
_exchangeRateTable,
_exchangeRate)),
SysComputedColumn::returnLiteral(100))
);
}
Example - 4
private static str getWorkerName(DataSourceName _workerDataSource)
{
str sql = strFmt('SELECT %1 FROM %2 WHERE %3 = %4',
new DictField(tableNum(DirPerson), fieldNum(DirPerson, Name)).name(DbBackend::Sql),
new DictTable(tableNum(DirPerson)).name(DbBackend::Sql),
new DictField(tableNum(DirPerson), fieldNum(DirPerson, RecId)).name(DbBackend::Sql),
SysComputedColumn::returnField(
tableStr(AssetFixedAssetV2Entity),
_workerDataSource,
fieldStr(HcmWorker, Person)));
return sql;
}
Example - 5
private static str computedAmountStr()
{
TableName viewName = tableStr(AssetLeaseAssetTransactionEntity);
str debitAmount = SysComputedColumn::returnField(viewName, identifierStr(AssetLeaseTransactionsEntity), identifierStr(DebitAmount));
str creditAmount = SysComputedColumn::returnField(viewName, identifierStr(AssetLeaseTransactionsEntity), identifierStr(CreditAmount));
return SysComputedColumn::if(SysComputedColumn::equalExpression(debitAmount,
SysComputedColumn::returnLiteral(0)),
SysComputedColumn::negative(creditAmount),
debitAmount);
}
No comments:
Post a Comment