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.