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 … Continua a leggere 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 = … Continua a leggere 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 = … Continua a leggere 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)); } }

AX – D365FO – How to call Form with filtered records using code in AX 2012

Suppose you have a Form and you want to call another form passing some filters. Go to Clicked() method of the "ViewSource" button and insert this code void clicked() { Args args = new Args(); str menuItemDisplay = MenuItemDisplayStr(ERAInventJournalTable); MenuFunction MenuFunction = new MenuFunction(menuItemDisplay, MenuItemType::Display); Query q = new Query(); QueryBuildDataSource qbds = q.addDataSource(tableNum(InventJournalTable)); QBDS.addRange(fieldNum(InventJournalTable,JournalId)).value(ERAStageExtWhsInvrptbal.JournalNameId); … Continua a leggere AX – D365FO – How to call Form with filtered records using code in AX 2012

D365FFO – AX – QueryBuildDataSource SortOrder syntax

static void Cyborg3QueryBuild2(Args _args) { Query query; QueryRun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; VendTable vendTable; query = new query(); qbds = query.addDataSource(tableNum(vendTable)); qbr = qbds.addRange(fieldNum(vendTable, VendGroup)); qbr.value('10'); //here is the line where I thought I could control the sort direction qbds.addSortField(fieldNum(vendTable, AccountNum),SortOrder::Ascending); qr = new QueryRun(query); if(qr.prompt()) { while(qr.next()) { vendTable = qr.get(tableNum(vendTable)); info( vendTable.AccountNum); … Continua a leggere D365FFO – AX – QueryBuildDataSource SortOrder syntax

D365FO – AX – Multiple tables join with Query AddLink syntax example

This example : Query q;QueryRun qr;QueryBuildDataSource qbds;QueryBuildDataSource qbds2;QueryBuildDataSource qbds3;QueryBuildRange qbr; qbds = q.addDataSource(tableNum(CustPackingSlipJour));qbr = qbds.addRange(fieldnum(CustPackingSlipJour, StatusExportDDTRhiagERA));qbr.value(QueryValue(NoYes::No));qbr = qbds.addRange(fieldnum(CustPackingSlipJour, ReturnItemNum));qbr.value(QueryValue("")); qbds3 = qbds.addDataSource(tableNum(EDIDocumentParametersERA));qbds3.relations(false);qbds3.joinMode(JoinMode::InnerJoin);qbds3.fetchmode(QueryFetchMode::One2One);qbds3.addLink(fieldnum(CustPackingSlipJour, OrderAccount),fieldnum(EDIDocumentParametersERA, CustAccount)); qbds2 = qbds.addDataSource(tableNum(InterfaceWMSParameterERA));qbds2.relations(false);qbds2.joinMode(JoinMode::InnerJoin);qbds2.fetchmode(QueryFetchMode::One2One);qbds2.addLink(fieldnum(CustPackingSlipJour, InventLocationId),fieldnum(InterfaceWMSParameterERA, IntInventLocationId));  qr = new QueryRun(q);   while (qr.next()) { //Insert code here } Produces this SQL output: SELECT *FROM CustPackingSlipJourWHERE (StatusExportDDTRhiagERA = 0 AND ReturnItemNum = '')JOIN EDIDocumentParametersERAON CustPackingSlipJour.OrderAccount = EDIDocumentParametersERA.CustAccountJOIN … Continua a leggere D365FO – AX – Multiple tables join with Query AddLink syntax example