Dynamics Community Forum Thread Details

Date Computed Column in View

Hi. I'm getting the latest VendPurchOrderJour.PurchOrderDate for a Date computed column in a view.

I do this because I want this field to be able for sorting when it's used in a grid.

Here is the original query in it's display method. Since I only want the latest date, I select firstonly and sort it in descending order:

select firstOnly PurchOrderDate from vendPurchOrderJour
Order by vendPurchOrderJour.PurchOrderDocNum desc
where vendPurchOrderJour.PurchId == purchTable.PurchId;

Here is my view:

PurchId field is taken from PurchTable.

poDateStr is String Computed Column which View method is latestPODate() method.

poDateDate is Date Computed Column which View method is supposed to be PODate() method.

Basically here is what written is both latestPODate() and PODate(). The only difference is latestPODate returns String and PODate returns Date.

public static server date PODate()
{
    SysDictTable dt = new SysDictTable(tableNum(VendPurchOrderJour));

    DictView dv = new DictView(tableNum(RMCostView));

    date s = str2Date(strFmt('SELECT TOP 1 %1 FROM %2 WHERE %2.%3 = %4 ORDER BY %5 DESC',
        dt.fieldName(fieldNum(VendPurchOrderJour, PurchOrderDate), DbBackend::Sql),
        dt.name(DbBackend::Sql),
        dt.fieldName(fieldNum(VendPurchOrderJour, PurchId), DbBackend::Sql),
        dv.computedColumnString(tableStr(PurchTable), fieldStr(PurchTable, PurchId), FieldNameGenerationMode::WhereClause),
        dt.fieldName(fieldNum(vendPurchOrderJour, PurchOrderDocNum), DbBackend::Sql)),123);

    return str2Date(strFmt('ISNULL((%1), \'\')', s),123);
}