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?

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