web
You’re offline. This is a read only version of the page.
close
Skip to main content

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) / 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 39

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

I have the same question (0)
  • Suggested answer
    Joris dG Profile Picture
    Joris dG 17,775 on at

    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.

  • Oleg Abapolov Profile Picture
    Oleg Abapolov 39 on at

    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.

  • Joris dG Profile Picture
    Joris dG 17,775 on at

    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.

  • Community Member Profile Picture
    Community Member on at

    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?

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Sagar Suman Profile Picture

Sagar Suman 2 Super User 2026 Season 1

#1
Alexey Lekanov Profile Picture

Alexey Lekanov 2

#1
Pratik Bhosle Profile Picture

Pratik Bhosle 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans