For business management solutions email us or call 020 3004 4600

How to deliver true value with Alternate Keys in Dynamics 365

Like with most things in the business world, you can only really deliver true value to your business if you have a comprehensive understanding of the Dynamics 365 solution that you are using.

When it comes to creating records in Dynamics 365, a 16 digit unique id, called a GUID is allocated to the record. If you review the fields in an entity from a solution, you will find that every single entity has an id field that is a type of primary key. If you continue to add additional field’s you will not be permitted to add a new field with a data type of primary key as each entity in your CRM can only have one primary key allocated. However, there is a way around this through the use of alternate keys.

When it comes to databases, this is a regular occurrence – each table has one primary key that is the unique identifier for records and there are no instances where they can be the same within the same table. So, what happens if you have to either migrate or integrate data? Your source system won’t have the auto-assigned GUID from the Dynamics 365 record. So just how do you link these systems and keep them in sync without creating countless duplicate records and without ending up with a whole host of unwanted fields?

The answer is simple...Alternate Keys.

In our latest tips and tricks article, we will show you how to create alternate keys, provide you with a few tips as well as demonstrate how you can maintain data integrity within your Dynamics 365 solution.

How to create Alternate Keys

The first step you need to take is to analyse your data. As with a primary key, your alternate key will need to be unique. So, if you use Account Number and you have duplicate Account Numbers, you can’t create your primary key using the Account Number field by itself. What’s so special about using alternate keys in Dynamics 365, we hear you ask? Well, you are able to use a number of fields to generate a unique key. All you have to consider is exactly what along with your Account Number, is unique across all Accounts.

What you need to do within a solution is simply to look under the Entity, you will see that there is a component called ‘Keys’. Go through to this area and then select ‘New’. Pick a suitable name for your alternate key and then choose the fields that you want to use as your alternate key. After you have made your selections, hit ‘OK’ and you will see that a System Job has been created to generate your key.

Some Top Tips

If your CRM system has heaps of data, it could take a while to generate the key as it will need to check through the data to ensure that there are no duplicates. However, if there are duplicates found within your data you will see a status of ‘Failed’ for your key. All you have to do to resolve this is to fix the data and then reactivate the key. 

Some of the field types available to use to create an alternate key include Decimal Number, Whole Number, Single Line of Text, Date/Time, Lookup and Picklist fields. 

It is important to note that you can only use 5 alternate keys per entity and that the individual keys must conform to SQL based index constraints. If the key exceeds the constraints, you will see that an error message pops up. You are able to delete keys, however this has to be used with extreme caution and is certainly not recommended. If you do use it, you will need to make sure that you don’t have any migration or integration jobs that depend on the key being used before you delete it. 

If you happen to be using patch or upsert actions within your ETL program, be careful when picking text fields as part of your key. Certain characters such as < > * & : will cause these actions to fail when writing to Dynamics 365. 

When implementing solutions between organisations, you are able to add keys as part of your solution. However, when you import the solution into an organisation you need to make sure that you allocate enough time for the system job to run and create the key as you would do if you created the key manually. 

Maintain Data Integrity

If you’re familiar with Duplicate Detection in Dynamics 365 (if not, we wrote a blog article on this), you will understand that it has some limitations. The biggest one being that you are not able to stop a user from adding a duplicate record. You can advise them not to do this but they are able to work round this by simply hitting ‘Save Record’.

Alternate keys provide you with greater control over duplicate data, however this can go one of two ways if you don’t take care when setting up alternate keys in the first place. You should consider setting up an alternate key for Contacts which is based on a Phone Number. You couldn’t add a 2nd contact with the same phone number. Unlike with Duplicate Detection in Dynamics 365, if you use keys, your users will see a hard error appear and won’t be able to save the record. However, if you setup your alternate key which makes use of the First Name, Last Name and Phone Number fields, it will help to ensure that you maintain the quality of your data with less duplicates as users won’t be able to enter a 2nd contact record with the same name and phone number!

If you are unsure how to setup Alternate Keys or need more information on how to do so, please get in touch with our team of CRM experts today.

If you are looking to receive the latest CRM tips and tricks & more straight into your inbox then please sign up to our mailing list now!

Intelligence Hub
Related Articles