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 – 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 – D365FO – Clear data source query ranges

If you add a query range with a certain value to a Query, then next time you execute the query with another value the query doesn't clear previous range. In order to clean the old values you have to use clearRanges() method see example below /// </summary> public void executeQuery() { super(); QueryBuildDataSource productCategory_ds = … Continue reading AX – D365FO – Clear data source query ranges

AX – D365FO – Get current data source record

If you want to get the current selected data source record you can use dataSource_ds.cursor() method See code below public void executeQuery() { super(); if(Al0VendorActivity_ds.cursor().RecId != 0) { QueryBuildDataSource productCategory_ds = this.query().dataSourceTable(tableNum(Al0VendorSearchCriteria)); productCategory_ds.addRange(FieldNum(Al0VendorSearchCriteria, VendorActivityRefRecId)).value(int642Str(Al0VendorActivity_ds.cursor().RecId)); } }