AX 2012 - Understanding the changes to Table Relations

Well I hope everyone is having a fine week so far. Oh Wednesdays, the furthermost point between two weekends. It's a day of great productivity, as we push towards that glimpse, and hope for our Saturday and Sunday.

To help you out during the middle of this week, I thought it was time we spent a little focus around the changes in AX 2012 around extended data types, and specifically around understanding the way relations are made.

First off the bat, lets look to the Extended Data Types in the Database [AX 2012] In this article on MSDN, Microsoft points out some highlights about EDT's in AX 2012, specifically, highlighted is:


"Starting in Microsoft Dynamics AX 2012, you can no longer define relations under an EDT element in the AOT. If your system has any EDT relations, you should migrate those relations to the appropriate table elements. For more information, see EDT Relation Migration Tool."

On this same page, before we move forward in looking at what this means, there is a link on How to: add Dimensions. Make sure to NOT follow that link. It has not been updated yet for AX 2012.

Now with that aside, lets look at what this means for AX 2012. Back in AX 2009, relations would exist, on the EDT, for example, linking a specific EDT, to a reference table, in which the EDT was the primary key.

Now in AX 2012, this has moved to the table relations level. So lets take the following for example. If I have a Table, and as part of it, I would like to have an EDT, that is controlled and is referenced to an underlying table itself. This EDT then, and the field that it represents would be a foreign key for this new table we are building.

To help set this straight, lets look at the following.:


With this, we have the relationship, that now lives at the Primary Table, that has a Foreign Key, represented by the EDT. The EDT, in turn is the Primary Key, for the underlying reference table.

Moving ahead, lets see this in action, with a simple example. To see this in action, we will need a project, an Extended Datatype, or EDT, as well as our reference and Primary table.



You will notice that I have already added the EDT, as the PrimaryField to the idbReference Table node. Also, I've created a Primary Index, and placed that field in the Primary Index, as set the index to not allow duplicates.

Now that we have this base, we must move to the EDT, and add the idbReference, as a table reference to the EDT. This does not set the relation, instead it is the primary reference for the EDT.





After this, we add the EDT, to the Primary table, which I've called ForeignKeyField, in order to help understand the point.



Now that we have this, we next have to create our relation, from the Primary table to the reference table. We do this, and on the properties of the relation, we set that it's an EDTRelation = Yes.



In doing this, we can now set the relation, via a Normal Relation Option, from our Primary Table, to our Reference Table field. In doing this, and because of the EDTRelation = Yes is true, we can now select what EDT should be used in this relation.



After this we end up, with our goal, of having correctly related reference data, from one table, as part of the record for another table.



Moving the relations to the table level makes correct sense. It's a change in how you've worked with AX in the past for this area, but it's a very good change. For example because of this table relations now support Dynamic Links, as well as unbound control lookups. Implementing the above, means also that when it comes time for form design, your reference field, from your Primary datasource, will enable correct lookup of data, from the reference table.

This is important to understand, for those people who are doing new custom development work, and also for those who are focused on upgrades. There is Whitepaper, for those looking to how best and tackle this for upgrades. You can find that white paper here: Migrating EDT Relations

Well I hope that on this fine Wednesday, you have a productive one, and hope that the above might come in handy, or better help your understanding of this topic, as it relates to new customization in AX 2012 as well as how this can impact and affect task when upgrading.

That's all for now, till next time!

Update: Fellow Blogging Peer Joris, over @ DAXMusings pointed a two other facts out, via twitter today for this post. First, "When you use an EDT with a reference table, it asks if you want to copy the relation onto the table where you're referencing" and Second, "[A]lso, the reference is important on EDT still for "view details" (goto main table) on dialogs." I would like to thank Joris for taking the time to add to this post, in a very positive way. These are good points that need to be understood, with the context of this post.



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.


"Visit the Dynamics AX Community Page today!"