You’re offline. This is a read only version of the page.
781
Hello Experts,
I want to make two relations in a query using InventTransId and ToInventTransId like this
queryBuildDataSource3.addLink(fieldnum(InventJournalTrans,InventTransId),fieldnum(InventTransOrigin,InventTransId));
queryBuildDataSource3.addLink(fieldnum(InventJournalTrans,ToInventTransId),fieldnum(InventTransOrigin,InventTransId));
Note:
using while i achieved like below, but i want to achieve using X++ Query
while select inventJournalName where inventJournalName.JournalType==InventJournalType::Transfer
&& inventJournalName.SEK_CEC_JournalType==SEK_CEC_JournalType::Change
join inventJournalTable where InventJournalTable.JournalNameId==inventJournalName.JournalNameId
join inventJournalTrans where inventJournalTrans.JournalId==inventJournalTable.JournalId
join inventTransOrigin where InventTransOrigin.InventTransId==inventJournalTrans.InventTransId
|| InventTransOrigin.InventTransId==inventJournalTrans.ToInventTransId
Please let me know how to do it.
Thanks in advance,
Johnkrish
*This post is locked for comments
Here's a job I just wrote.
Share
Report
All responses (
Answers (
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
Is that all you're trying to do is find the from and to warehouses? I thought you were trying to drive a form data source or grid or something.
Hi Brandon,
Sorry for the delayed response, i tried different ways, but i couldnt get the correct output, So i did that with while. Here i've added the simplified code, can you convert this code into Query.
static void GetFromAndToWarehouse(Args _args)
{
InventJournalTrans inventJournalTrans;
InventTransOrigin inventTransOrigin;
InventTrans inventTrans;
InventDim inventDim;
while select inventJournalTrans where inventJournalTrans.JournalId=="00081"//inventJournalTable.JournalId
join inventTransOrigin where InventTransOrigin.InventTransId==inventJournalTrans.InventTransId
|| InventTransOrigin.InventTransId==inventJournalTrans.ToInventTransId
join inventTrans where inventTrans.InventTransOrigin==inventTransOrigin.RecId
{
inventDim = InventDim::find(InventTrans.InventDimId);
info(strFmt("%1",inventDim.InventLocationId));
}
}
Thanks in advance
You're aware that you're going to get a 1:n relationship between InventJournalTrans and InventTransOrigin?
If you use .relations(true), then you get the standard relation between the tables and that will interfere with your OR clause.
In your OR clause, you have not qualified your fields with aliases to refer back to the tables. I suspect that is the error you are getting.
Whether you use a relation or a range, you get a WHERE clause either way. If you want to use an OR clause, you have to construct it yourself and use a range. There are good examples elsewhere in the AOT of this. Let me know if you'd like me to find one.
Hi Brandon,
I've tried like this
queryBuildDataSource3=queryBuildDataSource2.addDataSource(tableNum(InventTransOrigin));
queryBuildDataSource3.joinMode(JoinMode::InnerJoin);
queryBuildDataSource3.relations(true);
queryBuildRange1 = SysQuery::findOrCreateRange(queryBuildDataSource3, fieldNum(InventTransOrigin, InventTransId));
queryBuildRange1.value(strFmt('((%1 == %2) || ((%3 == %4)))',fieldStr(InventTransOrigin, InventTransId),fieldStr(InventJournalTrans, InventTransId),fieldStr(InventTransOrigin, InventTransId),fieldStr(InventJournalTrans, ToInventTransId)));
But it is giving error ( Query extended range failure )
I want to make to Relation not Range. Can we make relations with OR conditions like Range?.
It may be queryBuildDataSource4 in your query. You want the value with the OR clause on the InventTransOrigin data source because it refers back to its parent InventJournalTrans data source.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,232
Super User 2024 Season 2
#2
Martin Dráb
230,064
Most Valuable Professional
#3
nmaenpaa
101,156