Microsoft Dynamics AX (Archived)

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

(0) Share

Report

Posted on by Johnkrish

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

All responses (11)

Answers (1)

Johnkrish 781 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

Hi Brandon,

Thank you very much your kind help, it helped me a lot in solving my issue.

Regards,

Johnkrish
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report
RE: 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
Brandon Wiese 17,788 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

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.
Johnkrish 781 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

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
Johnkrish 781 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

yes i aware of that
Brandon Wiese 17,788 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

You're aware that you're going to get a 1:n relationship between InventJournalTrans and InventTransOrigin?
Brandon Wiese 17,788 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

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.
Johnkrish 781 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

Yes i've queryBuildDataSource4 as InventTrans table
Johnkrish 781 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

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?.
Brandon Wiese 17,788 on at

Like (0)

Report

RE: How to add TWO links between two datasource in X++ Query

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.