Dynamics CRM Load Balance Email Router

We can load balance emails in dynamics CRM with a few customization steps using multiple email routers. The concept is simple, I use a workflow to generate emails and assigned those emails to specific users (service accounts). I link those users to a custom entity which will round robin (load balance) between them. On the same custom entity I create a counter field which defines the next user in sequence. Our load balance solution will require the following items:

1. Custom Entity "Email pool".
2. Entity Relationships 1:N (1 to many).
3. Small piece of Javascript code to set the default email pool which controls the loop.
4. Workflow to trigger the emails.
5.  Database trigger to update the Email Pool counter.

The following diagram illustrates how we will load balance emails:

• User creates a record
• An email is triggered by the workflow
• Workflow assigns the email to one of the 3 users
• Email router processing email for that user will then deliver the email

Step 1 - Create and customize the new entity (Email Pool)
First we need to create our custom entity pool which will hold the users responsible for processing emails.

Go to: Settings > Customization > Customize the system > highlight entities > On the right click New
 



Now we create a field for each user:


Create Pool 1, Pool 2 and Pool 3

 Create a new field for the counter:


Now add the new fields to the main form:


Step 2 - Create the Relationship
Create a relationship 1:N (1 to many) with any entity which you would like to balance emails. On this article we are creating the relationship with the accounts entity.
The relationship should look like this:
 

 Step 3 - Create the email pool record and link the CRM users
Create 3 Users called Router1, Router2 and Router3.

Create an Email Pool record and assign the users, it should look like this:


 Step 4 - Customize the Accounts entity
The next step is to customize the Accounts entity to allow the Email Pool Entity to be referenced from within the account records.
We are adding the Email Pool field on the Accounts form:
 

You can customize the Email Pool field to be hidden by default or do that from the OnLoad event script.

Click on Form Properties and configure the following script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

function SetPool() {   var isCreateForm = Xrm.Page.ui.getFormType() == 1;   var y = Xrm.Page.getAttribute("new_emailpoolid"); var guid = []; guid[0] = {}; guid[0].id = "{4CE54A4B-946B-E111-8244-000C29D8F73A}"; guid[0].entityType = "new_emailpool"; guid[0].name = "Email Pool";   if (isCreateForm) {   y.setValue(guid); }   Xrm.Page.getControl("new_emailpoolid").setVisible(false);   }

Step 5 - Create the workflow
The workflow will send an email when the record is created. It reads the email pool counter and based on this value will route the email to the appropriate user in the sequence. A trigger on the database side will update automatically the email pool counter.




Step 6 - Update the Email Pool Counter
To update the counter we could create a workflow that would update the Email Pool counter field, however multiple workflows updating the same field is not a good approach and may result in locks. Instead of using a workflow we can update the counter field using a database trigger.

The trigger would look like this.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

BEGIN declare @counter int   set @counter = (     SELECT top 1 new_emailpool.new_poolcounter from new_emailpool )   IF @counter = 1     --set @counter = 2     UPDATE new_emailpool set new_poolcounter = 2 ELSE IF @counter = 2     --set @counter = 3     UPDATE new_emailpool set new_poolcounter = 3 ELSE IF @counter = 3     --set @counter = 1     UPDATE new_emailpool set new_poolcounter = 2 END

Step 7 - Set up the Email Router
The last step is to configure each Email Router to process email for the specific user. In order to do this the email router configuration file needs to be manually customized, adding the user ID and the update period set to 0. For more information on how to do this and understand the concept read of my articles on how to install email router on multiple servers:
http://quantusdynamics.blogspot.com/2012/02/dynamics-crm-installing-email-router-on.html 


Using Queues
It's possible to use the same concept but instead of users we can use queues. The process is the same, however we will need extra steps.
 The following diagram illustrates the same concept:




Conclusion
The concept works perfectly and is very reliable. You can extend the solution to provide some resilience implementing SQL scripts to move emails to different users in case the email router has crashed. 

I hope you enjoyed it.

Regards
Nuno  Rodrigues Costa
C:\My\Blog\Dynamics\CRM