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