Working with the AX 2012 EDT Relation Migration Tool

This week I was faced with a couple of problems related to the relationships between tables made under an Extended Data Type (EDT) in AX 2012. In my case, I learned when relationships between tables exist through EDTs, they only capture a single field relationship and not necessarily a “real” relationship between tables.

A good example of this is when we assign an ItemId EDT to a custom field in a custom table. In this case, AX 2012 asks you to create an EDT relationship, so I did. Later in the week, I was getting an error related to a violation of a primary key in the custom table I created when data was being inserted to it.

The error was related to the relationships being defined under an EDT as the kernel was having issues defining which relationship to examine first.

It took me a while to understand that the problem was the EDT relationship and, after looking at some documentation it was easy to catch the issue as EDT relationships do not contain relationship metadata, such as cardinality and relationship type, and more often than not, they cannot be included in the relations node.

Luckily for us, AX 2012 provides a simple way to fix the EDT relations issues. In fact, the migration can be done both manually or by using the new EDT relation migration tool. However, if the data model is not correct, there are cases where we will need to fix the EDTs manually.

To begin using the EDT relation migration tool, open the form for the tool by using the navigation path Tools > Code upgrade > EDT relation migration tool as seen in the picture below.

NOTE: Keep in mind that if you are using this tool for the first time, AX 2012 will ask you if you want to refresh all the EDT relationships data. Click yes and the EDT Migration tool will open.

When the form has opened, follow the next steps:

1. Select a table from the Table name pane.
2. Select each relation in the EDT relations table and choose an action from the Migration action drop-down menu for each of them.
3. After you have set an action on all the relations for that table, click the Migrate single table button on the ribbon at the top of the form.

See Image for clarification:

What happens in the background?

• The migration tool attempts to find a match for the EDT relation in the existing relations in the selected table. If a match is found, the SourceEDT property on the table relation is set to the name of the EDT.
• However, if there is no match found in the table, the EDT migration tool will create a new table relationship only if the index (IndexType ) on the referenced table (shown by “”) is set to the correct table relationship.
• Finally, the tool will not create a new table relation if the matching index for the EDT field on the referenced table is set to NoIndex, Unique, NonUnique.

Example: Migrating an EDT relation to a new table relation.

This example shows the case in which an EDT relation is migrated to a table where the table relation was previously not defined. The result from this example will be the creation of a new table relation.

The EDT PKTableField1 defines a relation to the PKTable.Field1 field, which is an alternate key AK1.

In addition, before the EDT migration, the FKTable.Field1 field uses the EDT PKTableField1, which makes it a foreign key into PKTable. However, there is no table relation defined on the FKTable, and the ExtendedDataType property on FKTable.Field1 is set to PKTableField1 instead.

We choose the FKTablle and under the Migration Action, we choose Migrate.

What happens in the background?

The EDT relation migration tool performs the following actions:

• The EDT migration tool creates the new relation to the PKTable under the Relations node of the FKTable. This relation will be of type Normal because the key is not the primary key.
• Then, the EDT migration tool will set the EDTRelation property of the PKTable relation to Yes. This is because the tool performs the direct migration of an EDT relation to the table relation.
• Then, the EDT migration tool creates one field link, FKTable.Field1 == PKTable.Field1, for the PKTable relation.
• Finally, the EDT migration tool will set the SourceEDT property of the field link toPKTableField1.

What should we expect the next time we use the tables?

• All the APIs that used the EDT relation first on FKTable.Field1 will now find the same relation with the same field link under the PKTable relation by examining its SourceEDTproperty.
• If a table relation that refers to PKTable already exists in FKTable, all the APIs that used those table relations will not pick up the PKTable relation because it is flagged as an EDTRelation, and the PKTable relation to the Relations node of the FKTable, with its EDTRelation property set to Yes.

In addition to the above, we can also double check the outcome of the EDT migration tool by looking at the SourceEDT property of FKTable.Field1. This should have been set to PKTableField1 to maintain a relationship with the EDT.

Let recap for a minute on what we just went over. The EDT relation migration tool can be used to automate the following actions:

• Copy an EDT relation to all hosting tables.
• Automatically set the EDT migration properties (markers) to reflect migration status.
• Automatically populate relation metadata.
• Derive cardinality from the index on the foreign key.
• Derive the relationship type from the delete action/key composition.
• Determine role names.
• Report AOT objects impacted by the migration, depending on the relation used. The objects that can be affected include:

Queries
Forms
Delete actions on tables
Data sets
X++ reports

I really hope you like this article and that can help you at some point in your AX 2012 adventure. Also, I will be writing a bit more about AX 2012 Retail and Inventory and Product management in AX 2012 soon, so don’t miss it!

RSM Staff