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 – 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 – 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

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); … Continue reading D365FFO – AX – QueryBuildDataSource SortOrder syntax

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

This example : Query q = new query();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 … Continue reading 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 … Continue reading 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 … Continue reading 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 … Continue reading Expressions in query ranges

AX / D365FO – 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 … Continue reading AX / D365FO – X++ – Expressions in query ranges