Dynamics CRM Calculation & Rollup Fields

Introduction

After completing my blog on Dynamics CRM Calculation Fields I was asked by JJ Scholtz, friend and fellow Dynamics Enthusiast to write on Calculation fields vs Rollup Fields.

Calculated Fields

Calculated fields are virtual fields meaning that the actual result of the calculation does not get saved in the database column, but it is still available on all the Forms, Views and even when selecting data from the SQL views using TSQL. If you navigate to your Dynamics database and view the calculate field that you created you will see that the field type is ComputedA computed field has a expression that can perform calculations using other columns in the same table. The expression can use noncomputed columns, constant, function or a combination.

If you view the properties of your computed column in SQL you will see in the Computed text property contains the name of a function created by Dynamics with a SQL version of the formula defined in CRM when the calculated field was created.

 

Things to keep in mind when using calculated fields:

  • Updates to these fields won’t trigger workflow updates
  • Fields are only available for retrieve in plugins and are not available as part of create or update plugin context
  • Fields are virtual and not stored at the database level
  • You cannot mix and/or statements.  Can only use all OR’s or all AND’s
  • Can only go up on level to access data on N:1 relationships

Rollup Fields

Rollup fields are actually saved in the database and are not virtual fields like we have seen with the calculated fields. Rollup up fields are meant to be aggregate calculations of child entities. For example Total value of all line items on quote or invoice.

The UI for creating a rollup field is almost the same as for calculated fields except on rollup fields we have a section for Related Entities used to rollup values on child records.

Rollup fields are stored in the RollupPropertiesBase table in SQL. OOB an Async job gets executed every hour to recalculate the rollup field. Users can also force the recalculation process if the rollup field exist on the form.When users hover over the field they will see a refresh icon.

Things to keep in mind when using rollup fields:

  • Roolup field calculation does not get preformed in real time
  • Value is stored in the database as a actual field
  • Recalculating a rollup field will not trigger the update plugin context.