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

AX – D365FO – Complex criteria with combined AND and OR conditions

Find all records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard range syntax. This example shows how to do that Note also that in this example, we are using the fieldStr() method to specify our actual field names … Continue reading AX – D365FO – Complex criteria with combined AND and OR conditions