CRM2015 introduces a new feature to help with real time calculations. In previous versions of CRM, you would need additional development in order to do this, such as writing code using Plug-ins or JavaScript. Calculated fields can use values from the current entity or related entities, but cannot span more than 2 entities. A Calculated Field will always be displayed to the user as a read only field.

It’s also worth noting that the data shown in a calculate field will not actually be stored in the MSCRM DB. Instead, a function will be called in order to perform the real time calculation and then display the result in a form, view or chart.

The table below shows the list of supported data types, as well as the functions/operators that are available for use within a calculated field:

 

Data Type

Operator/Function Available

Whole Number

Decimal Number

Values                           

= (Equal), + (Plus), - (Minus), / (Divide) and *(Multiply).                                               

Single Line of Text

Multi-line of Text

CONCAT, TRIMLEFT and TRIMRIGHT.

Date Time

ADDHOURS, ADDDAYS, ADDWEEKS, ADDMONTHS, ADDYEARS, SUBTRACTHOURS, SUBTRACTDAYS, SUBTRACTWEEKS, SUBTRACTMONTHS, and SUBTRACTYEARS.

 

Configuration

In this demonstration, I have added 3 fields to my event form: Total Number of Tickets, Ticket Price and Total Estimated Value. I would like to use a calculated field to automatically calculate the estimated revenue for this event but multiplying the ‘Total Number of Tickets’ with ‘Ticket Price’.

The 2 fields I will be using in the calculation are just standard fields (Number of Tickets (Whole Number) and Ticket Value (Currency)) so I’ll presume we’re ok with creating these fields. The third field, Total Est. Revenue is my calculated field so let’s look at the properties of this field in more detail.

You will see from my screenshot that I have defined this as a ‘Calculated’ field type. In doing so, you will also see that there is an ‘Edit’ button available which will allow you to configure the calculation for this field.

 

When you click on the Edit button, you will see the following screen.

The first thing you’ll probably notice it that the configuration screen uses the same editor as Business Rules so I expect you are already familiar with the general principals of this screen.

 

In my example I have used a condition that ensures my 2 relevant fields contain data before performing the calculation action.

 

I need to then enter my calculation in the ‘Action’ section shown below, which in this example is: “Total Number of Tickets * Ticket Price”. Tip: The action section uses intellisense so as I start to type I will be presented with a list of available fields or functions.

This final screenshot shows my completed configuration.

Overview

The screenshot below shows my Event form which includes the 2 fields I used in my calculation, plus the calculated field itself. I have entered a number in the ‘Total Number of Tickets’ field as well as a value in the ‘Ticket Price’ field. Note: Notice that my ‘Total Est Revenue’ field is read only as mentioned previously.

 

Once you have saved your form, you will see that the ‘Total Est Revenue’ field has now been populated with a value.

 

 

Remember, as previously mentioned, the value in the calculated field is not actually saved as data into the MSCRM database. A function is being called in the back end in order to perform the calculation and display the result to the user.

 

Notes and Take Away Points

  • Calculated fields are always performed in real time
  • Calculated fields are always read only
  • Calculated field values are not stored in the MSCRM DB. Instead, a MS SQL function is called in order to perform the calculation and then present the result in a form, view or chart
  • Calculated fields can use data from the same entity or any other related entity. They can only span a maximum of 2 entities
  • Calculated fields cannot be used to trigger events such a workflow or plugins
  • Calculated fields can be used within another calculated field, however you can only have a maximum of 5 chained fields to help mitigate against any potential performance issues
  • If the calculate field results is over 4000 characters, the data will be truncated with no indication made to the user.
  • Calculated fields will not work in offline mode
  • Circular references are prohibited so therefore you cannot reference the same calculated field within the calculation (i.e. within the action section of the calculated field properties)
  • Saved queries, charts and visualisations can only have a maximum of 10 calculated fields
  • You can only sort a view if the calculate field uses fields from within the same entity. If the calculated field used fields from a parental entity, you will be unable to sort on the calculated field.

Conclusion

Calculated fields should help reduce the need for additional code when wanting to perform real time calculations. This simplified configuration should help to reduce the amount of rework required when upgrading CRM. A possible down side to this could be that as the calculated data is not stored within the MSCRM DB, you are limited to how you can report on this data. You will be ok to show this in views and charts within CRM and therefore still be able to export the data, however you wold not be able to query this data using additional reporting tools such as Reporting Services (SSRS). Finally, it is important to decide if workflow rules are dependant on the values of the calculated field. If so, then plugins, jscript or indeed the ‘Basic Math’ custom workflow activity available in the Workflow Essentials solution may be a better option.