Dynamics Community Forum Thread Details

problem creating dynamic query in DAX4

I want to create a query in a class, using dialog values.

The query should basically represent the following SQL-Statement: (plus some other restricting values for custTable if selected)

while SELECT * FROM CustTable
        JOIN SUM(InvoiceAmount) FROM CustInvoiceJour
            GROUP BY InvoiceAccount
            WHERE CustTable.AccountNum == CustInvoiceJour .InvoiceAccount
            && ((CustInvoiceJour .InvoiceDate >= fromDate && CustInvoiceJour .InvoiceDate<=toDate))

My code for query looks like this

    QueryBuildDatasource    qbdsCustomers, qbdsInvoiceJour;
    Query                   query = new Query();
    ;

    qbdsCustomers       = query.addDataSource(tablenum(CustTable));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, AccountNum)).value(custAccount == "" ? SysQuery::valueUnlimited() : custAccount);
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, LineDisc)).value(lineDiscCode == "" ? SysQuery::valueUnlimited() : lineDiscCode);
    if (blockedActive)
        SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, Blocked)).value(queryValue(custBlocked));
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, CountryRegionId)).value(country);
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, EndDisc)).value(endDiscCode == "" ? SysQuery::valueUnlimited() : endDiscCode);
    SysQuery::findOrCreateRange(qbdsCustomers, fieldnum(CustTable, MOCTotalVolume)).value(totalVolume == 0 ? SysQuery::valueUnlimited() : queryValue(totalVolume));

    if( !(fromDate == datenull() && toDate == datenull()) )
    {
        qbdsInvoiceJour     = qbdsCustomers.addDataSource(tablenum(CustInvoiceJour));
        qbdsInvoiceJour.orderMode(OrderMode::GroupBy);
        qbdsInvoiceJour.relations(false);
        qbdsInvoiceJour.addLink(fieldnum(CustTable,AccountNum), fieldnum(CustInvoiceJour,InvoiceAccount));
        qbdsInvoiceJour.addSelectionField(fieldNum(CustInvoiceJour,InvoiceAmount),SelectionField::Sum);
        qbdsInvoiceJour.addSortField(fieldnum(CustInvoiceJour,InvoiceAccount));
        SysQuery::findOrCreateRange(qbdsInvoiceJour, fieldnum(CustInvoiceJour, InvoiceAccount)).value(custAccount == "" ? SysQuery::valueUnlimited() : custAccount);
        SysQuery::findOrCreateRange(qbdsInvoiceJour, fieldnum(CustInvoiceJour, InvoiceDate)).value(queryrange(fromDate, toDate));
    }

The query does not find any customers but there should be

Quick Links

Leaderboard