D365FO – Format a date of a View with computed column

on

 



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')

Comments

Post a Comment