Duplicate detection using alternate keys

Duplicate detection has been around since the early versions of Dynamics CRM. In 2015, alternate keys were introduced to help identify a record based on a unique combination of attributes to perform an upsert request. An upsert is the action of inserting a record if it is new, or updating it if it already exists, based on the primary key identifier (https://msdn.microsoft.com/en-us/library/dn932135.aspx). Logically, this means whenever an alternate key is defined, any new records that are created with the same key combination will throw a duplicate exception.

This recipe will walk us though defining an alternate key for a contact, and testing the duplicate detection.

Getting ready

Similar to the previous recipes, a System Customizer or higher security role is required to perform the configuration as well as a solution to contain the changes.

How to do it

1. Navigate to Settings | Solutions | Packt.
2. Create a new field by clicking on Entity | Contact | Keys | New.
3. In the Key definition dialogue, type Name and Email in the Display Name field.
4. Double-click on the First Name, Last Name, and Email Address fields under Available Attributes:

5. Click on Save and Close.

6. Navigate to contacts and try creating two contacts with the same first name, last name, and e-mail address. You will be prompted with the Duplicate Record error, as shown in the following screenshot:

How it works

In step 2 to step 5, we created an alternate key using First Name, Last Name, and Email on an individual. In step 6, we tested the duplicate detection by creating two records with the same key combination.

Alternate keys have a similar behaviour to conventional duplicate detection, except the check happens at a lower level in the database (a unique nonclustered index). Additionally, if a duplicate is detected, primary keys will strictly stop a duplicate from being created, whereas, conventional duplicate detection functionality gives you the option to create it nonetheless. This is particularly important if you want to stop duplicates when using different channels than the frontend forms.

Behind the scenes, Dynamics CRM is creating a nonclustered unique index using the three fields defined in the key. If you have an on-premise deployment, you can run a SQL profiler to intercept commands that are executed on the database. A query similar to the following one will appear in your list:

CREATE UNIQUE INDEX [ndx_for_entitykey_packt_NameandEmail]
ON [ContactBase]
([EMailAddress1] ASC, [FirstName] ASC, [LastName] ASC) 
INCLUDE ([ContactId])
WHERE [EMailAddress1] is not null
AND [FirstName] is not null
AND [LastName] is not null
WITH (FILLFACTOR = 80, MAXDOP = 4, SORT_IN_TEMPDB = ON)

The preceding query creates a unique nonclustered index on the ContactBase (the contact table) on the three columns: FirstName, LastName, and EmailAddress1. For more information on nonclustered indexes, read the following article at https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes

During the duplicate detection process, if a field has an empty value (translated to NULL in the database) in one of the fields, the record will not be identified as a duplicate.

Note that alternate key creation can fail sometimes. Always check, after creating your key, whether the creation has been successful. If a duplicate already exists in your dataset, the key creation will fail. To check the status of a newly created key, in your solution, navigate to Entities | <your entity> | Keys and ensure the Status column states Active. If the creation fails, it will state Failed: