AX / D365FO – Format a date of a View with computed column #d365fo #ax #msdyn365fo

In this example I will create a new computed column that sjows the FromDate field of the PriceDiscTable in a YYYYMMDD format

Open the view and add a new string computed column

Give it “FromDateStr” name

Now create a new method

The method must be private static server str fromDateformat()

private static server str fromDateformat()
    {
        str         dateStr;
        DictView    dictView;
        str         translationSQLStr;

        dictView = new DictView(tableNum(DEDCorePriceListProsXMLView));

        dateStr = dictView.computedColumnString(tableStr(PriceDiscTable),fieldStr(PriceDiscTable, FromDate),FieldNameGenerationMode::FieldList,true);

        translationSQLStr = strFmt('CONVERT(CHAR(8), %1 , 112)' , dateStr);

        return translationSQLStr;
    }


Now return to the computed column and access property tab.

Insert the previously created method into ”View method” property of the computed field

Build and syncronize the database

Your result query will be like that

SELECT ACCOUNTCODE, ACCOUNTRELATION, AGREEMENT, AGREEMENTHEADEREXT_RU, ALLOCATEMARKUP, AMOUNT, CALENDARDAYS, CURRENCY, DATAAREAID AS DATAAREAID1, DEDCOREECORESCATEGORY, 
DEDCOREPRICEGROUPID, DELIVERYTIME, DISREGARDLEADTIME, FROMDATE, GENERICCURRENCY, INVENTBAILEEFREEDAYS_RU, INVENTDIMID, ITEMCODE, ITEMRELATION, MARKUP, 
MAXIMUMRETAILPRICE_IN, MCRFIXEDAMOUNTCUR, MCRMERCHANDISINGEVENTID, MCRPRICEDISCGROUPTYPE, MODIFIEDDATETIME AS MODIFIEDDATETIME1, MODULE, 
ORIGINALPRICEDISCADMTRANSRECID, PARTITION AS PARTITION1, PDSCALCULATIONID, PERCENT1, PERCENT2, PRICEUNIT, QUANTITYAMOUNTFROM, QUANTITYAMOUNTTO, RECID AS RECID1, 
RECVERSION AS RECVERSION1, RELATION, SEARCHAGAIN, TODATE, UNITID, DATAAREAID, PARTITION, RECID, CAST(CONVERT(CHAR(8), FROMDATE, 112) AS NVARCHAR(10)) AS FROMDATESTR
FROM dbo.PRICEDISCTABLE AS T1
WHERE (RELATION = 4 OR
                         RELATION = 0) AND (FROMDATE <= '01/22/2022 00:00:00') AND (TODATE >= '01/22/2022 00:00:00')

One thought on “AX / D365FO – Format a date of a View with computed column #d365fo #ax #msdyn365fo

  1. Hi Macro , Are you applying filter on From date fields from your computed column method . I am trying to apply filter on my view using Day(0).. and ..Day(0) on From date and Todate fields but it is not working next day when server date changes . I am using View in Data entity . It is working whrn i build and sync same day but not in next day . Can you please help how the date filter change automatically based on server date .

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s