Table Relationships - Learning with an Example

In AX 2012, Table relationships have gone through many changes with many new kind of keys coming into the picture. I had a previous blog entry here.

Today let's see Foreign Keys, Replacement Keys in action.

We will find out that it is possible now to define parent/child relationship without having to define the same primary key in both the tables. For eg, till AX 2009, a header/lines relationship used to exist on the basis of a unique key like SalesId field in both SalesTable and SalesLine. This is no more required in AX 2012. Such relations are defined on RecIds and  values are displayed on forms depending on what is defined on your ReplacementKey (thats why the name Replacement!)

Lets go one by one.

1. Table HeaderTable

2. HeaderTable has two fields AccountId and Name

3. Two indexes AccountIdx and NameIdx. Both have their AllowDuplicates property set as No and AlternateKey property set as Yes.

4. Header table's ReplacementKey property set as NameIdx.

5. Second table LineTable will have two fields LineName and AccountId.

6. Create an Int64 type EDT HeaderTableRefRecId with property ReferenceTable as HeaderTable and Extends as RefRecId (this is important!)

7. Drop this EDT on LineTable.

8. Click Yes on the prompt.

9. New field, new index and new relations are created. Relation is between HeaderTable's RecId and the new EDT field HeaderTableRefRecId.

10. Create a Form with these two tables as datasources. Drop the fields in HeaderView and LineView nodes. 
(Important Note: when you drop the HeaderTableRefRecId field on the Line node, it uses a special ReferenceGroup control, which stores a int64 recId field of the Header table but effectively displays Name. You cannot use a normal int64 control.)

11. Create some dummy records within the table browser. Set the field HeaderTableRefRecId's value from the dropdown. Notice the values displayed in the dropdown. One is the header's RecId and second is the Name, the ReplacementKey.

12. Checkout the values on the form. Field HeaderTableRefRecId's value on UI will not be some random RecId but the Name of the header record. This happens because of the ReplacementKey.