The Power of Synchronous Workflows For Autonumbering

CRM 2013 has a number of important improvements, including a flattened database model and synchronous workflows.  This article will discuss these improvements in the context of implementing an auto-numbering solution.

Auto Number Managed Solution File

Flattened Database Model

One of the improvements in CRM 2013 is that the database model has been flattened.  There is no longer a base table and an extension table for each entity in CRM.  This was done for performance reasons as well as in an overall effort to reduce or eliminate SQL deadlocks.  In previous versions of CRM, there were two separate tables and the SQL queries issued by CRM had to join those tables.  Different queries had different orders of operations which would result in SQL deadlocks under high load.  The scenario where this was readily apparent was in autonumber solutions, especially in bulk import scenarios.  The Adxstudio Productivity Pack solution included an auto numbering solution by implementing a two phase commit model on two different entities as well as logic that had to catch errors and retry operations.  The combination of this resulted in a reliable autonumber solution, but the performance overhead was significant.  Using CRM 2013, the flattened database model has significantly reduced the problems related to SQL deadlocks and that has simplified the implementation for autonumbering solutions.  It will also simplify many other scenarios such as doing bulk imports on a busy CRM system and two way synchronization with internal databases and other systems.

Synchronous Workflows

CRM 2013 now allows us to run a workflow synchronously.  There are two important things that result from this new feature.  The first thing is that it is now possible to have workflows run on data as it is inserted into CRM instead of later, which enables us to build systems that have better data integrity and a better user experience.  The second benefit of this feature is that the workflow is now run inside the CRM execution pipeline which has the benefit of being inside of the SQL transaction.  Full commit and rollback is now supported.  For the autonumber scenario, it was always desirable to have the autonumber identity of the new record created when the record is created.  This is why the Adxstudio autonumber solution was implemented in plugins instead of a workflow.  Having the human-readable number available at the time of creation is important for CRM forms, charts, views, search, and workflows.  With synchronous workflows, we can now implement auto numbering without plugins or any code, and have the number created at the time that the underlying CRM record is created.

Auto Numbering Approach

I am going to demonstrate a sample auto numbering approach and provide you with the solution file.  The goal is to do this without a single line of code using only configuration mechanisms.  I will provide you with the solution file so that you can simply import it into your CRM and experiment with the technique.  The Adxstudio Productivity Pack will still include a full fledged auto numbering solution and the sample in this blog article will not attempt to implement all of the features, but will give you enough information to learn some of the benefits of these new CRM 2013 features.  Keep in mind that the Adxstudio Productivity Pack is free, so I highly recommend you use it for a production system instead of the solution in this blog article.

To implement an auto numbering solution, we need to create an entity to hold the last number used.  This entity will serve as a dictionary of different auto numbering sequences.  We will call this entity Auto Number Definition.  The two important fields will be the name field and the Last Number Used field.

Other people have taken the approach of creating 1:n relationships from the auto number definition entity to their primary CRM entities such as account and contact, but I wanted to do this without having to create a large number of relationships that could complicate your solution layering with additional dependencies.  In order to do this, I have chosen the approach of creating a separate entity called Auto Number Request.  The purpose of this entity is to use it in a workflow to create an instance of a record that the workflow can then set and read information.  Keep in mind that even though dialogs have variables, CRM workflows still do not have variables except for automatic variables for entities that are created in the workflow.  The Auto Number Request entity has a n:1 relationship to the Auto Number Definition entity.  That is the only relationship that is needed to implement automatic numbering - no need to create any relationships to account or contact entities.

I implemented the automatic number increment mechanism in a workflow on the Auto Number Request entity.  When an instance of the record is created, it will look at the Auto Number Definition entity specified and will use the workflow 'increment' feature to increment the last number used.  It then copies the new number back to itself because any parent workflows will only be able to span one relationship and will be unable to read the number through the n:1 relationship.

To implement auto number on an entity, such as account, all I have to do is create a new row in Auto Number Definition, then create a synchronous workflow and have it create an Auto Number Request record, setting the Auto Number Definition relationship to a static row in CRM.  Once the record is created, it can then read the new number and transfer it back to its own record.

This approach will need a little cleanup - keep in mind that a new Auto Number Request entity will be created in each workflow.  The best way to clean this up is to configure a bulk data deletion job each day.  You could get fancy in the workflows and mark the Auto Number Request entity as disabled, then clean up disabled records, or you could just delete any records that are older than today or some other threshold.  The challenge is that the CRM workflow cannot delete any records.  Perhaps that will change in the future.

Auto Number Solution Files

Here is the main solution file for the implementation of a generic auto number mechanism:

autonumber_1_0_managed.zip

Here is a solution that demonstrates how to use the above auto numbering mechanism with the account entity:

accountautonumber_1_0_managed.zip

Comments