Primary key, Clustured Index, Replacement key, Surrogate Key in Axapta

This post is a quick walk-through of surrogate keys, replacement keys, and field preview parts, which are new concepts in Ax2012.

Overview

The requirement used in this example is as follows:

• Have a new field on the sales order header called 'Priority'.
• This should point to a user-configurable table, containing a priority code and description.

Before we start there are a couple of terms you need to understand:

• 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.

These aren't new concepts - Wikipedia has tonnes of information on general database theory. One of the main changes in Ax2012 is that it more heavily promotes the use of the surrogate keys when relating tables. This is something that was always used in Ax, but more often when we had general-purpose tables, such as document-handling entries/misc. charge lines, that pointed to different types of records. Now you'll find that in a lot of places when two tables are related it's by the RecID instead of the natural key.

Ax also has the concept of the Replacement key. This is used to indicate which fields are displayed on the UI, regardless of the table's primary key. 

Creating the objects

First we create the basic datatype and table structure:

• Create an extended data type (string) called SalesPriorityID. This will be the priority 'code' displayed on forms.
• Create table SalesPriority.
• Add types SalesPriorityID and Description to the table.
• Create index SalesPriorityIdx containing field SalesPriorityID. Set AllowDuplicates to No, and AlternareKey to Yes. Note than an index cannot be designated as a table's alternate key unless it's unique.
• In the table properties, you'll notice that the PrimaryIndex and ClusterIndex have already been set to SurrogateKey. You'll also notice that the CreateRecIdIndex table is set to Yes and locked for editing. 
• Set the ReplacementKey to SalesPriorityIdx. This indicates to Ax that even though our primary key is SurrogateKey (automatic index on RecID), we want the priority code displayed on forms. 

Now we have a basic table with a primary key (on RecID), and unique replacement key (on SalesPriorityID). To reference this table using the RecID, we need a new extended data type:

•  Create an extended data type (Int64) called SalesPriorityRefRecID. Make sure this extends RefRecID.
• Set the ReferenceTable property to SalesPriority.
• Under the Table References node, set it so that SalesPriorityRefRecID = = SalesPriority.RecID.

Don't forget to extend RefRecID. It looks like if you forget to do this, the replacement key functionality doesn't work correctly, even if the data-type is Int64. 

So we now have a table, and an extended data type that references it, via the RecID. All we have to do is drag this field onto SalesTable. You'll notice that when we do this, Ax prompts you to automatically create a relationship based on the EDT reference. Click yes. Rename the field toSalesPriority.

The field can now be dragged from the form data-source onto the design as normal. You'll see that instead of adding an Int64 control, it adds a reference, since Ax has determined the relations automatically. When a reference group is shown on the form, it will display the value of the alternate/replacement key instead of the underlying RecID pointing to the SalesPriority table. I added the field to the 'Status' group, shown below:

In the underlying SalesTable record, the field SalesPriority points to SalesPriority.RecID, but displays the value of SalesPriorityID since it's contained in the nominated Replacement key.

Adding a preview part

I won't go into too much detail here, but the idea is to add a preview to the field, which acts as a sort of extended tool-tip:

The basic steps are:

• Create form SalesPriorityPreview. Add table SalesPriority as a datasource, setting allow edit/add/delete to false. Add fields directly onto the design.
• Create FormPart of the same name and set the Form property.
• Create a display menu-item of the same name, pointing to the FormPart.
• On the SalesPriority table, set property PreviewPartRef to the menu item.

Now when you hover the priority value on the sales order form, you'll see your preview form popup automatically.

There's plenty of information on this - Another good post on the subject can be read here, and of course the MSDN.

Source - AXInternals Blog