Custom Entity "Email pool".

Entity Relationships 1:N (1 to many).

Small piece of Javascript code to set the default email pool which controls the loop.

Workflow to trigger the emails.

 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:

We are adding the Email Pool field on the Accounts form:
 ?

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 Routernew_poolcounter = 3