Advanced query range

Question

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

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

So result should show SalesOrder 001 AND other salesOrders which has at least one SalesLine with LineAmount = 100?

Partake

Answer 1

Jan's solution works fine if sales order '001' should only be selected if it has sales lines. If it doesn't have lines it won't appear in the output.

If it is important to you that sales order '001' should always appear in the output even if it doesn't have sales lines, you can do it via 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);
    }
}

Homestead

Answer 2

The AX select statement supports exists join such as:

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

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

However AX supports query expressions in a query.

Therefore your query should be possible to express like this:

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 lines, it will not be selected. Other than that the output is as you requested:

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

Faucher

Thank you. Solution fits perfectly. I looked at it before, but just now noticed that qst.name(), qsl.name() - is not just a table name, because i tried tableStr(SalesTable) - and got error. Now i see difference. – Partake