Conditional Unique Auto Number Sequences in Microsoft Dynamics 365 / CRM

A common request for Auto Numbers in Microsoft Dynamics 365 (CDS/CRM) is to have one number field with different formats and unique sequences based on data on the record or it’s parents.
I call this “Conditional Unique Auto Number Sequences”.

In a previous article I demonstrated one way of including dynamic information in a numbered attribute using a calculated field for the number. In this one I will take another approach, and also add the unique sequences.

On my Rocket entity I have an OptionSet defining the Purpose of the rocket.

The customer required (I know what you all think but my customer is not Elon, he made me promise to always deny that) a separate sequence for each Purpose, but they should all be shown in the same field.
As we know, this is not possible out of the box, so we have to bend the laws of nature a bit and come up with a little customization trick to satisfy him the customer.

---

Step by step

Generic Numbering Entity

First create a “generic numbering entity” that can be used for all numbering purposes in the system, where we need to up the game from OOB functionality.

Add fields for all different numbering sequences you need. In this case I have one sequence for Contacts, and four different for my Rockets, where the number sequence and format will depend on the chosen Purpose of the rocket.

The numbering fields can be configured something like below.
Click the image to see full configuration.

Real-time workflow

Now create a real-time workflow triggering on create of the entity to be numbered, Rocket in my case.

Selecting sequence to use

Check the Purpose field of the created rocket, and for each of the available options, create a branch in the workflow. In each branch, add a step to create a new record of the Cool Numbers entity.

Generating the unique number from the unique sequence

Set the name of the new record to indicate the source record (the new rocket) that triggered the creation of the numbering record. Then add a dash (-) to each of the auto number fields that will not be used for this condition. Leave the field for the selected number empty.

Adding the dashes will prevent these fields from being numbered, and thus “preserves” the numbers in their respective sequences.

Setting the number

Now add a step to update the rocket record that triggered the workflow. Set the Rocket Number field to the selected numbering field on the created numbering record.

Repeat this for all possible purposes.

Complete workflow

The complete workflow will now look like this.

---

Result

Creating new rockets in my environment, and they are now numbered according to my new unique sequences based on the selected purpose of each rocket.

---

Method in short

To create conditional unique auto number sequences for one single field, follow these simple steps>

1. On entities to be numbered, create a normal text field to hold the number.
2. Create a “generic numbering entity” (GNE) that can be used for all numbering purposes.
3. For each unique sequence, create a field of type Autonumber and define numbering format.
4. Create a real-time workflow for each entity to be numbered, triggering after create.
5. Let the workflow check conditions defining which number should be used.
6. Let the workflow create a new record of the GNE and set the value of all numbering fields except the one we want to use to “-“. Also set the name of the GNE record to the name of the record triggering the workflow.
7. Let the workflow copy the value of the numbering field used on the GNE record to the numbering field on the triggering record.

---

Numbering existing records

Now that you have implemented this type of smart conditional numbering, you might be wondering what you should do with all existing records?

There is a solution to that as well. Just go back to a previous article in this series – Auto Numbering existing records in Microsoft Dynamics 365 / CRM.

---

What about Mirko?

Well this article didn’t really solve Mirko’s request on Facebook. But stay tuned for next article, I will show you how to use this technique to only assign numbers when specific conditions are met.