Dynamics Community Forum Thread Details

building queries

hi,

please am trying to build a query using the code below

static void Job2(Args _args)
{
Query custTransQuery = new Query();
    QueryBuildDataSource custDataSource;
    QueryBuildDataSource transDataSource;
    QueryBuildDataSource businesstransDataSource;
    QueryBuildDataSource reversalTransDataSource;

    custDataSource = custTransQuery.addDataSource(tableNum(CustTable));
    custDataSource.addOrderByField(fieldNum(CustTable, DataAreaId));


    transDataSource = custDataSource.addDataSource(tableNum(CustTrans));

    transDataSource.addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTrans, AccountNum));
    // use outer join because customers without transactions are included
    transDataSource.joinMode(JoinMode::OuterJoin);

    transDataSource.addRange(fieldNum(CustTrans, TransType)).value(strFmt('((%1 != %2) && (%1 != %3))',
                                                                            fieldStr(CustTrans, TransType),
                                                                            any2int(LedgerTransType::ExchAdjustment),
                                                                            any2int(LedgerTransType::Settlement)));
    transDataSource.addOrderByField(fieldNum(CustTrans, DataAreaId));
    transDataSource.addOrderByField(fieldNum(CustTrans, TransDate));
    transDataSource.addOrderByField(fieldNum(CustTrans, Voucher));
     businesstransDataSource = transDataSource.addDataSource(tableNum(DefaultDimensionView1));
    businesstransDataSource.joinMode(JoinMode::InnerJoin);
    businesstransDataSource.addLink(fieldNum(CustTrans, defaultdimension), fieldNum(DefaultDimensionView1, defaultdimension));
       reversalTransDataSource = transDataSource.addDataSource(tableNum(TransactionReversalTrans));
        reversalTransDataSource.joinMode(JoinMode::NoExistsJoin);
        reversalTransDataSource.addLink(fieldNum(CustTrans, TableId), fieldNum(TransactionReversalTrans, RefTableId));
        reversalTransDataSource.addLink(fieldNum(CustTrans, RecId), fieldNum(TransactionReversalTrans, RefRecId));
        reversalTransDataSource.addRange(fieldNum(TransactionReversalTrans, Reversed)).value(enum2str(NoYes::Yes));

    info(custTransQuery.dataSourceNo(1).toString());
}