Replacement Key in AX

Table keys: Replacement key in AX 2012

Hello guys J today am going to walk through the different table keys in AX 2012 R2.

As we know many different tables’ keys exist in AX 2012 such as Surrogate key, Alternate key, Primary key, foreign key etc.

Since many of them are briefly explained on MSDN.

But there are certain table keys which are difficult to understand just by the definition, something like “Replacement key”. So let me then showcase the practical scenario like how you can use it and the benefit of having that property in your table.

What is replacement key??

A replacement key is an alternate key that the system can display on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.

The replacement key is chosen by setting the ReplacementKey property on the table. The drop-down list offers every alternate key as an available value.

 

The drop-down list contains every index that has its AlternateKey property set to Yes.

You might change the default blank value to an index whose field values within each record provide a name or other moniker that is meaningful to people. If a ReplacementKey is chosen, its fields can appear on forms to helpfully identify each record.

The ReplacementKey should be a set of fields that represent the natural key.

 

Now let’s look at it in AX 2012, how we can create this key and how does it look.

Firstly am going to create a simple table called “EmployeeTable” which contains 3 fields emplId, Name and DOB (dateofbirth).

And now going to create index on this table where “AllowDuplicate” property should be set as ‘NO’ and alternate key property as ‘YES’ .As shown below:

 

Once the creation of index is done then we can easily assign the replacement key to this table as shown here in the below image.

 

Now creating another table called “EmployeeTransactions” which contains 2 fields TotalAmountCharged and TotalHourWorked.

In order to give relation with EmployeeTable, creating foreign key relations and as soon as we do that automatically new field (EmployeeTable) got added in our “EmployeeTransactions” table. As shown in below image:

Now, I can say that relation exist between the two tables and relation is RecId field of employeetable to that of newly created employeeTable field (Int64 – datatype).

NOTE: You can also achieve this relations using EDT.

 

Now let’s create some line in to these tables and check how and what it stores in to employeetable field in ‘employeetransaction’ table.

As we have seen from backend, table stores the INT64 field i.e., recId of related ‘employeetble’ record.

 

Since form is the artifact which is user oriented (Client interface). Now let’s create a form and check how it appears there. So am just creating a simple form which consist of these two table as datasource and using the joinsource property to join them. Form structure looks something like this:

 

So basically, header and line part in form represents “employeetable”&” employeetransaction”. Here in header part details can be filled up & emplid can be easily accessed and if you look at the line level there is a field called reference which I dragged from transaction datasource > emloyeetable field.  For reference, just look at the form image above.

Now here comes the magic J as soon as you create & save the record in header then line level reference gets filled up with related emplid field. But if you look at the backend in tables it would be stored as recid.

So I can say that Alternate key allows the system to display significant value on forms instead of a meaningless numeric primary key value. Each table can have a maximum of one replacement key.