How to set Calculated and Roll up fields in MS Dynamics CRM?

Calculated Fields

In Microsoft Dynamics CRM, Calculated Fields help you to perform manual calculations used in your business processes. For example, if you have a few fields and you need to display the calculated total of those fields. Before the 2013 version, we used to do this by using JavaScript, and from 2013 onwards, business rules were introduced. Post the arrival of business rules, we use Calculated Fields to perform these kinds of operations.

Let us take three fields:

• Stationery Cost
• Machine Cost
• Production Cost

See the screenshot given below:

We created the three fields mentioned above as decimal numbers with Simple Type. The total cost field will be created as a decimal type.

As mentioned above, 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 screenshot.

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 and the field, add all the fields to the form, and 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, and 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.
• The field value remains locked and we can’t edit it manually.

Disadvantages of Calculated Fields:

• Updates to these fields would not trigger workflow updates.
• Fields are not available as part of ‘create’ or ‘update’ plugins, but are only available for ‘retrieve’.

Roll Up Field

Roll up field 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 implementation 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 click the ‘Edit’ button in the above form, we get another form where we enter the calculation logic of that field.

1. Source Entity – The default entity for which the Roll up field is created. In our example, the source entity will be ‘Account’.
2. 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.
3. Filters– Here we can apply filters for our source entity.
4. Related Entity – Select an entity to get an aggregation of related entity data in our Roll up. In our example, we selected ‘contact’ as related entity because we wanted to calculate the total count of all the active contacts associated with an account.
5. Filters– Here we can apply filters for our related entity.
6. Aggregation– Here we specify the aggregation function, which we want to apply in the 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 ‘+’ on 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.

Disadvantages:

• Automatic updates can be set for a minimum of 1 hour.

Check out our blogs on MS Dynamics CRM. Do share your comments with us. To learn more about Suyati’s MS Dynamics CRM expertise, feel free to write to us at services@suyati.com.

Related Posts:

• 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 K