How to Return Date Difference in Dynamics 365 Finance and Operations (D365FO) Computed Column

In Dynamics 365 Finance and Operations (D365FO), computed columns can dynamically calculate and display values derived from other fields or system values. This guide will show you how to create a computed column that calculates the difference in days between a record's creation date and the current date.

Steps to Create a Computed Column and Method

1. Define the Computed Column in the AOT

1. Open Visual Studio and navigate to the Application Object Tree (AOT).
2. Expand the Data Dictionary node.
3. Right-click on Views and select the custom view / Extension of standad view, where you want to add the computed column (e.g., AllPOLinesView).
4. Click on New > Computed Column.
5. Name your computed column (e.g., ApprovalDays).
6. Set the ComputedColumnExpression property to the method you will define for calculating the date difference (e.g., AllPOLinesView::compColumnApprovalDays()).

2. Implement the Computed Column Method (compColumnApprovalDays)

Add the following method to your view's code to calculate the date difference:

private static server str compColumnApprovalDays()
{
    return SysComputedColumn::getDateDiff(
                SysComputedColumn::returnField(tableStr(AllPOLinesView), identifierStr(PurchLine),
                                        fieldId2name(tableNum(PurchLine), fieldNum(PurchLine, CreatedDateTime))),
                SysComputedColumn::getCurrentUtcDate(),
                SysComputedColumnDatePart::Day);
}
    

Explanation of the Method:

• SysComputedColumn::returnField(tableStr(AllPOLinesView), identifierStr(PurchLine), fieldId2name(tableNum(PurchLine), fieldNum(PurchLine, CreatedDateTime))): Retrieves the CreatedDateTime field from the PurchLine data source in the AllPOLinesView.
• SysComputedColumn::getCurrentUtcDate(): Gets the current UTC date.
• SysComputedColumn::getDateDiff(...): Computes the difference in days between the CreatedDateTime field and the current UTC date.
• SysComputedColumnDatePart::Day: Specifies that the date difference should be calculated in days.

3. Compile and Synchronize

• Compile your changes in Visual Studio.
• Synchronize your database to reflect the changes in D365FO.

Using the Computed Column

After setting up the computed column, it will automatically calculate and display the approval days based on the creation date (CreatedDateTime field) for each record in the AllPOLinesView.

Example Usage

Here’s how you can use the computed column in a form or report:

1. Navigate to the form or report that displays the AllPOLinesView, also make sure ApprovalDays is added on report/Form.
2. The ApprovalDays computed column will show the number of days between the creation date and the current date for each purchase order line.

Summary

By following these steps, you can create a computed column in D365FO to calculate date differences dynamically. This method enhances data presentation and provides valuable insights based on the computed values. Computed columns like this one can automate calculations and streamline your business processes in D365FO.