Are you creating an AOT Query and want to create a filter condition by an ENUM field? In the AOT query you can do it by using a data source Range object In the value field property just insert the numeric or string value of the the ENUM field. In this example I used the string value … Continue reading AX / D365fo – Filter by Enum value in an AOT Query
Category: Query
AX / D365FO – Use Wildcards (..LIKE SYNTAX..) in AOT Query objects
Are you creating an AOT Query and want to use a wildcard filter? An example could be that you want to filter all the items that starts with a specific word. In an ideal world you would use the SQL keyword "LIKE". In the AOT query you can do it by using a data source … Continue reading AX / D365FO – Use Wildcards (..LIKE SYNTAX..) in AOT Query objects
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
AX / Dynamics 365 For Finance and Operations blog 