How to set Calculated and Roll up fields in MS Dynamics CRM?
Let us take three fields:
- Stationery Cost
- Machine Cost
- Production Cost
See the screenshot given below:
We created the above-mentioned three fields as decimal numbers with Simple Type. Total cost field will be created as decimal type.
As mentioned the total cost contains the sum of stationery cost, machine cost and production cost. When we click on the ‘Edit’ button in the above form, we get another form where we enter the calculation logic of that field. Check the following screen shot.
Here the condition is ‘If the name field contains data, the total cost should be the sum of stationary, machine and production costs’.
Once the calculation logic is written, save the business rule, the field and add all the fields to the form, then publish the customization.
In CRM, open the form and enter the value for stationery cost, machine cost and production cost. Then save the form. After saving, just reload the page, the value of the total cost will be displayed as shown below.
Advantages of Calculated Fields:
- Helps to eliminate the complexity of business rules in forms.
- Field value remains locked. We can’t edit manually.
Disadvantages of Calculated Fields:
- Updates to these fields won’t trigger workflow updates.
- Fields are not available as part of create or update plugins, only available for retrieve.
Roll up Fields
Roll up Fields is one of the interesting features implemented in CRM 2015, which provides a way to perform high-level aggregation from child records to its parent record.
Let us take an example to understand the implementing and working of Roll up fields. Suppose we wanted to calculate the total number of contacts associated with the parent account. To do this we can use a Roll up field.
According to our example we can set the field type as “Roll up” and the data type as “Whole Number”. When we clicking the ‘Edit’ button in above form, we get an another form where we enter the calculation logic of that field.
- A) Source Entity – The default entity for which the Roll up field is created. In our example, Source Entity will be Account.
- B) Use Hierarchy can be set to Yes or No for utilizing parent-child account relationship. In our example, it should be set to ‘YES’ because we want to get the aggregation for all child accounts of a parent account.
- C) Filters– Here we can apply filter for our source entity.
- D) Related Entity – Select an entity to get aggregation of related entity data in our roll up. In our example, we select contact as related entity because we wanted to calculate the total count of all the active contacts associated with an account.
- E) Filters– Here, we can apply filter for our related entity.
- F) Aggregation– Here we specify the aggregation function, which we want to apply in roll up field. In our example, we wanted to count the contacts.
Now we can create a new account. Now the roll up field has zero number of child contacts. We can add contacts related to this account by using the ‘+’ in the right side of the form.
When we click on ‘+’ button, we will get a quick form to create contacts related to that account. Fill the details in the contact form and save the form.
After saving the contact to the account, refresh the Roll up field. Now the Roll up field value will be updated to 1.
Advantages of Roll up fields:
- You can create roll-up fields just like how you create a regular field.
- Wide selection of aggregate functions, such as SUM, COUNT, MIN, or MAX.
- Full filter support can be set to source entity or related entity while using multiple conditions.
- Automatic update can be set for a minimum of 1 hour.
- Email HTML Editor Control in Microsoft Dynamics CRM
- Create and Manage Email Templates in MS Dynamics CRM
- Understanding Business Rules in MS Dynamics CRM
- A Quick Glance at MS Dynamics 2016
About the Author
Gokul is currently working as a Software Developer at Suyati Technologies. He is part of the MS Dynamics CRM team at Suyati. He is proficient in MS Dynamics CRM, and ASP.NET platforms. He loves playing cricket, badminton and carroms. Besides, he loves exploring new technologies and updates in the industry.