Welcome to my next post aimed at helping people prepare for the MB2-712 exam (Microsoft Dynamics CRM 2016 Customization and Configuration). In this post I’m going to continue my series by looking 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 CRM 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.
So if the primary field isn’t the primary key what is? The answer to this question is a field called GUID. All CRM 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 CRM 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.)
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 a key with CRM. (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 123458 a duplicate error will be shown.
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”.
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 CRM forms etc. But it does exist in background and can be used when integrating in with other systems.
- All CRM 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 CRM database.
- All entries in the CRM 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 CRM fields.
- CRM 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-713 exam.