Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Add link to existing q...
Microsoft Dynamics AX (Archived)

Add link to existing query

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,

I need to add new link to existing query.

I do something like :

    QueryRun            queryRun;
    Query               query;

    QueryBuildDataSource qbds;


     query = queryRun.query('CustTableCube');
     queryRun = new queryRun(query);

/********** *****/
     qbds = queryRun.query().dataSourceNo(1).addDataSource(tableNum(MyTable));

          qbds.relations(true);
          qbds.joinMode(JoinMode::InnerJoin);


     qbds.addLink(fieldNum(CustTable, AccountNum),    fieldNum(MyTable, MyAccount));

    qbds.addRange(fieldNum(CustTable, AccountNum).value('1234');

For some reason the lines after the asterisks are ignored, so when I an doing:

 while (queryRun.next())
        {
/* some code */
}


I am reaching the code in the while clause.

I have tried also moving the line : 

queryRun = new queryRun(query);

just before the loop

and do : 

 qbds = query().dataSourceNo(1).addLink(fieldNum(CustTable, AccountNum),    fieldNum(MyTable, MyAccount));


instead of line:

     qbds = queryRun.query().dataSourceNo(1).addDataSource(tableNum(MyTable));


What may be wrong?

Thanks :)

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Add link to existing query

    1. I have said I did : InventBuyerGroup just for check in purpose (but still in the look).

    2. The key for the solution was QueryFetchMode::One2One - That solved the problem. BTW - Why One2One solved the problem?

    Thanks :)

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,235 Most Valuable Professional on at
    RE: Add link to existing query

    ItemBuyerGroupId is related to InventBuyerGroup.Group, not CustTable.AccountNum. Also, you have the wrong order of fields in addLink().

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Add link to existing query

    Link custTable to custTable is just an example.

    I need link to any table, so I added the lines of

    qbds.relations(true);
qbds.fetchMode(QueryFetchMode::One2One);


    Look at the following code - I in purpose, link (ItemTable::ItemBuyerGroup), which shall not get into the loop, but indeed I still get into the loop while (queryrun.next()) ... 

    Refer to this code (after fixing, as your advise):

        query query;
    queryRun queryRun;
    QueryBuildDataSource qbds;
    DictRelation dictRelation;
    query = new query('CustTableCube');
    qbds = query.dataSourceNo(1).addDataSource(tableNum(InventTable));

    qbds.clearLinks();
    qbds.relations(true);
    qbds.joinMode(JoinMode::InnerJoin);
    qbds.fetchMode(QueryFetchMode::One2One);
    qbds.addLink(fieldNum(InventTable, ItemBuyerGroupId), fieldNum(CustTable, AccountNum));
    qbds.addRange(fieldNum(InventTable, ItemBuyerGroupId)).value('dummyValue');
    queryRun = new QueryRun(query);
    queryRun.query(query);
    while (queryRun.next())
    {
        info('in loop');
    }

    Thanks :)

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,235 Most Valuable Professional on at
    RE: Add link to existing query

    I've rewritten your query to this:

    Query query = new Query(queryStr(CustTableCube));
QueryBuildDataSource qbds = query.dataSourceNo(1).addDataSource(tableNum(CustTable));
    
qbds.relations(true);
qbds.fetchMode(QueryFetchMode::One2One);
    
qbds.addRange(fieldNum(CustTable, AccountNum)).value(queryValue('dummyValue'));

    And this is your query string:

    SELECT * FROM CustTable(CustTable_1)

OUTER JOIN Name, RecId FROM DirPartyTable(DirPartyTable_1)
ON CustTable.Party = DirPartyTable.RecId

OUTER JOIN City, County, State, ZipCode, CountryRegionId, LocationName, Party, IsPrimary
FROM DirPartyPostalAddressView(DirPartyPostalAddressView_1)
ON DirPartyTable.RecId = DirPartyPostalAddressView.Party
AND ((IsPrimary = 1))
AND ((ValidTo>='2006-12-01T00:00:00' AND ValidTo<='2006-12-01T23:59:59'))

JOIN * FROM CustTable(CustTable_1_1)
ON CustTable.AccountNum = CustTable.AccountNum
AND ((AccountNum = N'dummyValue'))

    Isn't it what you wanted? (Although I don't understand why you added CustTable again.)

  • dynamics developer Profile Picture
    dynamics developer 1,272 on at
    RE: Add link to existing query

    try

    qbds.clearLinks();
    qbds.relations(true);
    qbds.joinMode(JoinMode::InnerJoin);

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Add link to existing query

    Here is the full code again (a compiled code):

        query query;
    queryRun queryRun;
    QueryBuildDataSource qbds;
    query = new query('CustTableCube');
    qbds = query.dataSourceNo(1).addDataSource(tableNum(CustTable));
    
    qbds.relations(false);
    qbds.clearLinks();
    qbds.joinMode(JoinMode::InnerJoin);
    qbds.addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTable, AccountNum), 'CustTable_1');
    qbds.addRange(fieldNum(CustTable, AccountNum)).value('dummyValue');
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        info('in loop');
    }


    Even I put 'dummyValue' which is not exist value, I reach the loop.

    Even I change the new table to a random table, i.e InventTable (linked with field: ItemBuyerGroupId) - I reach the loop with info('in loop') line.

    What is wrong?

    Thanks :)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Add link to existing query

    Just need to add a new datasource that is linked to CustTable in CustTableCube (in runtime).

    Didn't succeed doing that properly, for some reason.

    Thanks :)

  • venkatesh vadlamani Profile Picture
    venkatesh vadlamani 3,180 on at
    RE: Add link to existing query

    Outer join,

    But even if it has datasources in outer join custTableCube will be one data source for query . Can you post few more details .

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Add link to existing query

    I would recommend you two step process here

    //Step#1 Indicate you don't want to use relations automatically
    datasource.relations(false);

//Step#2 Add link between parent field and child field
datasource.addLink(fieldNum(SalesTable, SalesId),fieldNum(SalesLine, SalesId));

    Both of steps should be done, as it will ignore AX Table level relationships and will entertain relationships of step2

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Add link to existing query

    I think that the problem is in CustTableCube, which has outer join on its datasources.

    That's why X++ ignore the link.

    If I create a new query with just CustTable on it - it works fine.

    Still, I want to use CustTableCube (or other queries with outer join, which I presume to cause the problem).

    How can I do that?

    Thanks :)

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 Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans