Auto Numbering existing records in Microsoft Dynamics 365 / CRM

As the creator of the Auto Number Manager tool in XrmToolBox, I get two questions more than any other questions:

How can I number existing records?

How can I add dynamic content to the numbers?

To answer these questions once and for all, I will post my suggestions in two articles. I will step way outside my comfort zone of coding the solutions and give you strictly no-code alternatives.
Well, I do feel reasonably comfortable, since I wrote the code in the tools we need… ?

In this article I will focus on the first question.

Consider the scenario that we have an existing system with lots of existing information, and we want to start numbering our Rockets.
Creating the numbering format for the attribute to number is simple, using the Auto Number Manager.
But according to the documentation, numbers are only assigned to new records, just before they are being created in the CDS database.

In summary my trick is to create a temporary entity with a lookup to the Rocket entity, add auto numbering to an attribute on the temporary entity, run a workflow on all accounts that are missing the number, and then remove the temporary entity.
The workflow will create a record on the temporary entity and set the parent Rocket. Then it will copy the value in the numbered attribute from the temporary record to the parent rocket.
Tada!

So, let’s look at the details of that procedure.

Create numbering format for Rocket

I have my Rockets, but need to add auto numbering for a new Rocket Number attribute.

This is done by using the Auto Number Manager in XrmToolBox.
I select the solution I am working in, and the Rocket entity, and then click New Attribute.

Enter logical name and display name for the new attribute.
I use a quite simple numbering format in this case, “CR” of course an acronym for Cool Rockets, and then a five-digit sequential number.

Note that I also set the Seed value to 100. This to make sure that there will still be room for lower rocket numbers for the existing rockets, even if I create new rockets.

Hit Create, and your new auto numbered attribute will be created!

Due to an issue with metadata being cached, the new attribute might not appear in Auto Number Manager immediately. Try restarting the tool, and it should hopefully show up.

You can now add the new attribute to views and forms from the Maker Portal or the old Solution Explorer.
Create a new Rocket, just to test the numbering.

As you can see above, of course the old rockets I have do not have any number.

Create temporary entity

I create my new temporary entity as simple as possible – organization owned, no activities or anything like that.

Save the entity and then add a lookup relationship to the Rocket entity.

I make sure to set the primary field of the entity to Not required, since this will be automatically populated by my numbering format.

Create numbering for temporary entity

Now start Auto Number Manager again, selecting the new entity.
I create the same numbering format as I did on Rocket, but now on the primary attribute of the temporary entity and set the Seed to 1.

Create workflow

Now we need a workflow to create these temporary child records from existing rockets.

I could of course have done this with Microsoft Flow, but for the good old times I will stick with a classic workflow in this case.

I define the workflow to trigger on change of the Rocket Number attribute. This may seem strange, as this is what we want to get a number for, but it will have some advantages too as you will see later in this article.
To prevent recursion and unintended overwriting the numbers, first check that the Rocket Number attribute is empty.

Then create a record of the temporary entity and set the lookup to the Rocket that triggered the workflow.

Finally copy the numbered value (Primary Name) from the temporary record to the Rocket Number attribute on the Rocket.

Bulk updating unnumbered Rockets

After activating the workflow, it is time to start the Bulk Data Updater tool in XrmToolBox.

I won’t go into the details of this tool here, but you can watch a short 6 minute video tutorial on BDU here: http://jonasr.app/xtb-school/bdu

I use a very simple query in this case, simply returning name and number of all rockets. The query may of course be filtered and adjusted for your needs.

Since I retrieve all rockets, I select the ones missing Rocket Number, and chose the option to only update selected records.

I select attribute Rocket Number, and set its value to null. This will trigger the workflow, and make sure new number is assigned.

Hit Update records, wait for the workflows to execute, and then check the results.

The existing rockets now have new numbers from the number series assigned to the temporary entity!

Side effects

• As I created the workflow to trigger on the Number attribute, I should be able to “reassign” numbers for existing records by simply deleting the value from single records. This could be good during this migration phase to align all numbers.
• Assuming there are no external dependencies to existing numbers, I can also run the Bulk Data Updater on all records, and they will then be reassigned.
• The workflow I created was defined to run asynchronously, which means I cannot really be sure in which order they are actually executed. To ensure this, change the workflow to run synchronously, and then it is possible to define the order by adding order clauses to the query retrieving the records in Bulk Data Updater.

When you are done numbering existing records, remember to deactivate the workflow and delete the temporary entity.

Continue to the next article, where I show how to include dynamic data in your auto numbers!