RSS

KEY BENEFITS OF REPLACEMENT AND SURROGATE KEYS

24 Sep

KEY BENEFITS OF REPLACEMENT AND SURROGATE KEYS

Microsoft Dynamics® AX 2012 introduced the concept of surrogate keys and replacement keys to make life easier on the back-end database and the users responsible for creating forms. This blog post will define each of those terms as well as define the AutoIdentification field group and reference groups. This blog will also explain how to set up a form using reference groups to refer to a surrogate key field. Last, it will detail a common failure scenario when attempting to create a reference group.

WHAT IS A SURROGATE KEY?

In AX 2012, a surrogate key is the auto-generated primary key for a table in the database. The surrogate key is linked to the already existing RecId field within any table. This means that the surrogate key is a unique 64-bit integer value that is mandatory for the table and cannot be changed or have duplicates. The fact that it is a 64-bit integer (int64) value means table operations normally perform faster than other types of field such as string fields. This is the main strength of surrogate keys.

WHAT IS A REPLACEMENTKEY INDEX?

While a surrogate key is great for lookup and database performance, it is not useful for the end user because it gives no indication of the table’s purpose, or what related tables it is linked to. For this reason AX 2012 has added the ‘Replacement Key’ index property for tables. The replacement key index is a dropdown of alternate keys that have been specified for the table. There can be any number of alternate keys for a table but only a single replacement key. More than one field can be specified under a replacement key, and it is these fields that will be displayed to the end user on a form instead of the surrogate key field.

WHAT IS AN ALTERNATE KEY?

A table can have any number of alternate keys. An alternate key may be a natural key or a single field primary key used in foreign or primary key relations with other tables. In either case, to set one, the user must create a new index and then set AllowDuplicates to “No” and AlternateKey to “Yes”. If AllowDuplicates is not set to “No” then AlternateKey should be greyed out and uneditable.

WHAT IS AUTOIDENTIFICATION?

AutoIdentification is one of the system field groups for a table. If a replacement key is set this table will auto-populate with the field(s) specified in the replacement key index only if the ‘AutoPopulate’ property is set for the AutoIdentification field group. Otherwise, the fields can be dragged manually into the field group.

Figure 1 - AutoPopulate for AutoIdentification field group set to 'No'

 Figure 1 – AutoPopulate for AutoIdentification field group set to ‘No’

Figure 2 - Because AutoPopulate is set to 'No', the ReplacementKey index field does not show up in AutoIdentification unless it is dragged in manually

Figure 2 – Because AutoPopulate is set to ‘No’, the ReplacementKey index field does not show up in AutoIdentification unless it is dragged in manually

By default the reference group for a form will look to the AutoIdentification field to replace the surrogate key value for dragging and dropping from the datasource of the form. Users can set up their own field group and set the reference group to reference this field group instead of the auto-populated AutoIdentification field.

WHAT IS A REFERENCE GROUP?

In the form designer a reference group is a way of displaying the replacement key index fields for a table instead of the surrogate key, which is normally the RecId, for the table.

SETTING UP A FORM

Now that the terms have been defined, let’s set up a form.

Figure 3 - SurrogateKey as PrimaryIndex for first table

Figure 3 – SurrogateKey as PrimaryIndex for first table

From this table and properties window, it is clear that the table’s primary key field is a surrogate key. That surrogate key above is the RecId field which is again, a unique, int64 value field, and is a mandatory system generated field. Below the ClusterIndex field is the ReplacementKey field, which in this case is an index called “LocationIdx”.

Figure 4 - Setting up an AlternateKey which is a ReplacementKey

Figure 4 – Setting up an AlternateKey which is a ReplacementKey

This index shows up as a selectable option in the dropdown because the index is set up as an alternate key. Once the replacement key is selected the AutoIdentification field group should auto-populate with the field(s) specified in the “LocationIdx” index.

Figure 5 - AutoIdentification example with replacement key auto-populated field

Figure 5 – AutoIdentification example with replacement key auto-populated field

There is a user created field group here called ReferenceGroup. This is not to be confused with the reference group control type for forms. The field group was named this way to make it clear that the purpose of this field group is to be used for form reference groups. The idea here is that if the user wants to display other fields which are not part of the replacement key index they cannot use the AutoIdentification field group since it is auto-populated. This issue becomes more apparent when creating a single field alternate key.

The next step is to create a second table which will have a foreign key relation to the first table.

Figure 6 – This is the Second Table with Foreign Key relation to First Table's SurrogateKey

Figure 6 – This is the Second Table with Foreign Key relation to First Table’s SurrogateKey

