NEIL PARKHURST

As I prepare for my MB2-716 exam I’m producing a series of blog posts that collectively should help others revising for the MB2-716 Certification. (Microsoft Dynamics 365 customization and Configuration.) This time I will look at alternate keys.

In this next section I will describe alternate keys and how they might be used. Before we consider alternate keys we need to understand the existing primary key on the Dynamics 365 entity.

Primary Field (Not a key)

You may have noticed that when you create a CRM entity a primary field is defined. All CRM entities have a primary field and it is often called “name”, although that is just the default setting and can be changed. The important thing to remember here is that the primary field is not a key field! It is simply a field that is mandatory on all records.

It is quite common to hold an account number or policy number in this field. But that still doesn’t make it a key field! As to be a key field implies uniqueness across the database. It is perfectly reasonable to have two accounts with the same name.

Primary Key

So if the primary field isn’t the primary key what is? The answer to this question is a field called GUID. All Dynamics 365 entities have a special field that is “hidden” on forms that is the id field for that entity. This id field holds the GUID value for the record.

Note:
GUID stands for Globally Unique Identified. It is a value held in the database for all records in CRM that uniquely identifies the record. The GUID unique not just within the entity but across the entire database.

As a user you don’t routinely see the GUID on a Dynamics 365 entity but as a developer its presence is very important.

One place you can see it is from an Excel export. Whenever we export data in a static worksheet there are some hidden columns. The first of which contains the GUID. Unhide column A and you can see the GUID. (In the case of an Excel export this is present to support being able to update the correct records should this data be altered and re-imported.)

You can also commonly see the GUIDs in the URLs used to access forms in Dynamics 365. Below I have shown the url used to open one of my account records. Notice that the GUID is highlighted in red.

Alternate Keys

Often just having the hidden primary key of the GUID will be enough and no further keys are required. Although if you wish to have a user readable key field or need to integrate into other systems using a predefined unique key the 36 character GUID is not a very “friendly” field. In these circumstances we might want to define one or more alternate keys. And those keys might comprise of one or more fields from the entity.

Let’s look at an example …..

I have used a custom entity of “Policy” as an example previously and I will stick with that theme in this one. Imagine on my entity I have a policy number and a code that represents the type of policy. The combination of these two fields might provide a unique key to identify the policy.

This might then be used on documentation to the customer or to integrate CRM with other systems. Such as a third party account package.

Below you can see I have added “Policy Number” and “Policy Type Code” to my policy entity.

I have already defined these fields a key with Dynamics 365. (We will look at how in a second.) It is important to understand that the system is going to enforce uniqueness on the combination of these two fields. So if I try to save a record with a type of CAR and policy number of 12345 a duplicate error will be shown.

Create Keys
When we customize the entity there is a section for keys. You can see this on my policy entity shown below. Plus you can see that I have added a key field called “Policy Key”.

Note: You can have up to 5 alternate keys per entity.

Tip:
Notice that the status is initially pending. The status changes to active once Dynamics 365 has been able to create any required unique indexes. This points to another benefit of alternate keys as their presence can improve record lookup times.

When I open the policy key you can see that I have selected “Policy Type Code” and “Policy Number” as the attributes to make up this key. (I maybe could have just selected policy number but I wanted to show that a key can be made up from multiple fields.)

Like the GUID, this key field doesn’t show on Dynamics 365 forms etc. But it does exist in background and can be used when integrating in with other systems.

Summary

• All Dynamics 365 records have a primary field; this is the name for the entity but isn’t the primary key.
• The primary key is a field known as the GUID. (Global Unique Identifier)
• The GUID is unique across the entire Dynamics 365 database.
• All entries in the Dynamics 365 database have a GUID. (Including meta data, so every entity, form, view, dashboard (etc. etc.) has a GUID.)
• Alternate keys can be defined using one or more Dynamics 365 fields.
• You can have maximum of 5 alternate keys per entity.
• Dynamics 365 enforce uniqueness on alternate keys and will give duplicate errors if any are created.

Hopefully this post has given you the details needed to understand keys for the MB2-716 exam.
