Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

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

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

  • Johnkrish Profile Picture
    Johnkrish 781 on at
    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 Profile Picture
    Brandon Wiese 17,788 on at
    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 Profile Picture
    Brandon Wiese 17,788 on at
    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 Profile Picture
    Johnkrish 781 on at
    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 Profile Picture
    Johnkrish 781 on at
    RE: How to add TWO links between two datasource in X++ Query

    yes i aware of that

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    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 Profile Picture
    Brandon Wiese 17,788 on at
    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 Profile Picture
    Johnkrish 781 on at
    RE: How to add TWO links between two datasource in X++ Query

    Yes i've queryBuildDataSource4 as InventTrans table

  • Johnkrish Profile Picture
    Johnkrish 781 on at
    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 Profile Picture
    Brandon Wiese 17,788 on at
    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.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans