Tip #1414: Enforce unique email but ignore inactive records

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.
    Screenshot of a new column properties. Column is called "I Am Unique" with the length of 250 characters.
  2. Create new real-time workflow (and you can do that from the new solution explorer)
    Screenshot of the expanded NEW menu in the new solution explorer. Submenus are opened as New srcset= Automation > Process > Workflow”>
    New workflow properties. DIsplay name is "Unique Email", table is contact, checkbox "Run workflow in the background" is cleared
  3. Set the workflow properties
    Screenshot of workflow properties as described
    • 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
    Screenshot of an alternate key record that contains only I Am Unique 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.

A screenshot of a sample error message with a caption "Business Process Error" and the message "A record that has the attribute values I Am Unique already exists. The entity key Unique Email requires that this set of attributes contains unique values. Select unique values and try again."

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

List of contacts where two of them have the same email address but one of them is inactive.

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

Leave a Reply

Your email address will not be published. Required fields are marked *