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?

Auto Number Manager for XrmToolBox

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!

23 thoughts on “Auto Numbering existing records in Microsoft Dynamics 365 / CRM

  1. Hi Jonas,

    I use the auto number manager on a regular basis and I was wondering how you would solve the requirement to use two different sequence numbers in one field. e.g. using the sequence number A for Business Unit A and sequence B for BU B.

    Thanks for your efforts butting into the Auto Number Manager!
    Looking forward to reading your thoughts on my problem…

    BR
    Mario

    1. Hi Mario,
      Interesting challenge – I think I see a future article coming! ?
      Just need to test a bit.
      Stay tuned…

      1. Thanks for your quick response. Really appreciated!

        The ideal solution I’ve in mind is to define an attribute for each auto number. Based on the attribute the counting should take place.

        I was also considering a solution like you describe it above. Having an additional entity containing the sequence number. Which could work for two-option sets. But this would mean to create a new entity for each option of an attribute. Not a good solution from my point of view.

        1. Do the sequence numbers need to be contiguous? If not, then use a calculated field or (work)flow in combination with “hidden” sequence number to combine a field on the business unit with the sequence number field

  2. Thanks for your feedback. I have already suggested the workflow option to my customer. But this is not working. Each accounting area needs to have contiguous numbers. e.g. for each Country, we need to start the year with 0001 increase the no. with every invoice.
    AT0001, AT0002, DE0001, AT0003, DE0002, …
    The same requirement with a different use case could be to have different typs of projects (internal, customer, events,…) in conjunction with the business unit. AT-CP-0001, IP-AT-0001, EP-AT-0001, EP-AT-0002, EP-DE-0001…

  3. Just a thought, but would it be simpler to trigger the workflows manually utilizing the Bulk Workflow Executor plugin? Then the workflow could just create the temporary entity (no need for a link back to Rockets even) and then update Rocket with the number generated by the previous create step.

  4. Hi Jonas!
    Great article and great tool! Thank you so much for your hard work!

    I’m trying to create a Lead ID, and Auto Number is perfect for new records. I tried following the steps above for old records, but keep hitting a snag.

    One error I got is the workflow is an infinite loop…

    The other error I get is when trying to use the Bulk Data Updater, I receive and XML exceeded the content buffer error. (XRM toolbox and plugins are up to date).

    Thanks!
    -Nik

  5. Hi Jonas,

    Thank you for sharing. Unfortunately, this would not work for CRM 2016 on premise. Would you have any suggestions without the use of a plug-in?

    1. I’m a bit at loss with the older versions – I guess the auto numbering feature that is missing in 2016?
      In that case I believe you are forced to use a plugin with some intricate locking mechanism to ensure unicity is required.
      I would though recommend the North52 third party solution for this, reach out to https://twitter.com/_north52_ and say hi from me 🙂

      Jonas

  6. Hi Jonas,

    What would be the Flow alternative? The way I see it is:
    – Get a list of all Rocket records with blank number
    – For each record:
    — Create a Temp Rocket record (with same format)
    — Copy its number to main Rocket record
    – Delete Temp Rocket records

    No linking of Rocket to the Temp entity (not needed) and no triggering of the workflow, since Flow will go over all the records.

    Would you agree? Am I missing anything? Thanks again for the post!

  7. Easiest/fastest way is to bulkedit in Excel(online) 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.