In an earlier blog post in December, I described one use case for the new Rollup Fields feature introduced in Microsoft Dynamics CRM 2015. This example involved rolling up data from email events tracked via ClickDimensions and summarizing this on the contact’s form, so you’ll want to check out the steps listed there if you don’t have any hands-on experience about this new feature yet. In this post I’m going to dig deeper into the details about how Rollup Fields actually work behind the scenes and what limitations you should be aware of when considering whether they are the right tool for the job in your own use cases.
The first thing you need to understand about Rollup Fields is that they are not updated in real time. If you’re familiar with the difference between the real time workflows introduced in CRM 2013 and the asynchronous versions that were available in earlier versions, then this is something a bit like that, but not quite. As you might know, the traditional background workflows were triggered by an event that took place on a CRM record and the resulting workflow instance was scheduled to be executed by the asynchronous process running on the CRM server at the earliest possible date (depending on the overall workload on the server). Whereas this usually meant a delay of perhaps a minute or two at most, the new Rollup Fields are even further from real time than this.
As we saw in my earlier post, when you create a new Rollup Field, a new mass calculation job will be created for the field in question. This will be scheduled 12 hours into the future, based on the assumption that this will most likely fall outside the office hours when actual CRM end users are working with the system. (Because us CRM customizers or system admins never work during the night, right? Yeah, what a funny assumption that is, but anyway…) The reason for such precaution is that the very first calculation job will have to populate each and every record that exists for that entity, which could be up into the millions, depending on what type of data you manage in your CRM.
So, does this mean the Rollup Fields only get updated once per day, during that nocturnal schedule? No, actually they get updated once every hour. If you go to the Settings – System Jobs menu you’ll see that there are jobs of type “calculate rollup field” type running for each of your entities that have one or more Rollup Fields defined for them. They are not scheduled to start at exactly the same time, but they all run at one hour intervals. Another thing worth noting in the Rollup Field implementation architecture is that these calculation jobs are only applied to records that were created, updated or deleted after the last job finished. No point in processing a million records if only a handful of them could possibly have new values to be calculated, right? This is why the initial rollup and the recurring rollup requests are handled by different system jobs in the CRM platform.
Rollups and Workflows
Now that we know the Rollup Fields may not show a current values in the UI for quite some time, the next logical question to ask is: anything we can do to speed the calculations up? As an end user, you could go and look at any Rollup Fields that have been added onto an entity form which you have the necessary rights to view, then hover over the field and click the “recycle” icon to force the recalculation of the Rollup Field value. As a developer, you also have the option to force a Rollup Field to be recalculated on demand via a plugin, by using the CalculateRollupField message. As a system customizer… Well, there’s not much you can do, at least in the CRM 2015 version.
No standard workflow or Business Rule will allow you to initiate the Rollup Field recalculation based on the custom business logic you’d like to define. What this means is that Rollup Fields in the current application version are not really the ultimate replacement for custom code in typical scenarios where you’d wish to show the number of child records on a parent record field. For example, showing the number of attendee registrations for an event (when built as an XRM solution with custom entities for event & attendee, with a 1:N relationship between them) wouldn’t really work with Rollup Fields alone, as the number might have been calculated almost one hour ago and the event might thus already be fully booked by the time the user needs to provide this information to a customer or manually create new attendee records under the event record.
Luckily Dynamics CRM is an extensible platform with an active user community that can often produce solutions for filling the functional gaps that may exist in the core product. In only a couple of months time after the release of CRM 2015 there was already an open source solution available on CodePlex that allows the on demand update of Rollup Fields via a custom workflow activity. The Dynamics CRM 2015 Calculate Rollup Field (Workflow Activity) solution by CRM MVP Demian Rasko essentially provides a no-code shortcut for calling the aforementioned CalculateRollupField message in the platform via a step in a workflow rule. Now, since workflows can be true real-time actions, this also allows the Rollup Field calculation to occur immediately. Of course since these workflows are something that you’ll configure into your own CRM environment, you’ll need to assume the responsibility of any performance impact that the rollup calculations will have (and also triggering the workflow for all the required events, but that’s the topic of a whole blog post of its own).
Rollups and Business Logic
The workflow discussion has lead us nicely into the last but definitely not least important topic you need to understand about Rollup Fields. Now that we have the ability to perform calculations such as count of child entity records for the parent record, let’s do a critical analysis on what new scenarios this feature actually unlocks for us.
Let’s start by looking at what tools we had at our disposal before CRM 2015. If we wanted to see the count of child records or a sum of numerical field values from these records, there was the possibility of leveraging charts embedded on entity forms. As an example, if we were looking at an event record form, there could be a subgrid of the related attendee records shown in there. Instead of defining the subgrid as a list of records, we’d simply tick the box of “show chart only” in the subgrid definition. Then we’d pick a simple enough chart that would only contain a single category at any given time and, voilá, we have the total count/sum of child records available on the form.
“That’s cute, but having a field with that value is just sooo much better.” Fair enough, you want to show a field with a number, and from a presentation perspective that’s perfectly justifiable, since the charts really do introduce a whole lot of unnecessary whitespace on the form and are not a very efficient way to use up the screen estate. But remember this: the chart will always show the actual figure, calculated in real time, whereas the Rollup Field value can only be guaranteed to be valid if A) no one touched the data for the past hour or B) you’ve built a custom process to refresh the Rollup Field on all events that may alter the actual value.
“Mmm, okay, but with a field value stored on the entity, we can actually use this data to drive our business process automation. Try that with your pretty chart!” Well, your assumption there is perfectly justifiable, since traditionally any field value you have on an entity attribute could have been used to trigger workflows that evaluate various conditions and automatically perform follow-up actions if these are met. However, this is where Rollup Fields are a whole new generation of attributes that doesn’t want to play by the old rules anymore. Specifically, a Rollup Field cannot trigger a workflow process.
Yes, you heard that right. When the value for the Rollup Field changes, you’ll have to go and look it up yourself, since a workflow cannot be defined to run automatically based on this update event. While this won’t be such a big issue for fields that are just informative in nature, it does make it difficult to use the Rollup Field data in scenarios that are more transactional, where you’d want to conditionally perform an action based on the field value. For example, if we were to manage a custom lead score field where a certain number of actions (web page views, for example) tracked against the lead should trigger an activity to be created for a sales rep to contact the lead directly, this action could not be configured occur based on the score field if it’s a Rollup Field. Another example might be that you want close off an event as “maximum capacity reached” when the number of attendees signed up for it reaches a predefined threshold. Nope, that’s not going to work via a Rollup Field either.
So, despite of the undeniable benefits that built-in Rollup Fields in CRM 2015 offer us, there are many situations where a simple rollup won’t be enough to meet the business requirements. What are our options then? Well, aside from custom plugins, there are cases where the creative use of workflow processes can help to achieve the required results. In other scenarios it might be more convenient to combine the Rollup Fields with some scheduled bulk processes that carry the actual business process forward. That’s a topic which I’m planning to explore in my next post on CRM 2015 Rollup Field functionality. Stay tuned!