AX / D365FO – Create an AOT View based on a Query #d365fo #ax #msdyn365fo

Create the AOT Query object

First of all you must create an AOT Query

Open Visual Studio and Add a new Query Item

Add new Data source

Set Query properties

• Dynamic Fields > to automatically include all Table fields choose “Yes”, otherwise chose “No” to select fields manually
• Table > choose the table of query data source

Set Query Ranges

Now I want to do set a couple of predefined filters in my query

Base Enum Range

First I want to filter only the Pricelist of type “Sales” and “Purch”.

The field to be filtered is “PriceDiscTable.Relation “ which is a base enum type

Create a new Range

Choose the “relation” field and add the base enums values separated by comma in Value field

The syntax to use for base enum is PriceType::PriceSales, PriceType::PricePurch

Range based on Date fields

Now I want to add another Range to filter only the valid PriceLists based on the current validity date

To do that we must create this filter : PriceDiscTable.FromDate <= today and PriceDiscTable.ToDate >= Today

Create a new Range, select PriceDiscTable.FromDate field and type ..Day(0) in the value field

The syntax ..Day(0) means Less than or equal to Today

Create a new Range, select PriceDiscTable.ToDate field and type Day(0) ..in the value field

The syntax Day(0).. means Greater than or equal to Today

Add the Query to an AOT View

Go into View “Properties” tab > “Query” > insert your query

Your Query will appear under “View Metadata” section of the view tree

Now open the Query fields node and add to View Fields nodes.

Now Build and syncronize database

Open a SSMS Session to check if your view has been created and the results are extracted

Open View design

Your result view query will be something 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
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')

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