Here, a second table was created with a foreign key relation to the surrogate key field of the first table. Notice how the table stores an int64 field to refer to the LocationId field. This LocationId field is a created extended data type (EDT) that extends RefRecId, for this example. The purpose of creating this EDT is to more easily establish the foreign key relation when the EDT is dragged into the table from the AOT. The form will not display this long integer value if a reference group is properly set up.

Finally, create a form with the Second Table as a datasource. Drag the important fields into a grid control within the form. The resulting form should look similar to Figure 7.

Figure 7 - ReferenceGroup created on form.

Figure 7 – ReferenceGroup created on form.

Because a replacement key was set on the first table, which populated the AutoIdentification field group, and the second table is linked to the first table by a surrogate key, when the “LocationId” field is dragged into the designer from the form datasource – it will automatically create a ReferenceGroup control which converts the int64 surrogate key value into the easier to understand fields specified in the AutoIdentification field group. If the AutoIdentifcation field group is not populated, the drag and drop of the above surrogate key field will not be converted to a reference field group and instead will be added as the int64 field. All is not lost though as you can still create a reference group control manually and then set the field group you want to display – this can be any table field group, for example the ReferenceGroup field group that was created in the example. Therefore, it is not mandatory to have a ReplacementKey index and AutoIdentification field group set on the table, this is for convenience and allows the int64 surrogate key to be replaced with the ReferenceGroup field group when the control is dragged to the form design from the form data source.

Figure 8 - Manually create a Reference Group

Figure 8 – Manually create a Reference Group

Highlighted in black in Figure 7 is what can happen if the user attempts to drag in the int64 field from the data source and the replacement key is not set up properly. In this example, the replacement key for the first table was blanked out which also blanked out the AutoIdentification field group. Therefore, the surrogate key was selected and not the contents of the AutoIdentification field group. The AutoIdentification group (which is derived from the replacement key index fields) needs to be selected for reference groups to work when dragging in a surrogate key field from a form datasource.

Figure 9 - Proper Setup for Reference Group to Work

Figure 9 – Proper Setup for Reference Group to Work

Microsoft Dynamics® AX 2012 introduced the concept of surrogate keys and replacement keys which can lead to better performing databases, and benefits users who create forms by better clarifying table fields. This allows for a better user experience by avoiding an unclear, long numbers within a form record. From this blog, users gained a better understanding of surrogate keys, replacement keys, alternate keys, auto identification field groups, and reference groups. In addition, we set up two scenarios: (1) creating a form with a reference group that was set up correctly, and (2) creating a form with a reference group that was set up incorrectly. These two examples are beneficial to users as it compares.

While surrogate keys and replacement keys are helpful to users, they are not required for functional tables and forms. If you still want to learn more about table keys, check out this link from the Microsoft Developer Network.

– See more at: http://axmentor.com/learninglab/blog/development/key-benefits-of-replacement-and-surrogate-keys/#sthash.0UZhxIZT.dpuf

Advertisements
 
1 Comment

Posted by on September 24, 2015 in AX 2012

 

Tags:

One response to “KEY BENEFITS OF REPLACEMENT AND SURROGATE KEYS

  1. Johnkrish Collection

    October 14, 2015 at 7:19 am

    Reblogged this on Learn Dynamic Ax with Johnkrish.

    Like

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
All About Dynamics 365

Dynamics 365, D365, Implementor

syedrafayali

This blog contains information about Functional techniques and guidelines in Microsoft Dynamics AX, including tips, tricks, tutorials, tools and upcoming news enhancement in Microsoft Dynamics Ax

Philippsen's Blog

Everyday findings in my world of .net and related stuff

Microsoft Dynamics AX

A great WordPress.com site

Finite Minds

Adventures in IoT

Dynamics Ax

Technical Knowledge

timsaxblog

A blog about implementing Microsoft Dynamics AX and Dynamics 365 for Operations

Microsoft Dynamics 365 Blog

All Things Dynamics 365 - Enterprise Edition, A Blog by Sandeep Chaudhury

DEVSerra - Dynamics AX development blog

Your official Microsoft Dynamics AX blog.

OrganicAX

Discovering Dynamics

AX

A blog by Hai Nguyen

Learn Dynamics Ax with Johnkrish

Live as if you were to die tomorrow. Learn as if you were to live forever - Mahatma Gandhi ****** The more I learn, the less I know - Albert Einstein

Twisted Untwirled

Just another WordPress.com site

ramdynamicsax

Just another WordPress.com site

guyterry's Dynamics AX blog

Just another Dynamics AX blog

Chaitanya kumar

Happy DAXing...!!! :)

%d bloggers like this: