AX / D365FO – FIlter an AOT Query by multiple Enum types fields

Are you creating an AOT Query and want to create a filter condition by multiple ENUM types field? In the AOT query you can do it by using a data source Range object and separate value by comma In the value field property just insert the numeric or string value of the the ENUM fields. In this example I … Continue reading AX / D365FO – FIlter an AOT Query by multiple Enum types fields

AX / D365FO – How to test the Query syntax and results of a Query

Have you created an AOT Query and want to check if the syntax is correct and the results are what you expected? You can create a Runnable class like show below class TestQuery { public static void main(Args _args) { /* Example job used to test query results with.: */ QueryRun queryRun; Counter totalRecords; // … Continue reading AX / D365FO – How to test the Query syntax and results of a Query

AX / D365FO – How to create automatic join relationship between tables in an AOT Query

Are you creating an AOT query and want to join 2 or more tables but don't know the relationships between them? No problem! You can use the "Use Relations" property like shown below In this example I want to to use the native relation between InventSerial and InventSum tables (which is InventSerial.ItemId = InventSum.ItemId). By … Continue reading AX / D365FO – How to create automatic join relationship between tables in an AOT Query

AX / D365FO – How to show Query Builder Select syntax in an Infolog

To show the SQL command of a Query builder in an infolog just use the ToString() method like shown in code below Query q = new Query(); QueryBuildDataSource qbds,qbds1,qbds2; QueryBuildRange qbr; QueryRun qr; SalesLine salesLine = SalesLine::findInventTransId('xxxxxxx'); //InventTrans qbds = q.addDataSource(tableNum(InventTrans)); qbr = qbds.addRange(fieldNum(InventTrans, StatusIssue)); qbr.value(queryValue(StatusIssue::ReservPhysical)); //InventTransOrigin qbds1 = qbds.addDataSource(tableNum(InventTransOrigin)); qbds1.relations(true); qbds1.joinMode(JoinMode::InnerJoin); qbds1.fetchmode(QueryFetchMode::One2One); qbds1.addRange(fieldNum(InventTransOrigin, InventTransId)).value(salesLine.InventTransId); … Continue reading AX / D365FO – How to show Query Builder Select syntax in an Infolog

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 manuallyTable > choose the table of query data … Continue reading AX / D365FO – Create an AOT View based on a Query #d365fo #ax #msdyn365fo

AX – D365FO – Count records in Query #d365fo

If your query has just one datasource you can use SysQuery::countTotal static void CountProjTableRecords(Args _args) { Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbd; qbd = query.addDataSource(tablenum(ProjTable)); queryRun = new QueryRun(query); info(strfmt("Total Records in Query %1", SysQuery::countTotal(queryRun))); // total records 1130 } If we need to count number of records of a query with … Continue reading AX – D365FO – Count records in Query #d365fo

AX – D365FO – “OR” conditions in a QueryBuildRange for a same field

I want to add a list of values in an "OR" Condition for a same field in a QueryBuildRange. The result must be something like that select * from CustGroup where custGroup.CustGroup == '50' || custGroup.Group =='90'; To do that Just add range and assign value on the same field multiple times as shown in code … Continue reading AX – D365FO – “OR” conditions in a QueryBuildRange for a same field

AX – D365FO – Save previously used Query in a RunBaseBacth class

If yoou want to save last used Query on a RunBaseBatch class just set TRUE the QueryRun.saveUserSetup() method like show in the example below public QueryRun queryRun() { return gQueryRun; } public void initQueryRun() { Query query = new Query(); QueryBuildDataSource inventJournalTableDS; QueryBuildRange inventJournalStatus; QueryBuildRange inventJournalStatusRange; inventJournalTableDS = query.addDataSource(tableNum(InventJournalTable)); inventJournalStatus = inventJournalTableDS.addRange(fieldNum(InventJournalTable, Posted)); inventJournalStatus = … Continue reading AX – D365FO – Save previously used Query in a RunBaseBacth class