Alternate keys are great way to enforce uniqueness of the column values. For example, if you want lead emails to be truly unique, just add an alternate key and no user will be able to bypass that (unlike the duplicate detection which is a fairly timid mechanism). The challenge is when you want that uniqueness to apply only to active records. Bear with me.

You thought that lead was lost forever and deactivated the record (you do not ever delete important records, right?). Now they are coming back, rejuvenated and refreshed and ready to buy not one but seven of your wonderful Jigamagigs. New lead is created but with the same email and that’s OK – we don’t hold grudges. What you don’t want is two active leads with the identical emails.

Alternate key using email are not going to work for you in this scenario, but you can enforce the uniqueness using a faux column that contains email for active records and null for inactive.

It works because alternate keys ignore null values.

The walkthrough below uses the contact table.

1. Create a column large enough to hold email addresses. No need to make it searchable or enable audit.
   
2. Create new real-time workflow (and you can do that from the new solution explorer)
   Automation > Process > Workflow”>
   
3. Set the workflow properties
   
   • Check As an on-demand process if you have existing records in your Dataverse instance so that you can run it once to populate the values.
   • Start after Record is created, Record status changes, and Record fields change.
   • Select emailaddress1 as the field to trigger.
   • Set the workflow logic: If contact status is Active, set faux column created in Step 1 to emailaddress1 value. Otherwise clear the column.
4. Create alternate key over the faux column
   
5. Run the workflow over all existing contact records to populate. Note: it may fail if your table contains active duplicates. Check for failed workflows, resolve the duplicates, run again.

Now, when a user tries to create a record with a duplicate email address they will see the error.

But users will be able to create the duplicates if only one of the records is active.

The good thing about this method is that it’s bulletproof against clever users importing via Excel online, sneaky developers updating using SDK, and even almighty administrators activating the existing records that contain the duplicates.

This tip wouldn’t be possible without my family, my parents, my sponsors, and David Yack who provided a sanity check where I needed it most.

Cover photo by Rupert Britton on Unsplash