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

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

D365FO – AX – How to add TWO links between two datasource in X++ Query

Reference article : https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/188732/how-to-add-two-links-between-two-datasource-in-x-query Here's a job I just wrote. static void Job1(Args _args) { Query q; QueryBuildDataSource qbds; QueryBuildRange qbr; QueryRun qr; str value; InventJournalTrans inventJournalTrans; InventTransOrigin inventTransOrigin; ; q = new Query(); qbds = q.addDataSource(tableNum(InventJournalTable)); qbr = qbds.addRange(fieldNum(InventJournalTable, JournalType)); qbr.value(SysQuery::value(InventJournalType::Transfer)); qbds = qbds.addDataSource(tableNum(InventJournalTrans)); qbds.relations(true); qbds.fetchMode(QueryFetchMode::One2Many); qbds = qbds.addDataSource(tableNum(InventTransOrigin)); qbr = qbds.addRange(fieldNum(InventTransOrigin, InventTransId)); value … Continua a leggere D365FO – AX – How to add TWO links between two datasource in X++ Query

D365FFO – AX – X++ – Expressions in query ranges

One of least understood but most powerful Axapta features is the so-called Expressions in query ranges syntax. This is not the same as simply using a QueryBuildRange object in a query and specifying a criteria for a single field. Contents  [hide]  1 Introduction2 Syntax3 Examples3.1 Simple criteria3.2 Complex criteria with combined AND and OR clauses3.3 WHERE clauses referencing fields from multiple tables3.4 Conditional joins3.5 Filter on array … Continua a leggere D365FFO – AX – X++ – Expressions in query ranges

Expressions in query ranges

One of least understood but most powerful Axapta features is the so-called Expressions in query ranges syntax. This is not the same as simply using a QueryBuildRange object in a query and specifying a criteria for a single field. Contents  [hide]  1 Introduction2 Syntax3 Examples3.1 Simple criteria3.2 Complex criteria with combined AND and OR clauses3.3 WHERE clauses referencing fields from multiple tables3.4 Conditional joins3.5 Filter on array … Continua a leggere Expressions in query ranges

D365FFO – AX – X++ – Expressions in query ranges

One of least understood but most powerful Axapta features is the so-called Expressions in query ranges syntax. This is not the same as simply using a QueryBuildRange object in a query and specifying a criteria for a single field. Contents  [hide]  1 Introduction2 Syntax3 Examples3.1 Simple criteria3.2 Complex criteria with combined AND and OR clauses3.3 WHERE clauses referencing fields from multiple tables3.4 Conditional joins3.5 Filter on array … Continua a leggere D365FFO – AX – X++ – Expressions in query ranges