Extended query range

Question

How to make a request in Ax with advanced filtering (with x ++):

I want to make such filter criteria. In the form SalesTable show SalesTable.SalesId == "001" || SalesLine.LineAmount == 100 SalesTable.SalesId == "001" || SalesLine.LineAmount == 100 .

So the result should show SalesOrder 001 and other SalesOrders that have at least one SalesLine with LineAmount = 100?

Answer 1

AX select supports connection support, for example:

  while select salesTable exits join salesLine where salesLine.SalesId == salesTable.SalesId && salesLine.LineAmount == 100 

X ++ does not support the exists clause as a subquery in the where clause. Therefore, it is impossible to express exists in combination with or .

However, AX does support query expressions in the request.

Therefore, your request should be able to express the following:

 static void TestQuery(Args _args) { SalesTable st; QueryRun qr = new QueryRun(new Query()); QueryBuildDataSource qst = qr.query().addDataSource(tableNum(SalesTable)); QueryBuildDataSource qsl = qst.addDataSource(tableNum(SalesLine)); str qstr = strFmt('((%1.SalesId == "%2") || (%3.LineAmount == %4))', qst.name(), queryValue("001"), qsl.name(), queryValue(100)); qsl.relations(true); // Link on SalesId qsl.joinMode(JoinMode::ExistsJoin); qsl.addRange(fieldNum(SalesLine,RecId)).value(qstr); info(qstr); // This is the query expression info(qst.toString()); // This is the full query while (qr.next()) { st = qr.get(tableNum(SalesTable)); info(st.SalesId); } } 

However, if sales order 001 does not contain rows, it will not be selected. In addition, the output for your request:

((SalesTable_1.SalesId == "001") || (SalesLine_1.LineAmount == 100))

SELECT FIRSTFAST * FROM SalesTable EXISTS JOIN FIRSTFAST * FROM SalesLine WHERE SalesTable.SalesId = SalesLine.SalesId AND ((((SalesTable_1.SalesId == "001") || (SalesLine_1.LineAmount == 100)))))

001

125

175

Answer 2

Jan solution works just fine if sales order “001” should only be selected if it has sales lines. If it has no lines, it will not be displayed in the output.

If it is important for you that the sales order “001” is always displayed in the output, even if it does not have sales lines, you can do this through the union as follows:

 static void AdvancedFiltering(Args _args) { Query q; QueryRun qr; QueryBuildDataSource qbds; SalesTable salesTable; ; q = new Query(); q.queryType(QueryType::Union); qbds = q.addDataSource(tablenum(SalesTable), identifierstr(SalesTable_1)); qbds.fields().dynamic(false); qbds.fields().clearFieldList(); qbds.fields().addField(fieldnum(SalesTable, SalesId)); qbds.addRange(fieldnum(SalesTable, SalesId)).value(queryValue('001')); qbds = q.addDataSource(tablenum(SalesTable), identifierstr(SalesTable_2), UnionType::Union); qbds.fields().dynamic(false); qbds.fields().clearFieldList(); qbds.fields().addField(fieldnum(SalesTable, SalesId)); qbds = qbds.addDataSource(tablenum(SalesLine)); qbds.relations(true); qbds.joinMode(JoinMode::ExistsJoin); qbds.addRange(fieldnum(SalesLine, LineAmount )).value(queryValue(100)); qr = new QueryRun(q); while (qr.next()) { salesTable = qr.get(tablenum(SalesTable)); info(salesTable.SalesId); } }