Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to define a table ...
Microsoft Dynamics AX (Archived)
Suggested answer

How to define a table relation correctly in AX 2012 between tables (saved per company and not saved per company) including “related field fixed”?

(0) ShareShare
ReportReport
Posted on by Oleg Abapolov 2

Hi,

I would like to know how should a table relation between table saved per company and table not saved per company be defined correctly in AX 2012. Please find below 2 cases I made.

Case 1. Use dataAreaId in table relation

1.1 Create a new table TestSystemTable (SaveDataPerCompany = No).
1.2 Add 2 foreign keys from DlvMode (SaveDataPerCompany = Yes) and Hierarchy (SaveDatePerCompany = Yes) tables, plus add dataAreaId fields (use AgreementHeaderDefault table as an example in standard AX)
1.3 Add table relations (as I want to filter records of Hierarchy table I define a relation with “related field fixed”, actually like it is in CaseDetailBase table) :
8168.TestSystemTable2_5F00_Relation.png

1.4 Create a new record in table browser, open HierarchyId field lookup –> no record, however a record exists in Hierarchy table (for example in CEU company):

6254.TestSystemTable2_5F00_Lookups.png

1.5 Create a record in Hierarchy table in DAT company, repeat step 1.4 -> the record is presented in lookup.

So for some reason the relation between TestSystemTable and Hierarchy tables is built based on DAT dataAreaId, but not the current company or dataAreaId field specified in the table relation. Why?

 

Case2. Do not use dataAreaId in table relation

2.1 Duplicate TestSystemTable and name it TestSystemTable2

2.2 Remove dataAreaId from Hierarchy table relation:

2727.EDT_5F00_Relations.png

2.3 Lookup works correctly:

However from the logical point of view the relation is wrong as dataAreaId is not used.

 

It can be somehow connected to relations on EDT:

I’m out of ideas, but I think it is quite important to understand how table relations must be defined.
Thank you for feedback.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to define a table relation correctly in AX 2012 between tables (saved per company and not saved per company) including “related field fixed”?

    Just for info, I had the same problem as described in this question on an AX2012 FP Version (6.0.1108.670).

    Seems that this issue has been addressed with R2 as the same scenario on R2 works as expected. The version I used for test was AX2012 R2 RTM with Kernel 6.2.1000.4051 .

    Does someone know if for example a kernel upgrade to AX2012 CU5 will solve the issue?

  • Joris dG Profile Picture
    Joris dG 17,775 on at
    Re: How to define a table relation correctly in AX 2012 between tables (saved per company and not saved per company) including “related field fixed”?

    I would do a SQL trace to see what query that lookup in your first case is using, it should give you a clue as to what's happening and whether or not you can resolve it.

    Also, I checked in the AOT, the DocuRef table uses this exact pattern. The DocuRef table is not save data per company, and links to other tables. Of course nobody uses lookups on that table.

    I'm not aware of any special properties around cross-company relations.

  • Oleg Abapolov Profile Picture
    Oleg Abapolov 2 on at
    Re: How to define a table relation correctly in AX 2012 between tables (saved per company and not saved per company) including “related field fixed”?

    Thank you for response.

    In case 1 the current company is CEU, the value in TestSystemTable.HierarchyDataAreaId is CEU.

    So based on your explanation the table relation adds a range on dataAreaId (HierarchyDataAreaId field's value is CEU) and AX adds another range for the current company - CEU. So records of Hierarchy table should be filtered by dataAreaId = CEU. Do I understand you correctly? 

    However only records of DAT company are presented in the auto-lookup.

    In case 2 the current company is CEU. The table relation does not add the range, but AX adds the range for the current company - CEU. The records of CEU company are presented in the auto-lookup. OK.

    It still looks strange to me.

    My goal is to find out the correct (official) way to define relation in such case, probably I missed to set up a property to resolve this problem.

  • Suggested answer
    Joris dG Profile Picture
    Joris dG 17,775 on at
    Re: How to define a table relation correctly in AX 2012 between tables (saved per company and not saved per company) including “related field fixed”?

    Remember the dataareaid is an implicit selection that AX adds in queries based on the company you are currently in. You have to explicitly tell AX to query across dataareaids if you want that.

    Since you have no control over the query performed by the auto-lookup, the relation will put a filter on the dataareaid, but AX will automatically add another datareaid filter for the current company. If your lookup were the opposite way (from a company specific to a non-company specific, filtering on the hierarchydataareaid field) this would work just fine.

    The only way to resolve this is create your own lookup form, where you change the lookup form's query to crosscompany, at which point your relation-filter on datareaid should work.

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,354 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans