One of the missing features that is often requested for Dynamics CRM is the ability to automatically assign numeric identifiers to records (see the feature suggestion on MS Connect). Sure, we’ve had the Auto-Numering feature for a limited set of default entities (contracts, cases, articles, quotes, orders, invoices, campaigns) since the early days of CRM 3.0, but generating numbers on a standard entity like account or any custom entity has not been possible out of the box.
As if often the case, workflows offer a way for the creative user to overcome such limitations and enhance the CRM functionality with some custom business logic. The usage of workflows to generate automatic numbers for records has been described in several blog posts, of which I’d like to point out the ones written by Karel Iuel and Leon Tribe. The high level concept of this process is as follows:
- Create a custom entity to hold the current counter value of the number
- Link your business entity to this custom counter entity through a N:1 relationship (meaning the single counter record will be the parent for all the intended child business records)
- Trigger a workflow rule from the business entity record creation that performs the following actions:
- Links the new business entity record to the existing counter entity record
- Increments the counter value
- Assigns this value as the number for the child business entity record
With the recent enhancements to workflows in the CRM 2013 release I thought I’d take this scenario and use it to compare the different outcomes with the new real-time workflows and a traditional asynchronous workflow process.
Number assignment with a Real-Time Workflow
Let’s first see how the aforementioned methods work in a CRM 2013 environment. I’ve created a new custom entity called Record Number Counter and added a 1:N relationship to the account entity, with the intention of assigning an automatic number to any new account record that gets added into the CRM database. To actually make this happen, we need to have a workflow process in place that performs the three steps outlined above. Note that the box for “run this workflow in the background (recommended)” is unchecked, meaning this will be a synchronous, real-time workflow process that starts immediately after the specified event, which in this case is “record is created”.
Let’s do some account data importing next to test the results of this workflow. I’ve created an Account Number record of the Record Number Counter entity with its Number value set to 100 500, which means the next account record to get created should be numbered 100 501. With a set of 500 test records to import in my csv file, I’m expecting the counter to hit 101 000 by the time the job is done.
So, off we go with the import job and then we start to anxiously click on the Refresh icon on the account entity view to see which values get assigned in the Account Number column.
Looks like everything is as we wanted it to be! All the 500 account records have been assigned a unique number and the counter value is incremented by one after each assignment. Sure, we’re getting the local number formatting setting from our integer field applied into the target text field on the account (in my case it’s the empty space as the thousands separator), but that’s not a huge issue for our home grown auto-numbering solution. The concept itself appears to be working, which is the main outcome we were after.
Why this won’t work in CRM 2011
If you’ve been reading any discussions on the workflow driven method of auto-numbering, you may have come across issues with the numbers not always being unique. If you create a single record, then wait for the workflow process to complete and check the results before the next record, everything will most likely look ok. But when there’s a bigger volume of database inserts taking place in a bulk operation, the results may not be as expected.
To test this out in a pre-2013 implementation style, let’s convert our real-time workflow into a traditional asynchronous workflow. Now instead of immediate synchronous processing the workflow job will get queued and executed in the background. Remember that these are the only types of workflows that are available in CRM 2011 (or CRM 4.0) where all the real-time actions require a custom plug-in to be developed.
Then let’s re-import the same batch of 500 test accounts. Our Account Number counter record is currently at 101 000, so what we should see is a similar increase of 500 as in our previous test, assuming that all new records get a unique ID. With this new workflow setting, however, the results are not the same anymore:
Well, it does indeed look like we have a problem here. There are several accounts that share the same Account Number value. If we take a look at our Record Number Counter, we can also see that the counter number value only ever reached 101 042 when it should have gone up to 101 500. Plenty of records have therefore been assigned a non-unique number. Based on this quick test we can already conclude that the pre-2013 style workflow processes can quite easily cause issues if applied in this type of a scenario that requires unique values.
Why is the asynchronous workflow then not able to deliver the results we wanted but a synchronous / real-time workflow is able to get a unique number for each record? It’s all due to the fact that the real-time workflow runs inside the database transaction, meaning that all of the steps in it are part of a single transaction (including the record creation in our example). This is what allows the real-time workflows to abort the operation and it also ensures that another competing workflow doesn’t get a chance to mess with the process of retrieving the counter value. The real-time workflows are executed in either pre-operation or post-operation stages of CRM’s Event Execution Pipeline and are therefore guaranteed to be part of the database transaction.
Many of the topics around the CRM platform’s event framework were previously a domain that only developers and architects needed to dive into. The introduction of both synchronous and asynchronous workflow processes in the configuration UI of CRM 2013 means that this is an area where also CRM system customizers should now have a basic understanding. It will certainly help in predicting the outcome from the various workflows that CRM 2013 allows us to build without touching any code.
So, about this auto-numbering requirement…
If you’re not simply playing around with the workflow features for the sake of educating yourself about the Dynamics CRM platform but rather are looking for a way to reliably generate unique numbers for records in the system, then it’s always worth considering whether it would be best to use a ready-made solution developed by a Dynamics CRM ISV. Companies like CRM Innovation, PowerObjects or Gap Consulting offer packaged products that you can use already with CRM 2011 version, offering you plenty of control on the type of ID that is assigned to the records in question. Searching in CodePlex will also give you some results for free solutions that deliver some of the same functionality. If you’re interested in acquiring a more extensive toolkit to expand the process automation capabilities of CRM, then North 52 Business Process Activities (previously known as Formula Manager) also includes an AutoNumber formula that you could leverage to meet this requirement.
Edit 2014-01-11: Fellow CRM MVP, Shan McArtuhr, who’s got way more technical understanding of the CRM platform that I can ever dream of having, has also written a blog post about using real-time workflows for auto-numbering. His post includes a sample solution that leverages two custom entities and real-time workflow processes to automatically generate a numeric ID for any CRM entity. I encourage everyone to read The Power of Synchronous Workflows For Autonumbering from Shan’s blog.
Related posts
Enforcing Business Process Flow Fields with Real-time Workflows
Using Real-Time Workflows to Show Error Messages
Getting Your Head Around Dynamics CRM 2013 Processes – Part 2
No-code Customizations with North52 Formula Manager, Part 3: Case Resolutions
No-code Customizations with North52 Formula Manager, Part 2: Line Items
Great article…especially about how the business analyst customizing CRM must be more aware of the event pipeline and transaction capability of Dynamics CRM when working on CRM 2013.
Have you seen any tests done to determine the magnitude of the performance difference between Real-time Workflows and Synchronous Plugins? I’ve been meaning to do a test to understand this impact.
One correction, we’ve had auto-numbering for certain entities (I know Quotes and Orders) since at least CRM 1.2.
Thanks for the feedback, Nick! Yes, I suspected auto-numbering existed even before 3.0, but since I haven’t ever worked with that version, I played it on the safe side. At least the configuration dialog for entity numbering hasn’t been updated since 3.0, which must have added at least the campaign number there (as the marketing module didn’t exist before that). It’s good to see that the product team has flagged this feature request as an item considered for a future CRM release, since it’s not really something customer specific that system customizers should spend their energy on implementing.
I don’t have any performance data on the synchronous workflow vs. plug-in comparison. I saw a comment on the forums that due to the real-time workflows having to leverage the Windows Workflow Foundation and thus perform state persistence there would be an overhead that plug-ins can avoid. Hopefully MS will include some statistics to compare these two approaches in their future white papers, as I’m sure it’s a topic of interest to several CRM developers and customers.
Thanks for this Jukka,
I’ve just started looking into auto-numbering. Your post is well timed for me.
For ease of maintenance I’m trying to do as much as possible without custom plug-ins. It honestly surprises me that this isn’t an OOTB feature already – especially since it’s available for the other entities you mentioned.
Long-term would you advise using the solution outlined above? Or a commercial solution? Considering that down the road MS may roll this out in a future release anyway.
If the number will be something that the customer sees or system end users often interact with manually, the format of the number will likely carry importance, which means you’d need a bit more functionality than the simple workflow example here. In such a scenario I would currently look at the available add-ons and determine which one best meets the requirements. If uniqueness of the ID in high volume transaction scenarios is critical, I’d also encourage you to test the results first to verify that the solution can handle it.
If the auto-number is simply a “nice to have” identifier shown internally on record forms, to help the users reference “project 101” instead of long & ambiguous record names, I think the workflow approach with CRM 2013 would probably be sufficient. It’s certainly friendlier than GUID’s!
Hi jukka, and thank you for this great article.
I have just one question: did you try to launch bulk creations in differents threads to check if this solution is 100% sure to not repeat any number?
in other implementations i had to create plugins using Mutex to be 100% sure of this.
thanks
Demian, the only test I did for this post was via the standard Import Wizard of CRM in a single browser session. I know that multiple threads and their impact on business logic such as auto-numbering have been discussed on various forums, so I didn’t even bother trying to find out “the final truth” on whether a standard real-time workflow could produce a reliable unique ID for each record under such circumstances. Whether the CRM 2013 support for running the workflow within the transaction provides sufficient database locking for auto-numbering while avoiding any errors for record creation on multiple threads is something I’d be very much interested in hearing from others if you’ve got any test results to share. Without such proof, I’m recommending that people use a commercial auto-numbering add-on product with sufficient support resources behind it to ensure that the results under heavy load won’t cause issues with duplicate numbers being assigned to new records created.
Very good topic. I have made my own solutions regarding this since CRM 3.0 days and I agree that it should be part of the framework, but as it is not, probably for priority reasons, we have to develop our own. In CRM 3.0 and 4.0 when working with plugins to generate numbers and having multithreaded import programs we did have issues with duplicates. I do Think, ( I have not tested it though) that if generating a autonumber from a plugin in preoperation and not prevalidation, you will get it within the transaction which will cause other potiential threads to wait until the locking thread is done, avoiding duplicates. Not sure how synch workflows are handled transaction wise.
Thanks for your comment, Gustaf! Based on what it says in the CRM 2013 SDK, real-time workflows do indeed run within the transaction, which seems to indicate that they might be safe from any other threads trying to access the same auto-number counter entity:
“Executes in a stage of the event execution pipeline, similar to synchronous plug-ins. The real-time workflow can execute before (pre-operation), after (post-operation), or during the core operation. A real-time workflow that is executed during the core operation is the implementation of a custom action. Real-time workflows can be ranked within a stage just like you can do with plug-ins.”
Regarding the inside/outside transaction comment, our solution is to use a secondary entity to implement a latch/lock behaviour (I believe that is what it is commonly called) to prevent simultaneous “check out” to retrieve and increment next free number.
This solution might introduce a few other unwanted db locking issues during very heavy load, but it 100% certifies unique numbering.
Thanks for commenting, Jonas! Did I understand correctly that in your solution’s data model there’s a third entity used, in addition to the business entity and the counter entity? So that the actual auto-number requests are queued into the secondary entity and updated asynchronously to the actual business entity requiring the numbers, to avoid locking the main business entity records under a heavy load?
Seems like I haven’t been the only one interested in exploring the capabilities of real-time workflows to handle auto-numbering requirements. Shan McArthur has written an excellent blog post illustrating how to use two custom entities and workflows triggered from the creation of the business entity record to deliver a real-time automatic number for the record: The Power of Synchronous Workflows For Autonumbering.
Hi Jukka! this is a really interesting thread.
I’m going to write something about this also. I’m developed a .NET solution for a bulk creation, i started creating in two threads, and the first 5.000 records were fine with no duplications. After that, i started to see some duplications (just a few), the i started a third thread, and the duplications increase (also triplications).
I tested agains CRM Online Trial with UR1, so i can confirm right now, that this kind of solution doesn’t guarantee 100% of no duplication.
Anyway, if you are going to create records manually in CRM, it almost 100% safe (99,9% i say)
thanks!
My conclusions about this kind of auto numbering are in my blog (Sorry is in Spanish, but there you can see my duplicates ids): http://www.demianrasko.com/2014/01/workflows-sincronos-para-auto-numeracion.html
Thanks for sharing the results, Demian! Very interesting to know that the multi-thread scenario of the real-time workflow can still generate potential duplicates. I can imagine it’s not such an easy problem to solve if every record needs to have a unique, sequential number assigned. I wonder if there would be any method to make the number more random, by introducing a variable that is used in the generation of the unique ID that’s not subject to the potential overlap caused by multiple concurrent threads inserting records into CRM?
Thanks Jukka,
Perfect timings!! our business still used the contacts Ids to identify the customer . So its mandate to have the unique contact id. We were using a solution from a third party customer which turns out to be the worst decision we have made with the system . Its causing timeouts and hence user losing the Data.Your approach is is too good. I am fairly new to CRM . Could you please explain a little bit about he first step of workflow the linking of entities. What you mean by that. I really appreciate if you can give me that info
mathew
Mathew, I encourage you to take a look at the article by Shan McArthur, since that approach will save you from having to create any relationships to the business entities from the auto numbering entities. He also has sample solutions that you can download and install to have a closer look at the numbering mechanism.
Hi Jukka,
Thanks a lot it works perfect. Saved a lot of time. Did you find any work around to remove the , from the number?
Mathew
Sorry in your case its SPACE. but with my number formatting its ,
Mathew
Mathew, you would probably need a custom workflow activity to do a replacement of the space or comma characters with a null value. Can’t think of a way to do this with out of the box workflows alone.
Thanks
Mathew
Thanks much Jukka,
I’ve just started looking into auto-numbering generation for my Service Activity entity appointment confirmation.
I created a AutoNumber custom entity, and created a 1:N relationship with Service Activity. AutoNumber –>1:N–> Service Activity.
I followed your steps to configure a Synchronous Workflow (CRM2013 Online) and publish it.
While I am creating New Service Activity, am getting bellow log errors –
Starting sync workflow ‘Generate Confirmation Number’, Id: cff06323-2cf0-e311-92ff-6c3be5bde970
Entering UpdateStep1_step:
Entering UpdateStep2_step:
Entering UpdateStep3_step:
Sync workflow ‘Generate Confirmation Number’ terminated with error ‘Cannot find record to be updated’
I really appreciate if you can give me some info here. I tried below link approaches but no success.
http://crmintherealworld.blogspot.fi/2008/12/using-workflow-to-generate-auto-numbers.html
http://leontribe.blogspot.fi/2010/12/auto-numbering-using-workflow.html
Shashi, did you try the approach shown by Shan McArthur where there isn’t a direct relationship between the business entity and the auto number entity?
Hello,
could you please explain how to increment the record? I don´t find that Option in worklfow Editor.
or is that Option only availbable on a integer field (not string)?
Frank, you’re correct in your assumption. CRM wouldn’t know how to increment a field that has data in a string format.
Jukka,
Thanks for this good work and the update to Shan McAurtur’s method. Shan’s seemed more highly recommended by you so I went with that solution for our system. It does seem very elegant aand seems to work in almost all scenarios including OOB data import.
However, we integrate records using Scribe Software. This integration seems to be too quick for the real-time workflows and some seem to be triggered at the identical time leaving identical IDs.
Can you think of any OOB way to update either workflow that will ensure a staged increment or at least unique values?
Andy
Recently came up with a thought regarding an auto-number utility using either time workflows or synchronous plugins( pre or post operation), also either of the cases it works in DB transaction.
Query is regarding in case of plugins there are many examples which shows lock is used for generating the next id, kindly let me know if this is really required as per my knowledge it work in db transaction.
Any help will be really appreciated.
Best Regards,
Sarath
Sarath, please have a look at the Scalable Customization Design in Microsoft Dynamics CRM whitepaper written by Roger Gilchrist. It addresses this specific scenario of autonumbering and the impact of database locks in greater detail then any other information source I’ve come across.
omberta created a working CRM solution that does autonumbering: https://crm2015autonumber.codeplex.com/