Skip to main content

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.

Thus with this we are done with the Replacement key functionality in AX 2012 R2. Please input your email-id, if you are looking for XPO of it.

Keep reading for more. Thanks J


 

Comments

  1. Hello.. I need the .xpo for the above. Kindly send it to vivek1989maheshwari@gmail.com...

    .. thanx

    ReplyDelete
  2. please send me the .xpo file for this.. karthikeya.dax@gmail.com

    thanks.

    ReplyDelete
  3. Hello.. I need the .xpo for the above. Kindly send it to babu.dax2012@gmail.com...

    .. thanx

    ReplyDelete
  4. beautifully explained vishal, keep going........

    ReplyDelete
    Replies
    1. Hi Vishal, it really helped me a lot to understand the replacement key concept. Request you to please send me the xpo on my email id: nagesh_singh1978@hotmail.com

      Thanks

      Delete
  5. send me xpo mohanydder@gmail.com

    ReplyDelete
  6. Hi Vishal,
    It's very helpful. when i made the relation of employee table with the employeetransactions using the foreign key>'single field alternate key based' the dropdown value in the employeetable field of the employeetransactions shows the alternate key field values directly instead of the int64 values. Please comment

    ReplyDelete
  7. please send me the xpo file to christalen4u@gmail.com

    ReplyDelete
  8. nice...please send me xpo to amolpatil187@gmail.com

    ReplyDelete
  9. very nice please share me the XPO to adityaraja63@gmail.com

    ReplyDelete
  10. It's very helpful.please send me the xpo file to kmayazahmed@gmail.com

    ReplyDelete
  11. superb .... ple send XPO file to kothamasu.pavan91@gmail.com

    ReplyDelete
  12. pls send me XOP file pramod6960@gmail.com

    ReplyDelete
  13. Hi Vishal,,
    Its very good explanation.. My point on replacement key is cleared now..

    Thank yoou.

    ReplyDelete
  14. i want all the relations and execution steps ,why we use them

    ReplyDelete
  15. very good explanation... you cleared my doubts regarding surrogate key , replacement Key, forignkey relation, alternate key in single post..

    Thanks.

    ReplyDelete
  16. Very good explanation with proper example. Thank you

    ReplyDelete
  17. Thank you so much. Really it was very helpful.

    ReplyDelete
  18. Hi,

    It is really helpful... You explained in a very detailed way.. Thanks a lot...

    ReplyDelete
  19. Very good explanation with proper example. Thank you

    ReplyDelete
  20. its very easy for understanding .. very good explanation sir .. thank you

    ReplyDelete
  21. sir can you give detail information about ssrs report classes .. how we can fill data in tmp table . please reply

    ReplyDelete

Post a Comment