//
you're reading...
Dynamics CRM, Dynamics CRM Master

Intricacies of Calculated Fields in Dynamics CRM 2015


Microsoft Dynamics CRM 2015 has introduced Calculated fields to assist non-developer customisers to perform some easy calculations within Dynamics CRM. If you do not know much about Calculated fields, watch the following short video from Microsoft.

download

 

This blog post is not about how to use calculated fields. To know more on how to define and use calculated field, browse to “Define calculated fields

 

I wanted to discuss here the intricacies of the calculated fields.

Calculated fields are virtual fields, not physical one i.e. such fields are not committed to database.

How is a calculated field calculated?

Calculated fields are defined as “computed field” in the BASE table in the database and the computation logic is stored in a custom database function within the MSCRM database. Hence, when a form loads or views renders with a calculated field, the database performs the calculation and return the value to the application layer.

What are the implications of this design?

  1. Calculated fields are available in forms, views and SELECT statement running queries against an on-premises implementation.
  2. Since this is a virtual field and only available when retrieving the record; no workflows or plugins will fire on update of a calculated field.
  3. Calculated field value is not available within a plugin code.
  4. Duplicate detection rules are not triggered on calculated fields.
  5. The calculated field values are not displayed in the CRM Outlook Offline mode in the tile views or on entity main forms.

 

Calculated fields can span two entities only with all AND or all OR conditions.

What are the implications of this?

  1. In the formula only fields from the current entity and related parent entities can be used.
  2. Only All AND Conditions or all OR conditions possible while defining a calculated field. Multiple levels of AND/OR, a mix of AND/OR is not supported.
  3. A calculated field can’t contain a calculated field from another entity that also contains another field from a different entity (spanning three entities):

Calculated fields cannot have circular reference in calculation.

What does this mean?

Calculated fields calculation logic can include another calculated field or even a rollup field; however a calculated field cannot refer itself in the its formula or two calculated fields cannot reference each other in their respective formulas. CRM will throw circular reference error in both cases. A maximum number of chained calculated fields is 5.

Some more additional bits

  1. A roll-up field can’t reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity.
  2. Saved Queries, Charts and visualizations can have a maximum of 10 unique calculated fields.
  3. Sorting is disabled on:
    • A calculated field that contains a field of a parent record.
    • A calculated field that contains a logical field (for example, address field).
    • A calculated field that contains another calculated field.

 

About Dipankar Bhattacharya

Dipankar is a CITA-F, ITIL certified professional specializing Customer Relationship Management (CRM) and Cloud Computing. He is a certified Microsoft Dynamics CRM specialist and Architect and have been working in the field of CRM for enterprise customers across the globe.

Discussion

4 thoughts on “Intricacies of Calculated Fields in Dynamics CRM 2015

  1. Thanks for these details, @Dipankar. We have dynamics crm 2015 online. We are using calculated fields in an entity and are finding that the fields do not refresh except from the form itself. When we run a report that calls for those fields, they come back as blank unless user manually opens the form for that record itself.
    How can we get those fields to show up correctly without needing additional step of going to the record everytime? Just like the rollup fields where you can trigger their calculation on demand using CalculateRollupFieldRequest, is there a similar option for Calculated Fields also?

    Posted by varadagc | January 22, 2016, 2:30 AM
    • Calculated fields are virtual fields and not persisted in Database. Hence there is no SDK method to force calculate. We need to open the form to see the value.

      Posted by Dipankar Bhattacharya | January 22, 2016, 4:50 PM
      • Thanks for your prompt reply, @Dipankar. Appreciate it! But I am not able to understand why do we need to open only the “form” ? Why can’t running the report trigger this calculation? Aren’t these virtual fields supposed to be calculated when you run a SELECT statement against them? What am I missing? Sorry for picking your brain, but as you can tell this problem has me baffled.

        Posted by varadagc | January 23, 2016, 1:12 AM

Trackbacks/Pingbacks

  1. Pingback: Intricacies of Calculated Fields in Dynamics CRM 2015 - Microsoft Dynamics CRM Community - May 20, 2015

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

Blog Stats

  • 61,049 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 565 other followers

My Book

Follow me on Twitter

%d bloggers like this: