News Ticker

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 (59 Articles)
A multi-skilled Dynamics 365 Professional with strong experience in delivering IT projects especially across multiple industries. A Microsoft technology evangelist, a regular speaker at tech events, blogger and avid reader. Certified IT Architect and well versed in Solution Architecture of Business Applications using Microsoft platforms like Dynamics 365, Azure and Office 365.

3 Comments 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?

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

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

1 Trackback / Pingback

  1. Intricacies of Calculated Fields in Dynamics CRM 2015 - Microsoft Dynamics CRM Community

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

%d bloggers like this: