Thursday, April 26, 2012
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.
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.
Note: Creating the relation directly without the EDT doesn't work in this case. LineTable gets a new field HeaderTable displaying uneditable text Unretrieved. Anyone had a different experience?
Let me know if someone knows more interesting things around this concept. Cheers.
Ask me for the xpo :)