How to create and implement of surrogate keys in Ax2012

This post is a quick walk-through of how to create & implement of a surrogate key in a table in Ax 2012. Below example is a basic concept of creation and implementation of surrogate key in the table and the form.

Before we start there are few of terms you need to understand first:

·         Natural key. Think of this as the primary key that makes the most sense. eg CustTable.AccountNum and InventTable.ItemID. We can ignore the effect of DataAreaID for non-shared tables for now.

·         Surrogate key. The surrogate key in database terms refers to a field that also uniquely identifies a record, but isn't a natural selector. When looking at Ax, this is the RecID. In other systems this could be a sequential number, or GUID. Typcially, it's something created by the database itself, like an identity column, however in Ax it's managed by the kernel.

·         Primary key. The unique primary key should point to either the natural key, or the surrogate key.

·         Clustered index. This affects the physical layout of records in the database. This doesn't have any real functional impact, but you do need to be very careful when selecting the clustered index as it can have a serious effect on performance if setup incorrectly.

     Process:

     a.     Create two tables i.e. parentTable and child table along with the fields. Take “AccountNum” field is the primary key field in the table.

Figure 1: Parent Table structure

Figure 2: Parent table properties Figure 3: Index properties

b.     Create an EDT with INT64 as datatype and select the “ParentTable” as table in “Refference Table” field under the EDT propery.

Figure 4: EDT

Figure 5: EDT Properties

c.     Drag the EDT into the child table and then system will ask “Do you want to add the ForeignKey relation from the EDT to the current table?” and your selection will be “Yes”

       
d.  After completing above process you will find one index and a relation has been added in the child table.

    e.   Check whether your created surrogate key is created successfully or not

           a.        Enter few data in the Master table and then open child table.

           b.        Try to create new record in the child table

           c.        In the “SurrogateAcctNum” filed you will find a drop down like below. If you don’t find any dropdown like below then your process has not done in a right manner.

 

    f.   Create a form with the help of “ChildTable” and select the drop down of “AccountNum” field from the form control. Your drop down will look like below.

In the form control (under a form) you will see the original value of the record but if you go to the table and there you will find a recId (instead of value) in the field of the corresponding record.

Happy DAXing…