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