Add link to existing query

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (11)

Answers (2)

Suggested answer

venkatesh vadlamani 3,180 on at

Like (0)

Report

Do you mean to add a new table to query or just add additional link?

If its link then please get instance of child datasource (might be 2 in your case related to MyTable) and use something like below

childDatasourceRef. addLink(fieldNum(CustTable, AccountNum), fieldNum(MyTable, MyAccount))

this should work if you just need the link.

If you need the table be added to query then what you have written seems fine to me.

Regards

Venkatesh

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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 :)

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report
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

Was this reply helpful? Yes No
venkatesh vadlamani 3,180 on at

Like (0)

Report

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 .

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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 :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
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 :)

Was this reply helpful? Yes No
dynamics developer 2 on at

Like (0)

Report

try

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

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,972 Most Valuable Professional on at

Like (0)

Report
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.)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
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 :)

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,972 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No