How to create a computed column in a view through x++ in D365 F&O ?

Computed columns in D365 Finance and Operations (D365 F&O) are extremely useful when you want to add dynamic values in views, which are calculated at runtime using SQL. Instead of storing values in tables, computed columns help improve performance and reduce storage. 

When you have an AOT query where sometimes adding additional data source causes returning of duplicate records and then if this query is used in a view which is then being used in an inquiry form will result in displaying wrong data. In such scenarios Computed Columns in a view help us a lot. 

In this post, I will show you step-by-step how to create a computed column using X++ that gets column value from a table in D365 FO based on a value returned by a data source in an AOT query.

Below mentioned is a step by step process to achieve the same : - 

Step 1: Create a New View

1. Go to AOT → Data Model → Views

2. Right-click → New View

3. Rename it: LedgerVoucherComputedView

Step 2: Add Data Sources

Add the desired table (e.g., VendPackingSlipJour) to the view.

Step 3: Add Fields

Add basic fields you want in the view like PackingSlipId, DataAreaId, DeliveryDate, etc.

Step 4: Add Computed Column

1. Right-click View → New → Computed Column

2. Rename it: VoucherNo

Step 5: Write X++ Logic for the Computed Column

Now we define the computation using X++ static method.

Create a static method for computed column in the View

public static str computePhysicalVoucher()
{
    DictView  myView = new DictView(tableNum(LedgerVoucherComputedView));
    str primaryDatasourcename = myView.query().dataSourceTable(tablenum(VendPackingSlipJour)).name();

    str physicalvoucher = strFmt(@"SELECT TOP 1 SubledgerVoucher FROM GeneralJournalEntry WHERE GeneralJournalEntry.DocumentNumber = %1 
                               and GeneralJournalEntry.SubledgerVoucherDataAreaId = %2
                               and GeneralJournalEntry.AccountingDate = %3",
                               SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,PackingSlipId)),
                               SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,DataAreaId)),
        SysComputedColumn::returnField(tableStr(LedgerVoucherComputedView),primaryDatasourcename,fieldStr(VendPackingSlipJour,DeliveryDate)));
   
    return physicalvoucher;

}

Step 6: Attach the method to the computed column

• Select your computed column (VoucherNo)
• In Properties → View Method → set value to : computePhysicalVoucher

Conclusion

Computed columns in D365 F&O are powerful for real-time calculations without storing unnecessary data. Using X++, we can dynamically build SQL expressions and boost performance.