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)); } }

AX – D365FO – Clear a value of a previously query set filter

If you have done a query in a form and you you want to clean previouslu query filter you have to follo w this code public void executeQuery() { // Get the filter value from the filter control. date firstYearOfMonth; str monthStr; str yearStr; MonthsOfYear monthEnum; int monthEnumToInt; TransDate tmpDate; TransDate dateInMonth; TransDate firstDateOfMonth; TransDate … Continue reading AX – D365FO – Clear a value of a previously query set filter

AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)

CustInvoiceJour custInvoiceJour; CustInvoiceTrans custInvoiceTrans1; CustInvoiceTrans custInvoiceTrans2; while select InvoiceAccount,InvoiceId from custInvoiceJour group by InvoiceAccount outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1 where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId && custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011′) && custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011′) outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2 where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId && custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012′) && custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012′) { info( strFmt("Customer:%1 2011:%2 2012:%3", … Continue reading AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)

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); … Continue reading 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); … Continue reading 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 … Continue reading D365FO – AX – Multiple tables join with Query AddLink syntax example