AX 365: is it possible to create table relations automatically? Like it was in Ax 2009?

user_3775442 0
Viewed 63

I have a table FooGroupTable with a primary key FooGroupId of EDT type FooGroupId (and the EDT references FooGroupTable.FooGroupId through table reference)

I also have another table FooTable and I want to make a field FooTable.FooGroupId which references FooGroupTable.FooGroupId: any value that user enters on form into FooTable.FooGroupId should exist in FooGroupTable.FooGroupId.

How to do it in the most efficient way? In Ax 2009 I would just choose EDT and call it day: lookup would work, a check that a value entered in FooTable.FooGroupId exists in FooGroupTable table would be done automatically without me jumping through hoops.

In AX 365 automatic EDT relations are no more. When I drag'n'drop EDT into fields to make a new field, there is no dialog asking me if I want to make a relation. I can't drag'n'drop field into relations.

The only way I see is to create table relation manually. Is there a better way?

dynamics-365-operations axapta dynamics-ax-2009
1 Answers

TLDR;

In short: no, there does not seem to be a way to create table relations automatically like in AX 2009 based on the extended data type's metadata information.

As the warning in Extended Data Types in the Database states:

Starting in Microsoft Dynamics AX 2012, you can no longer define relations under an EDT element in the AOT.

Note that the extended data types in AX 2012 and D365FO still show a relations node. This is for backwards compatibility to still allow standard extended data types with relations. But for new extended data types, no relations can be defined.

Why?

So why would Microsoft remove this way of defining relations? Some answers can be found in the Migrating Extended Data Type Relations (White paper). In the introduction, the white paper says:

Table relationships under an EDT have some disadvantages:

  • They do not contain the rich relationship metadata, such as cardinality and relation type, that can be included in relations under a table node.
  • They can only capture single field relationships, which might not represent the intended — and possibly more intricate — relationship between the tables.

A significant difficulty with having table relations defined under both an EDT and a table is that the order of relations matters when table relationships are defined in both locations. In such cases, the kernel will use different algorithms to decide which relationship to examine first, depending on the context.

The "Add relation?" dialog

The question mentions a dialog when dragging and dropping an extended data type into the fields node of a table asking the user if a relation should be created. This dialog still exists, but compared with earlier versions, it is offered for fewer extended data types (e.g. in AX 2012 it is offered for the ItemId extended data type, in D365FO it is not). The conditions under which this dialog is offered or not seem to be a combination of the table reference settings on the extended data type and the primary index of the table that is referenced by the extended data type. However, so far, I was only able to find examples where the primary index is set to the default value "Surrogate key" where the dialog is offered. One such an example is the extended data type OMDepartmentRecId, which offers the following dialog in D365FO: Add relation dialog

More information

Apart from the previous two links, I would also like to mention a blog article by David Kidder: ReferenceTable & Table References on EDT (images are unfortunately missing and I couldn't find a version of it that has them). It describes the various combinations of the table reference settings of an extended data type and what the resulting behavior is. It also mentions the "Add relation?" dialog, but at least for D365FO, the description doesn't match the current behavior any more.

user_27205540
Related