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) / Adding Link to datasou...
Microsoft Dynamics AX (Archived)

Adding Link to datasources

(0) ShareShare
ReportReport
Posted on by Community Member

Hi ,

I need to get the following structure:  ItemId(InventTable), BomId(BOMVersion), bomID(BOM), QTY(bom). 

I have created a query and i tested it in the job and it works fine, here is the job

q       = new Query();
    qbds1   = q.addDataSource(tablenum(inventTable));
    qbds1   = qbds1.addDataSource(tablenum(BOMVersion));
    qbds1.relations(true);

    //link bom to bomversion
    qbds2   = qbds1.addDataSource(tablenum(BOM));
    qbds2.relations(false);
    qbds2.addLink(fieldNum(BOM,BOMId),fieldNum(BOMVersion,BOMId));


I tried to apply the  same logic on my form based on inventtable_ds (grid1),bomversion_ds(grid2),  bom_ds, (grid3), 
to show those values in 3 separate grids. In BOMversion_ds execute query  I added the following code refered to this post https://community.dynamics.com/ax/b/daxmusings/archive/2011/10/13/query-object-datasource-linking 
but i didn't get the result only inventtable and bomversion are linked together.

BOM_ds.query().datasourceTable(tableNum(BOM)).relations(false);
BOM_ds.query().datasourceTable(tableNum(BOM)).addDataSource(tableNum(BOMVersion)).addLink(fieldNum(BOM,BOMId),fieldNum(BOMVersion,BOMId));

Any suggestion please? thanks.

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,831 Most Valuable Professional on at

    First of all, review how the datasource is joined to its parent. If it uses a link instead of a join, your approach won't work, because you'll get two separate queries.

    If you have a single query but it doesn't work as expected, you should look at SQL code generated from your query class.

    For example, this is what your job generates (obtained by calling q.dataSourceNo(1).toString()):

    SELECT * FROM InventTable(InventTable_1)
JOIN * FROM BOMVersion(BOMVersion_1)
ON InventTable.ItemId = BOMVersion.ItemId
JOIN * FROM BOM(BOM_1) ON BOMVersion.BOMId = BOM.BOMId

    Do the same in the form and compare your actual query with the query you intended to build. Then you'll know what to fix.

    You should the change the query just once (in init()); you current code would keep adding the link again and again every time when the query gets executed.

    Also note that you call addLink() incorrectly - the first parameter should be the parent field ID.

  • Community Member Profile Picture
    Community Member on at

    Hi Martin Dràb, Thank you for explanation, 

    In my form i need to have the following join to scroll in 3 grids: item(grid1),bomversion(grid2),bom(grid3)

    1-BOMVersion join  InventTable with delayed linkType,

    2-BOM join  BOMVersion with delayed linkType,

    3-i have added the following code in init form in BM_ds based on your explanation:

    BOM_ds.query().datasourceTable(tableNum(BOM)).relations(false);
BOM_ds.query().datasourceTable(tableNum(BOM)).addDataSource(tableNum(BOMVersion)).addLink(fieldNum(BOMVersion,BOMId),fieldNum(BOM,BOMId));
info(BOM_ds.query().dataSourceNo(1).toString());

    SELECT FIRSTFAST * FROM BOM(BOM) JOIN * FROM BOMVersion(BOMVersion_1) ON BOM.BOMId = BOMVersion.BOMId

    So the link is well added but in grid i didn't get  boms wich are related to the selected bomVersion, i get all of them.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,831 Most Valuable Professional on at

    Delayed link isn't a join. You can have either a join or a delayed link; the choice is exclusive.

    If you connect three form datasources by delayed links, you get three separate queries, not a single query with three query datasources.

    If your goal is to have joins, replace delayed links with actual joins, most likely inner joins.

  • Community Member Profile Picture
    Community Member on at

    I need  to select values from grid1 (Itemid),then grid2(bomversion) and grid3(bom) will be updated if a related value exist, else i will get empty grids. By using inner join; i will get only related fields wich is not my case.

    Grid1(item) ,Grid2(bomversion) : it works fine because we have a link between InventTable and BomVersion table.  

    Grid3(Bomversion) and Grid3(Bom):  there is no link between Bomversion and Bom Table, so i tried to add link as i mentionned. if i will link Bom table with inventTable directly i will ger bom related to items not to bomversion.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,831 Most Valuable Professional on at

    All right, so you don't want a join after all (despite of saying "In my form i need to have the following join"). Fair enough.

    Unfortunately addLink() is used to define join conditions, therefore is useless in your case. It also means that you job does something completely different that what you want to achieve in the form.  You're using joins there, which you just ruled out for the form.

    To achieve such a link between separate queries, you have to add a dynamic link (addDynalink()) instead of a normal link.

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
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans