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 (http://survivingcrm.com/2015/02/crm-2015-rollup-fields-gotchas/), 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.

rollup_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.

loyalty_businessrule

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. 🙂

loyalty_contact

 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: https://msdn.microsoft.com/en-us/library/dn817863.aspx (MH)
Advertisements

6 thoughts on “Trigger Field Updates from Roll-up Fields in MS Dynamics CRM 2015

  1. Thanks for linking to my blog post!

    I tested the scenario of triggering a Business Rule from a Rollup Field, with the rule’s scope set on the entity instead of any specific form. It appears that the rule still won’t execute automatically after the Rollup Field value changes, but rather only when you open the specific record’s form in the CRM UI.

    In the scenario described in your post, wouldn’t this mean that the loyalty status will not get updated if the user only works with the Bookings entity and doesn’t visit the contact record form after making new entries to child records? This would potentially be an issue if the loyalty status value is used in some view, chart or subsequent workflow/rule that references this field.

    • Hi Jukka – yes you are quite right, I have added a note to say that; “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 primarily – it cannot currently replace the functionality of a plug-in”, as this works for the clients needs – but may not suit everyone. I have also highlighted the section from the post which stated “The business rule will execute when the records is loaded or the roll-up field value changes.” to make sure it is more visible. As even if the business rule is applied at entity level we cannot currently replace plug-in functionality using roll-up fields. Cheers for the feedback!

    • Hi Tim, what build version are you using, this post was written in reference to CRM On Premise – on build version 7.0.1.129, and the field was set to ‘searchable’, I could the find it inside the business rule wizard.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s