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!
Andrew Bibby (@AndrewBibby)
Another well-written post full of useful information – thank you, Jukka!
Good post Jukka. We have been discussing the rollup fields and the performance impact of the “recalculate” button on the rollup fields especially in large orgs. Let’s say, for instance that you have an org with 2000+ users and have added some fields to quoteproduct that you want to summarize on quote and people are pressing these constantly, or even worse, added a “activity score” field on appointment and then roll these up to user, to get a Customer-meeting-score. And due to the fact that users are uncertain of the validity of the data, or perhaps triggered with a plugin (in extreme scenarios, on retrieve) – I do Believe that the performance impact might be hight and this is the reason why it is not implemented with live updating. It is of course just speculation. Your thoughts on this?
Also, I Think I saw that it was possible to create calculated fields based on rollup fields, would that be a workaround for triggering workflows or can the not be used for triggering either? I havn’t tried myself.
Thanks, Gustaf! Regarding the performance impact, I do kind of understand why the real-time calculation of rollup fields has only been enabled as an SDK message rather than GUI level configuration item. Unless the system customizer understands what they are doing when defining a new rollup fields (amount of records, frequency of use) then there is a real danger that system performance could degrade as a result of this. Then again, there are also many obvious scenarios where the real-time updates on the records that the user is working on (quote & quote products is a good example) is a requirement for the rollup feature to make any sense at all (imagine a quote total that updates 30 minutes after the edits).
In general the problem with this type of features is that when Microsoft makes them available in the platform, they’ll always need to ensure that it can “scale to the millions”. Many neat UX enhancements that would make the product easier to use in 90% of the scenarios where Dynamics CRM users work with could potentially cause performance issues in 1% of CRM deployments. If it’s a “click to configure” type of a feature then there’s always far more safeguards needed than if you make it available to developers only. I see these recent enhancements in the platform that enable business analysts to create more complex applications without writing custom code as being sort of “on the fence” when it comes to this kind of scalability questions. If there was an option in the rollup field configuration steps to “update this in real-time” then would it be reasonable to assume that the responsibility of the resulting system performance impact is on the system customizer who enables that feature? I would say yes, but from a product management perspective the question may be much more complex.
As for the use of calculated fields to circumvent the limitations of rollup fields in workflows, I’m not sure if this would work. Calculated fields are calculated in real-time when they are retrieved, so their values do not persist in the database, which I think would be a requirement for the workflows. I’ll investigate this once I do the promised follow-up post to this one, though!
Francis de Briey
First thanks a lot for this very clear post, quality of posts is not always the best but yours allowed me to quiclky evaluate the use of Roollup fields for the CRM I’m working on.
I will evaluate the the solution of Demian Rasko, especially on the workload side and get back to this post if I get interesting results.
Francis de Briey
you forgot to mention there is a maximum of 10 rollup fields allowed per entity – grrrrrrrrrrrr
That and other limitations are actually included in the MSDN article which I’ve linked to at the start of my first blog post on Rollup Fields. Always a good idea to start from the official MS documentation when it comes to exploring new CRM features!
Hi Jucca! About the Demian’s solution, I’m not sure of what exactly is “Parent record URL”. My scenario consists in creating a rollup field in Opportunity that makes the sum of quantity field of relative OpportunityProduct lines. And another Rollup field in Account that indicates the total quantity sold at that account through all the associated opportunities.
So I create the opportunity.rollupfield1 that takes values form OpportunityProduct lines.
Then I create a workflow that, when the Opportunity Status changes to win, copies this value in a opportunity.simplefield1.
And in Account I create an account.rollupfield1 that takes value from opportunities opportunity.simplefields1,2,3…
The problem is timing! If the Opportunity Status changes before the rollup calculation is run, the value copied by workflow is too old and the account.rollupfield1 is not well updated!
This is why I’m looking for a solution that makes me able to trig the rollup calculation in real time.
Thanks in advance for your help!
Alessandro, the Parent Record URL is a refence to the record on which the Rollup Field resides in. As you will trigger the workflow from the child record, you need to tell Demian’s custom workflow activity on which entity and in which Rollup Field the value should be refreshed. So, in your case the parameter “FieldName” would be rollupfield1 and the “Parent Record URL” would be a value found from the list of fields for the regarding parental opportunity record. Demian’s solution documentation on CodePlex includes a screenshot of how you can retrieve this value in the workflow editor.
Jukka, thanks a lot for your answer. I’ve followed Demian’s instructions but something locked the processes queue, an unidentified “System event”. So I’ve removed the rollup plugin assembly and I’ve downloaded and imported the other Demian’s solution: Dynamics CRM 2015 WorkflowTools https://msdyncrmworkflowtools.codeplex.com/ . With this one everything works fine! So I think that the first solution is discontinued because the same feature is included in WorkflowTools.
Thanks for your passion Jukka, you’re a lighthouse in the Dynamics CRM sea! 🙂
Jukka, very informative article! I was able to use the workflow created by Damian Rasko that would force-update specific accounts I chose.
I do have a question though. I currently have a Roll-up field created, that generates a date every time an activity is created for an account. However, not all Roll-up fields are being updated by the System Job that is scheduled every night. Would I be able create a Workflow that, when an activity is created, Damian’s workflow would trigger and force the roll-up field to update? My goal is to avoid relying on the nightly System Job, as it currently doesn’t seem to be running for all accounts.
Peter, I don’t see a problem with creating your own workflow process to trigger on activity create, then check if it is associated to an account and then running Damian’s custom workflow activity. However, it sounds a bit strange that some records would not be getting picked up by the system job which runs every hour (only first run is with 12h delay, unless you manually change this). If you’re running your instance in MS cloud then it might be a good idea to open a support ticket to investigate this issue further.