Rollup fields in Dynamics are great, they are very useful, they are there out of the box, and a lot has been written about them.
Although, if you wanted to start somewhere, the page below would give you all the details:
That said, there is a bunch of things you can’t do with the rollups:
This list is rather long, so, if you’ve been using rollup fields on your Dynamics implementations, you have probably run into some of those limitations.
I would add two more that came up recently in the community forums:
– You cannot use a rollup to calculate the number of notes associated with a particular case (it’s probably the same with any other entity – can’t rollup over notes). No way to select notes below:
– You cannot create rollup fields on some entities (for example, there is a “Characteristic” entity which, somehow, does not allow rollup fields). There is just no rollup option:
So when this happens, when we run into a limitation, what can we do?
I wanted to show you how to do it with TCS Tools, though, since I just updated that solution to better support all those rollup scenarios. In a nutshell, all you need to do to define a custom rollup is:
So, let’s say we wanted to get the number of notes per case. In one the earlier posts, I already described how we can use “Lookup Setter” custom workflow activity to set a lookup value:
In the most recent version of the TCS Tools, that custom workflow activity has been renamed to “Attribute Setter” to better reflect its capabilities.
Here is how I am going to use it to count those notes:
Why do I need two workflows? Because I want to do those calculates “on delete” of the note records, too. But, if I make it a real-time workflow, it will run before note record is deleted. So the note being deleted will still be counted, and I don’t want that to happen.
And, btw, if you wanted to know more about FetchXml aggregations, I would suggest this link:
Let’s get ready:
1. I need a new attribute on the case entity
2. I need a lookup configuration record
Here is what all those attributes mean:
“Name”: just a name. I’ll use it when setting up the workflow later
“Fetch Result Attribute”: where the result will be once my fetch xml is executed
“Entity Attribute”: this is the name of the attribute where the result will be stored
“Update Direction”: Entity. I want to update case entity, not all the records which will be retrieved by that fetch xml
For your reference, here is my fetchxml:
<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>
<attribute name=’annotationid’ aggregate=’count’ alias=’count’/>
<condition attribute=’objectid’ operator=’eq’ value=’#incidentid#’ />
Notice how I’m using #incidentid# there. This allows me to choose only those notes which are associated with the case for which my workflow will be running.
3. I need to create a workflow on the case entity
Again, that’s a child process workflow.. I’m using Attribute Setter workflow activity.. And I’ve set up that activity to use my Notes Count lookup configuration from step #2.
Almost there, but I still need to call that workflow somehow
4. I need a workflow on the note entity
In this workflow, I will check if that notes record is related to a case, and, if yes, it will start my child process workflow from step #3
And that’s it. It is time to roll up!