AX / D365FO – Use a SQL Server function in an D365FO View

Need to add a SQL Server function to make some smart calculation into a D365FO AOT View based on Query?

In this example I will show how to do that.

I will use the FORMAT date function to convert a simple date in YYYYMMDD format

First of all you must have a view based on query. If you don’t have it and want to generate one follow this article : AX / D365FO – CREATE AN AOT VIEW BASED ON A QUERY

Now to add your function you must add a Computed Column to your view

Add a computed column to the View

In this example I will create a new computed column that shows 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

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