Get the Most Out of Alternate Keys in Microsoft Dynamics 365!

When you create records in Microsoft Dynamics 365, a 16 digit unique id, called a GUID, is assigned to the record. If you look at the fields in an entity from a solution, you will see that every entity contains an id field that is a type of Primary Key. If you add new fields, you are not able to add a new field with a Data Type of Primary Key as each entity can only have one primary key. However, alternate keys provide a way around this.

As far as databases go, this is normal – each table contains one primary key that is the unique identifier for records and they can never be the same within the same table. What if you need to migrate or integrate data? Your source system isn’t going to contain the auto-assigned GUID from the Dynamics 365 record. How do you link these systems together and keep them in sync without creating duplicate records and without creating a bunch of unneeded fields?

Easy…Keys. Alternate keys.

How to Create Alternate Keys

The first thing you need to do is analyze your data. Just like a primary key, your alternate key must also be unique. If you use Account Number and you have duplicate Account Numbers, you won’t be able to create your primary key using the Account Number field alone. What’s great about alternate keys in Dynamics 365? You can use a combination of multiple fields to generate a unique key. Just consider what, along with your Account Number, is unique on all Accounts.

Within a solution, under the Entity, there is a component called Keys. You simply need to go to this area and click New. Give your alternate key a name and then select the fields that you would like as your alternate key. When you are done, click OK and a System Job will be created to generate your key.

A Few “Key” Pointers

If you have a lot of data in your system, it may take some time to generate the key as it needs to confirm that there are no duplicates. If there are duplicates you will see a status of Failed for your Key. Simply fix the data and then Reactivate the Key.

Microsoft now allows you to use all attribute types to create an alternate key. This includes Decimal Number, Whole Number, Single Line of Text, Date/Time, Lookup, and Picklist fields.

You can only have 5 alternate keys per entity and the individual keys must conform to SQL-based index constraints. If the key exceeds the constraints, you will receive an error message. You can delete keys as well but use extreme caution. You should make sure that you don’t have any migration or integration jobs dependent on the key prior to removing it.

If you are using patch or upsert actions in your ETL program, use caution when selecting text fields as part of your key. The characters < > * % & : \\ will cause these actions to fail when writing to Dynamics 365.

When deploying solutions between orgs, you can include Keys that are part of your solution. When you import the solution into an org you must allow time for the system job to run and create the key just as you would if you created the key manually.

Maintain Data Integrity

If you’re familiar with Duplicate Detection in Dynamics 365, you know that it has some limitations. The biggest limitation is that you cannot stop a user from adding a duplicate record. You can warn them but all they need to do is simply click ‘Save Record’ to by-pass the warning.

Alternate keys can give you more control over duplicate data, but this can go one of two ways if you are not careful when setting up your alternate keys. Consider setting up an alternate key for Contacts based on Phone Number. You would never be able to enter a 2nd contact with the same phone number. Unlike Duplicate Detection, if you use Keys, your users will receive a hard error and will not be able to continue to save the record. However, if you setup your alternate key to use First Name, Last Name, and Phone Number, it will help you maintain data integrity with fewer duplicates as your users will not be able to enter a 2nd contact record with the same name and phone number.

If you need help understanding or setting up Alternate Keys in your Dynamics 365 system, please contact us today!

Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing, Email Security Implementation and Training, Unified Communication Solutions, and Cybersecurity Risk Assessment.