I'm setting up a QueryRange and found that if I pass my filter field an empty string the query doesn't run the range. I would have expected all records where the range field is empty to be shown but not… How can I solve this problem? Easy!! Instead of using an empty string I use … Continue reading AX / D365FO – How to add empty ranges in query
Category: QueryBuildDataSource
AX / D365FO – GroupBy on Lookup problem
I have a custom lookup with a GroupBy inside Query statement First time, when the is nothing choosen the group by works fine. If I choose something from list and again want to change it, now the group by is lost. To solve the issue simply use sysTableLookup.parmUseLookupValue(false) like show in below code public void … Continue reading AX / D365FO – GroupBy on Lookup problem
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 – 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 – 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 – D365FO – Query Object Datasource Linking
static void QueryExample(Args _args) { Query query; QueryBuildDatasource datasource; ; query = new Query(); // Add SalesTable main datasource datasource = query.addDataSource(tableNum(SalesTable)); // Add child datasource "SalesLine" to previously created DS datasource = datasource.addDataSource(tableNum(SalesLine)); // Set the join mode datasource.joinMode(JoinMode::InnerJoin); // Indicate you don't want to use relations automatically datasource.relations(false); // Add link between parent … Continue reading AX – D365FO – Query Object Datasource Linking
AX – D365FO – The data source is not embedded within a (parent) data source.
This error comes when you try to add new datasource to the top of the query. QueryBuildDataSource qbds;qbds = query.addDataSource(tableNum(newTable)); // This is wrong You should add new table to the Main datasource instead. query.dataSourceTable(tableNum(newTable)).addDataSource(..) // This is correct
AX – D365FO – X++ – Retrieve multiple occurrences of a DataSource in a QueryBuilDer with dataSourceTable method
When you make a query it can happen that you use more than one data source for the same table. If you use the query builder you will have to set the filters on the right data source. If you have multiple data sources for the same table the only way to select the correct … Continue reading AX – D365FO – X++ – Retrieve multiple occurrences of a DataSource in a QueryBuilDer with dataSourceTable method
AX – D365FO – Advanced filtering in code X++ on Query object
So, you are creating the query programmatically (of course using the Query* classes), and you need a statement like select * from tableA where columnB = val1 or columnC = val2 just do it like: QueryBuildDataSource qbds; QueryBuildRange qbr; str range; // initializing the query and the datasource(s) here; will go like qbds = … Continue reading AX – D365FO – Advanced filtering in code X++ on Query object
AX / Dynamics 365 For Finance and Operations blog 