Trigger Field Updates from Roll-up Fields in MS Dynamics CRM 2015

I recently read a post from Jukka Niiranen on limitations of using roll-up fields in Microsoft Dynamics CRM 2015 (, which was very useful – however I have been working on a client solution that did benefit from this feature using some simple configuration and business rules.

The idea is that you can use a roll-up field in CRM based on hierarchical and/or associated records to display a cumulative currency value, such as “Total Spend” or “Total Revenue” to trigger updates to other attributes such as, “Service Level” or “Customer Type” in the associated Account or Contact without using code. In some cases, using the “keep it simple” principle then you can achieve this using;

  • Roll-up Fields
  • Roll-up Filters
  • Business Rules

The scenario I am using as an example is that a customer makes a booking that is associated with their contact, the more they spend (new_totalnetbookingspend) the higher their “Loyalty Level” (new_membershiployaltystatus), but the principles can be applied to your customisations and other situations. So let’s get started.

Please Note; Business rules run only when the form loads and when field values change. They do not run when a record is saved, unless the scope for the rule is set at an entity level. For this scenario my client works within the Contact entity – it cannot currently replace the functionality of a plug-in.

Step 1 – Create a Roll-up field Go to your solution in CRM and the entity of your choice, (in this case I am using Contact) and either the asscoiated “Fields” or the “Form” you are going to work with, (this will depend on your working preference), then create a new field. Next complete the required information for your field, such as; the “Display Name” and “Field Requirement” level and add a “Description” if you desire, (remember in this version of CRM the description appears when the user rolls-over the field label). Then choose your “Data Type” in this case the one I am using is “Currency” and then the “Field Type” as “Rollup”. Then “Save” you field.


Step 2 – Define your Roll-up Calculation Then navigate down to the “Field Type” field and click the “Edit” button that should have dynamically appeared in the field properties window. This will launch the pop-up window for your roll-up calculation. You will need to complete the following;

  • Related Entity
  • Filters (the use of filtering is optional)
  • Aggregation

For filtering you may want to filter out records assigned a certain ‘status reason’ or ‘type’ depending on the requirements, for aggregation you can choose a “SUM” of the target field (in this case a sum of the NET Booking Amount),  I have included a screen shot of my example calculation below. When you are done click “Save and Close” and then add you new field to the entity form and Publish” your changes.

rollup_calc I also have the existing field which I want to update based on the roll-up value which is an option set, you may need to create a field before continuing to the next step.

Step 3 – Create a Business Rule As Jukka explains in his post; “No standard workflow or Business Rule will allow you to initiate the roll-up field calculation based on the custom business logic you’d like to define.” However you can trigger a business rule based on the value held in the field itself. Out of the box this relies on the roll-up value being updated either using the “refresh” or the system job to calculate the roll-up being completed. The business rule will execute when the record is loaded or the roll-up field value changes. So, for this example my contact field “new_membershiployaltystatus” has three options; Gold, Silver and Bronze. The loyalty status will be set based on the following rules;

  • Bronze – If the contact has a “Total NET Booking Spend” < $2500.00
  • Silver – If the contact has a “Total NET Booking Spend” >= to $2500 and < $5000
  • Gold – If the contact has a “Total NET Booking Spend” >= to $5000

Please Note; “Bronze” is also the default value for my option set “new_membershiployaltystatus”. To create a new business rule in your CRM solution navigate to the entity (in this case Contact) and click the “Business Rules” item in the nav’ tree and then “New”. You will then need to complete the Condition statements and Action for each of the possible options, as per the example below, then “Save” and “Activate” your business rule. You can also choose a form to execute the rule against, or execute against all forms.


Additionally I locked my fields on the form, however if you want the option set to be “read only” you will need to define this in the rule, not on the form – if you do it from the form the value changes might not be committed when the record saves. Don’t forget to test to check your results!  Easy. 🙂


 Happy CRM’ing!
**UPDATE** 16/08/2015 – You can use a plug-in to force the roll-up calculation to be updated using CalculateRollupField, you can also populate another attribute such as a date field to be populated when it has and use this to trigger a standard workflow. Thus negating the need for the record to be opened in the UI for the associated attribute to change, for more information please see: (MH)

Trigger OnLoad Workaround for AJAX issues in CRM 2013

I thought I’d create a quick post as I was required to use a workaround for a client today involving an OnLoad event that wasn’t being called correctly in Microsoft Dynamics CRM 2013.

The background on this is that the AJAX used to refresh the page in CRM 2013 doesn’t necessarily call the OnLoad action, in this case it was affecting the JavaScript used to switch the Account from base on the value “Account Type” option set.

I remembered that a colleague had mentioned this in the past and advised that there was indeed a workaround using configuration, namely;

  • 1 x Boolean field
  • 1 x Synchronous Workflow
  • Update to the fields OnChange event

So what do you do?

Step 1 – Create a new Boolean (Two Option) field for the entity or entities effected. In my client’s case it was Account, using the following steps; Settings > Solutions > Choose Your Solution and Open it > Entities > Account > Fields > New.

Then make sure you add a display name that will make sense to you, set the “Field Requirement” to “Optional” and set the “Type” option to “Two Options”, then click Save and Close.


Step 2 – Next add the new field to the required forms against your entity, then click “Change Properties” on your field and uncheck “Display Label on the Form” and “Visible by Default” and also check “Lock the Field on the Form” (this will endure admin’ users will have to think twice before removing the field) then click “OK” to close the box. Next click Save and then Publish your changes and close the form (still no button for Save, Publish and Close), repeat for other forms as required.


Step 3 – Create a new Synchronous Workflow (real-time) Process against the entity with “Organisation” as the scope (in this case Account) in CRM that is triggered when;

  • Record is created
  • Record status changes
  • Record fields change (check the required fields to apply this workflow to)

Add a workflow step to update the entity (in this example Account) and in the “View Properties” area select the Boolean we just added and set the field value to “Yes”. Save and Activate the workflow.

Please Note; be careful to select the right option in the “execute as” property, if the owner of the workflow is the system admin’ account then you should have no issues, if it is the user who made the changes, then you will have to consider their security role for the entity.

Step 4 – Finally add the JavaScript from your OnLoad that switches the form etc. to the OnChange event of the new Boolean field in your entity form. You can find this under the field by navigating to; Change Properties > Events > OnChange > Event Handlers > Add.

Remember to add the function in minus “()”. Then click “OK” and Save, Publish and Close. Repeat as required for other forms.

Don’t’ forget to test your changes and you are done.

Using Queues in Microsoft Dynamics CRM

Queues can be instrumental in helping to organise, prioritise, and monitor the progress of your work, such as Cases while you are using Microsoft Dynamics CRM.

If used properly they can act as a centralised location for users to manage case progress, respond to service calls or work with prospects in your sales pipeline. There is often times when the feature is overlooked or misunderstood, however as the functionality was updated around the use of queues in MS Dynamics CRM 2013 SP1 and 2015 there is a lot more to do with queues and it is not as difficult as you may think.

Recently I published a blog post on the use of Routing Rules in CRM 2015 and CRM Online and during the process referenced the Queue entity. So I thought I would expand a little. At a high level what you may or may not know is that queues;

  • Can be used with any custom entity
  • Can be made “Public” or “Private” (with Private queues making the queue items only available to members)
  • Private queues are auto-created for new users or teams when they are created
  • Queues can actually contain multiple entity types, (e.g. tasks, emails, cases)
  • Users can work on Queue Items to prevent task duplication
  • Queues can be workflow enabled
  • Queues cam ne enabled for auditing

Public or Private? In CRM there is an attribute called “QueueViewType” which is used to define if a queue is public or private. Private uses have individual members (users) which are used to allow/remove access to the queue. You can add a team to a private queue and this will add all the team members as members of the private queue. Some other things to remember about queues are that;

  • All user queues are private, so only the user will be able to see queue items in this queue
  • Team queues are marked as private by default, the team owner and all its members have access to the queue
  • All other queues are considered public, anyone with ‘read’ access to queues can see them

For information on CRM queues in previous versions check this MSDN article;

Creating a Public Queue:

Creating queues are simple enough in CRM, as mentioned above each user account has it’s own private queue created automatically. The ability to create a queue will be based on the users security role, out of the box the following roles can create a queue;

  • Customer Service Manager
  • System Customizer
  • System Administrator

If you have an applicable role with create permissions then you are good to go – if you are not sure you can check your role by using the steps in this Customer Centre article;

Next go to; Settings > Service Management/Business Management* > Queues, then choose New. You will then need to complete the required fields in the Queue form, out of the box these fields will be;

  • Name
  • Type (Public or Private)
  • Owner (Lookup to System User)
  • Convert Incoming Email To Activities

The “Incoming Email” field is for exactly that purpose, here you can enter the email address that receives all messages for this queue, for example; ‘’.

In the “Convert Incoming Email to Activities” field you will need to make sure you choose the appropriate action, by default this will likely be populated with “All email messages“, this will not be suitable for everyone. Depending on the purpose of the queue you may want to only covert emails that are “Email messages in response to a CRM email” or only emails from Leads, Accounts and Contacts.

The locked field referring to “Mailbox” will be automatically populated with a new mailbox record when you have created the Queue, once this has occurred you can update the mailbox details using the link.

Finally, click Save.

*Service Management will be visible in the Settings area of CRM based on the version you are using.


Queue Items:

Queue items are displayed in the associated sub-grid within the Queue form. If you are using Cases for your solution then all cases that are either; (a) routed to this queue (using routing rules) or (b) manually assigned cases will be displayed.

It is worth noting that a queue item is automatically deactivated if the associated record is updated from Active to Inactive, which applies as you’d expect to all queue enabled entity records that have Active or Inactive states in CRM.

Create an Automatic Case Creation Rule;

Automatically creating cases from incoming email can be awesome for reducing the amount of manually created cases in CRM and increasing the actual contact time of a support desk and your agents for both internal and external channels. Case creation rules use conditions similar to those found in Advanced Find to automatically convert emails into support cases.

Please Note; This functionality applies to MS Dynamics CRM Online that were either updated to CRM 2013 SP1 (or Spring ’14 release as it is known) or the CRM 2015 product update. If your deployment is On Premise you will also need to have updated to CRM 2013 Sp1 or CRM 2015.

Step 1 – Create a New Record

To create a new Case Creation Rule in CRM 2015 click the “Email to Case Settings” button in the ribbon from your Queue (as created in the above steps). The create window will pop a new Case Creation Rule form, complete the required fields, which include;

  • Name
  • Source Type (Email)
  • Queue (Lookup to your Queue)
  • Owner

Now, you can only associate one rule per source type, so in this example once we have selected the “Source Type” as “Email” in a rule for this queue, we cannot have another active rule associated unless it is used for Social Monitoring. Also, make sure your queue has an email address as per the previous section.


Step 2 – Specify Conditions

Next you will need to go to the “Specify Conditions for Case Creation area in the next section of the form and choose your condition or conditions (as you can add multiple). These include;

  • Create cases for email from unknown senders+
  • Create case if a valid entitlement exists
  • Create cases for activities associated with a resolved case
  • Create case when the case associated with the activity is resolved since

Create Cases for email from unknown senders – if you select this option all emails from unknown senders (i.e. a sender that cannot be associated with an email in a CRM record) are converted to a case, be default this will also create a contact record. However this does work in conjunction with the personal options for “Automatically Create Records” set by the user that owns the rule.

+If this option is not selected cases will only be created automatically for email senders that are attributed to an Account or Contact in CRM, (those email addresses associated to other records will not create a case).

Create case if a valid entitlement exists – if you select this option then the rule will do exactly that, to read more on entitlements see; If the sender of the email is a contact that has a parent account with a valid entitlement and the contact is associated using the sub-grid on the entitlement, then a case is created, this is also true if the entitlement sub-grid is empty as the entitlement can be applied to all contacts for that account.

Create cases for activities associated with a resolved case – a case will be created if the email is related to a resolved case if it references an active case then no case is created. If you select this option then the option for Create case when the case associated with the activity is resolved since appears which allows you to select/define a duration. This will mean that a new case will be created only if it is resolved earlier than the specified duration. If it is later then it is associated with the existing resolved case.

When you are done click “Save”.

Step 3 – Specify Case Details

Now we need to add conditions for the creation rule, for those users familiar with Advanced Find it is as easy as that. We also need to add the case properties for the records we are going to work with once created, such as the priority etc.

In this step you can define how to treat cases based on their customer category or the contact type, a good tip is to be sure that you use the correct reference in the conditions, for example, for Contacts it is; “Sender (Contact)”, for Accounts the reference is; “Senders Account(Account)”. You add conditions by clicking the “+” icon near for “Condition”.


Once you are done click “Save” and if you are happy click “Activate”.

Please Note; Once a case is created the incoming email is removed for your Queue. If there are no routing rules to apply to the newly created case to a user or Queue then the case owner will be set to the user that owns the case creation rule.

Hope this helps, if you need to dig deeper check out the CRM Customer Centre, or for technical information such as case creation from a web service see MSDN. Happy CRM’ing!