D365FO – AX – How to add TWO links between two datasource in X++ Query

Reference article : https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/188732/how-to-add-two-links-between-two-datasource-in-x-query

Here’s a job I just wrote.

static void Job1(Args _args)
{
    Query                       q;
    QueryBuildDataSource        qbds;
    QueryBuildRange             qbr;
    QueryRun                    qr;
    str                         value;
    InventJournalTrans          inventJournalTrans;
    InventTransOrigin           inventTransOrigin;
    ;
    
    q = new Query();
    
    qbds = q.addDataSource(tableNum(InventJournalTable));
    
    qbr = qbds.addRange(fieldNum(InventJournalTable, JournalType));
    qbr.value(SysQuery::value(InventJournalType::Transfer));
    
    qbds = qbds.addDataSource(tableNum(InventJournalTrans));
    qbds.relations(true);
    qbds.fetchMode(QueryFetchMode::One2Many);
    
    qbds = qbds.addDataSource(tableNum(InventTransOrigin));
    
    qbr = qbds.addRange(fieldNum(InventTransOrigin, InventTransId));
    
    value = strFmt("((%1.INVENTTRANSID == %2.INVENTTRANSID) || (%1.INVENTTRANSID == %2.TOINVENTTRANSID))",
                qbds.name(), qbds.parentDataSource().name());
    
    qbr.value(value);
    
    qr = new QueryRun(q);
    while (qr.next())
    {
        inventJournalTrans = qr.get(tableNum(InventJournalTrans));
        inventTransOrigin = qr.get(tableNum(InventTransOrigin));
        
        info(strFmt("%1 %2", inventJournalTrans.RecId, inventTransOrigin.RecId));
    }
}

It produces the following query.

SELECT * 
  FROM InventJournalTable(InventJournalTable_1) 
    WHERE ((JournalType = 2)) 
  JOIN * 
  FROM InventJournalTrans(InventJournalTrans_1) 
    ON InventJournalTable.JournalId = InventJournalTrans.JournalId 
  JOIN * 
  FROM InventTransOrigin(InventTransOrigin_1) 
    ON ((((InventTransOrigin_1.INVENTTRANSID == InventJournalTrans_1.INVENTTRANSID) 
       || (InventTransOrigin_1.INVENTTRANSID == InventJournalTrans_1.TOINVENTTRANSID))))

The results show that I get 2 InventTransOrigin records for every InventJournalTrans record.

Message (02:26:39 am)
5637144945 5637145368
5637144945 5637145369
5637145018 5637145448
5637145018 5637145449