How to Add a Computed Column in a Data Entity in D365 FO using x++

 

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);

 }

Useful DateTimeUtil Methods in D365 FO X++ (Dynamics AX / Dynamics 365 Finance & Operations)

Working with date and time is a very common requirement when developing solutions in Dynamics AX and Dynamics 365 Finance & Operations. Whether it’s handling batch jobs, logging execution time, applying business rules based on dates or converting values across time zones, date-time handling needs to be done carefully.

In X++ DateTimeUtil plays a key role in managing all date and time–related operations. It provides several built-in methods to convert dates, handle UTCDateTime values and work with user or system time zones in a reliable way.

During my development work, I have frequently used the following DateTimeUtil methods. I have collected them here as a quick reference that can help developers understand and use date-time operations more effectively in Dynamics AX / D365 FO.

Below is a list of commonly used DateTimeUtil methods with examples that you can use in your day-to-day X++ development.

// Get Past date time

DateTimeUtil::addDays(DateTimeUtil::utcNow(), -1);

// Convert Date to DateTime 

 FromDateTime fromDateTime = DateTimeUtil::newDateTime(Fromdatefilter.dateValue(), 0);

 ToDateTime toDateTime = DateTimeUtil::newDateTime(ToDateFilter.dateValue(), timeMax());

// Today date
date today = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());

//Convert DateTime to Date
DateTimeUtil::date(ledgerJournalTable.PostedDateTime)

//Convert to company date
 public date convertToCompanyDate(utcDateTime _dateTime)

 {

     return HcmDateTimeUtil::convertToCompanyDate(DateTimeUtil::removeTimeZoneOffset(_dateTime, userTimeZone));

 }

//Get min and max DateTime value 

utcdatetime validFromDate = DateTimeUtil::minValue();

utcdatetime validToDate = DateTimeUtil::maxValue();

//Get current year from date

 int defaultYear = year(DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone()));

//Last date in year

Date lastDayInYear = DateTimeUtil::getEndOfYearDate(locale, firstDayInYear);

How to Use CrossCompany in a Query in Dynamics 365 F&O (X++)

While working in Dynamics 365 Finance & Operations, there are many situations where we need to read data from multiple legal entities instead of just the current company.

By default, any query or select statement in X++ only fetches records from the company you are logged into. But what if you want data from all companies?

That’s where CrossCompany comes into the picture. 

What Is CrossCompany in D365 F&O?

CrossCompany allows you to read records from multiple legal entities in a single query without changing the company context.

Normally, D365 F&O automatically applies a DataAreaId filter behind the scenes. When CrossCompany is enabled, this filter is ignored and data is returned from all companies that the user has access to.

Example: Using CrossCompany in a Query

Below is a simple example where we want to read InventLocation records from all companies.

Query query = new Query();

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

queryBuildDataSource = query.addDataSource(tableNum(InventLocation));

query.allowCrossCompany(true);

How to cancel sales line in D365 F&O using X++

Sometimes in D365 Finance & Operations, we run into situations where we need to cancel a sales order line through code instead of manually doing it from the form. This usually comes up in automation scenarios, integrations or custom business processes.

Recently I had a requirement where a sales line needed to be cancelled using code. Instead of directly updating fields, I used the standard framework logic. In this post, I will share a simple and clean way to do it.

Implementation : Cancelling a Sales Line

In my case, I created a table extension for SalesLine and added a custom method that can be called whenever we need to cancel a specific line.

[ExtensionOf(tableStr(SalesLine))]
final class SalesLine_Extension
{
    public void gauCancelSalesOrderLine()
    {
        ttsbegin;
        this.selectForUpdate(true);
        SalesUpdateRemain::construct().updateDeliverRemainder(this, 0, 0);
        ttscommit;
    }
}

How to call Runbase batch job using X++ in D365 F&O

If there's a requirement to call a RunBaseBatch class using X++ code, the following example demonstrates how to do it. In this case, we’re using the GUPItemBasePriceCalcJob class, which is a RunBaseBatch class and calling it by setting the appropriate parameters.        

        BatchHeader         header;

        SysRecurrenceData   sysRecurrenceData;

        Batch               batch;

        BatchJob            batchJob;

        BatchInfo           processBatchInfo;

        BatchRetries        noOfRetriesOnFailure = 4;

        GUPItemBasePriceCalcJob GUPItemBasePriceCalcJob; // RunBase batch class

        #define.timeInSecondsDelay(20)

      

        select forupdate batch

        join batchJob

        where batchJob.RecId == batch.BatchJobId

                && batch.ClassNumber == classnum(GUPItemBasePriceCalcJob)

                && batchJob.Status == BatchStatus::Waiting

                && batch.Company == curext();

        if (!batch)

        {

            // Setup the RunBaseBatch Job

            header = BatchHeader::construct();

            GUPItemBasePriceCalcJob= GUPItemBasePriceCalcJob::construct();

            GUPItemBasePriceCalcJob.parmItemId(ItemId); // pass Item Id 

            GUPItemBasePriceCalcJob.parmSiteId(SiteId); // pass Site 

            processBatchInfo = GUPItemBasePriceCalcJob.batchInfo();

            processBatchInfo.parmRetriesOnFailure(noOfRetriesOnFailure);

            header.addTask(GUPItemBasePriceCalcJob);            

            // Set the recurrence data

            sysRecurrenceData = SysRecurrence::defaultRecurrence();

            sysRecurrenceData = SysRecurrence::setRecurrenceStartDateTime(sysRecurrenceData,                             DateTimeUtil::addSeconds(DateTimeUtil::utcNow(), #timeInSecondsDelay));

            sysRecurrenceData = SysRecurrence::setRecurrenceNoEnd(sysRecurrenceData);

            sysRecurrenceData = SysRecurrence::setRecurrenceEndAfter(sysRecurrenceData, 1); 

            header.parmRecurrenceData(sysRecurrenceData);

            // Set the batch alert configurations

            header.parmAlerts(NoYes::No, NoYes::Yes, NoYes::No, NoYes::Yes, NoYes::No);

            header.save();