The Good and the Bad With Calculated and Rollup Fields in Dynamics CRM
So I am sure if you have been working with Dynamics CRM Online for any length of time you have probably at least heard of Calculated Fields and Rollup Fields. To date I have found some great uses for these, but I have also run into some limitations. I am sure they will continue to improve with further updates to the product, but I would like to take a little bit of time and go through the upsides and also the limitations of these two features.
Calculated Fields
Pros
- Ability to populate a field with simple calculations based on other values and conditions on the form in a real time manner. This alleviates the need for JavaScript in fairly straight forward scenarios such as adding days or months to a date, or adding or subtracting fields on the same entity.
Cons
- Missing the kind of complexity you get from Excel formulas
- Example: There is no "MAX" function that you can use with dates to get the max of more than one datetime field.
- 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 plugins
- Fields are virtual and not stored at the database level
- Many times they are un-sortable in views unless you use all simple field types in your calculation
- Not available offline with the Outlook Client
- You cannot mix and/or statements. Can only use all OR's or all AND's
Rollup Fields
Pros
- Allows aggregation of data from related child entities up to a parent entity. This allows for some aggregation scenarios that might have previously required the use of custom code in workflow, plugin, or complex JavaScript to be done much more easily.
- Counts, Max, Min, and Sum
- Stored in the database as an actual field
- You can trigger this via the API in code manually and as an administrator you can postpone rollup calculations from settings.
Cons
- Not real time
- Currently the default is every 12 hours by default.
- Not good for on-demand reporting needs
- Rollup re-calculations will not trigger a modify call at the platform level and there is no other good plugin hook to use besides registering a plugin on the related child entities that are part of the recalculation.
- Example: To fire a plugin on a change in open revenue, which would be a rollup field on account from estimated revenue on open opportunities, you could not register your plugin on update of Account based on the recalculation of the rollup field. You would have to instead attach your plugin to the creation, state-change, update, and delete of an opportunity. And in the plugin you would have to manually call the rollup re-calculation via an API call to get the new value into the plugin. This would be a hassle due to the many points you would have to register the plugin and also the fact you need to perform secondary manual API calls to get the data when you need it.
In summary, there are definitely scenarios where rollup field and calculated fields shine and can save you a lot of time writing .NET or JavaScript code. Just be aware that it is a relatively new feature that only came out with the 2015 release of Dynamics CRM and it does have some serious limitations that can lead you to paint yourself into a corner and force you to change your methodology mid-stream if you are not aware. I am sure they will continue to improve these with future releases of CRM and it is definitely a welcome addition to the core product.
