Skip to main content

X++ code to get the max date using the SQL query and SysComputedColumn in D365FO

Hi,

In this post we can view the code to get the max date by comparing different dates and display the SQL query and SysComputedColumn in D365FO.

Please refer to this post to know about the details of the view DAXPurchAgreementDetails.

( ) X code using SysComputedColumn with a SQL query in D365FO. - Dynamics 365 Finance Community

Step 1: Created a new field on the view DAXPurchAgreementDetails by name MaxDateValue to display only the date value.

Step 2: Created a method "getMaxDateValue" to compare the dates ConfirmedDlv, DeliveryDate and LocalDeliveryDate of purchase order and display the max value.

public static server str getMaxDateValue()
    {
        TableName   viewName              = tableStr(DAXPurchAgreementDetails);
        str         confirmedDlv          = SysComputedColumn::returnField(viewName, identifierStr(PurchTable), fieldStr(PurchTable, ConfirmedDlv));
        str         deliveryDate          = SysComputedColumn::returnField(viewName, identifierStr(PurchTable), fieldStr(PurchTable, DeliveryDate));
        str         localDeliveryDate     = SysComputedColumn::returnField(viewName, identifierStr(PurchTable), fieldStr(PurchTable, LocalDeliveryDate));
        str         sqlQuery;

        sqlQuery = strfmt('(SELECT MAX(V) FROM (VALUES (%1), (%2), (%3)) AS VALUE (V))',
                    confirmedDlv,
                    deliveryDate,
					localDeliveryDate);

        return sqlQuery;
    }
Step 3:   Assigned view method "getMaxDateValue" to the newly created field "MaxDateValue ".
 

Step 4: Build the solution. In the table browser able to see the max value of dates of purchase orders.

Output:

Regards,

Chaitanya Golla

Comments

*This post is locked for comments