web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to add TWO links b...
Microsoft Dynamics AX (Archived)

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

(0) ShareShare
ReportReport
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

I have the same question (0)
  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    You'll want to do something like this.

    qbr = SysQuery::findOrCreateRange(queryBuildDataSource3, fieldNum(InventTransOrigin, InventTransId));

    value = "((%1.InventTransId == %2.InventTransId) || (%1.InventTransId == %2.ToInventTransId))";

    then replace %1 and %2 with the data source names, i.e. InventTransOrigin_ds.name() and InventJournalTrans_ds.name(). 

    qbr.value(value);

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    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.

  • Johnkrish Profile Picture
    Johnkrish 781 on at

    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?.

  • Johnkrish Profile Picture
    Johnkrish 781 on at

    Yes i've queryBuildDataSource4 as InventTrans table

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    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.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    You're aware that you're going to get a 1:n relationship between InventJournalTrans and InventTransOrigin?

  • Johnkrish Profile Picture
    Johnkrish 781 on at

    yes i aware of that

  • Johnkrish Profile Picture
    Johnkrish 781 on at

    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

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    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.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans