Modelling X++ queries for dynamically joining multiple data sources

Queries are powerful components, used very frequently in X++ ecosystems. But, sometimes, they lack the flexibility we would want when we are tackling complex joining scenarios. The next showcase is an example of how you can join, in X++, a data source to another data source that is one or more levels above it’s nearest parent in the hierarchy of a query.

When it comes to applying this kind of relations, from the query interface it is straightforward. Following the example above, let’s say we want to join TaxTrans with VendInvoiceTrans. You simply add the relation, and its properties:

When you want to do it from X++, there is another story, because for 1 data source you can add only 1 data source, if you add multiple, they will overwrite each other.

taxTransDS = vendInvoiceTransDS.addDataSource(tableNum(TaxTrans)); // works and the parent of taxTransDS will be vendInvoiceTransDS

But if you add another one :

taxTransDS = vendTransDS.addDataSource(tableNum(TaxTrans)); // still works but not, the parent of taxTransDS will be vendTransDS

In this scenario, what is left to do is add the .addRange clause ( that works like a “where” clause in T-SQL ) and trick the interpreter to think this is a filtering clause when, instead, this is an Inner Join clause ( disclaimer : this only works for Inner Join scenarios )

And the code snippet:

vendInvoiceJourDS = query.addDataSource(tableNum(VendInvoiceJour));

vendInvoiceTransDS = vendInvoiceJourDS.addDataSource(tableNum(VendInvoiceTrans));
link = vendInvoiceTransDS.addLink(fieldNum(VendInvoiceJour, PurchId), fieldNum(VendInvoiceTrans, PurchId));
link = vendInvoiceTransDS.addLink(fieldNum(VendInvoiceJour, InvoiceId), fieldNum(VendInvoiceTrans, InvoiceId));
link = vendInvoiceTransDS.addLink(fieldNum(VendInvoiceJour, InvoiceDate), fieldNum(VendInvoiceTrans, InvoiceDate));
link = vendInvoiceTransDS.addLink(fieldNum(VendInvoiceJour, numberSequenceGroup), fieldNum(VendInvoiceTrans, numberSequenceGroup));
link = vendInvoiceTransDS.addLink(fieldNum(VendInvoiceJour, InternalInvoiceId), fieldNum(VendInvoiceTrans, InternalInvoiceId));
vendInvoiceTransDS.joinMode(JoinMode::InnerJoin);
vendInvoiceTransDS.fetchMode(QueryFetchMode::One2One);

vendTransDS = vendInvoiceJourDS.addDataSource(tableNum(VendTrans));
link = vendTransDS.addLink(fieldNum(VendInvoiceJour, InvoiceId), fieldNum(VendTrans, Invoice));
link = vendTransDS.addLink(fieldNum(VendInvoiceJour, InvoiceDate), fieldNum(VendTrans, TransDate));
link = vendTransDS.addLink(fieldNum(VendInvoiceJour, LedgerVoucher), fieldNum(VendTrans, Voucher));
link = vendTransDS.addLink(fieldNum(VendInvoiceJour, InvoiceAccount), fieldNum(VendTrans, AccountNum));
vendTransDS.joinMode(JoinMode::InnerJoin);
vendTransDS.fetchMode(QueryFetchMode::One2One);
transTypeRange = vendTransDS.addRange(fieldNum(VendTrans, TransType));
transTypeRange.value(SysQuery::value(LedgerTransType::Purch));
transTypeRange.status(RangeStatus::Open);
transDateRange = vendTransDS.addRange(fieldNum(VendTrans, TransDate));
transDateRange.value(SysQuery::range(_fromDate, _toDate));
transDateRange.status(RangeStatus::Open);

taxTransDS = vendInvoiceTransDS.addDataSource(tableNum(TaxTrans));
link = taxTransDS.addLink(fieldNum(VendInvoiceTrans, RecId), fieldNum(TaxTrans, SourceRecId));
taxTransDS.joinMode(JoinMode::InnerJoin);
taxTransDS.fetchMode(QueryFetchMode::One2One);

taxTransRange = taxTransDS.addRange(fieldNum(TaxTrans, Voucher));
taxTransRange.value(strFmt("(%1.Voucher == %2.Voucher)",taxTransDS.name(), vendTransDS.name()));

Thanks for reading this article, hope it was helpful 😊.