How to: roll up without limitations

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:

https://technet.microsoft.com/en-us/library/dn832162.aspx

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?

• We can develop a plugin
• We can develop a custom workflow activity
• We can use javascripts
• And there are, probably, other options as well

 

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:

• Create FetchXml to query the data
• Create a workflow that will use that FetchXml to update a field

 

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:

http://www.itaintboring.com/dynamics-crm/dynamics-crm-use-a-workflow-to-set-a-lookup/

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:

• I will create a whole number field on the case entity (Notes Count)
• I will create a Lookup Configuration record to define fetch xml which will use aggregation to get the count of notes per case
• Then I will create a workflow on the case entity (a  “child” workflow) which will use “Attribute Setter” custom workflow activity in combination with the fetch xml defined above to calculate notes count and to set “Notes Count” field. This will be a background workflow
• And, finally, I will create a workflow on the notes entity which will be triggered on create/on delete of the note records and which will be calling that workflow on the case entity as a child workflow. This will be a real-time workflow

 

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:

https://msdn.microsoft.com/en-us/library/gg309565.aspx

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’>
<entity name=’annotation’>
<attribute name=’annotationid’ aggregate=’count’ alias=’count’/>
<filter type=’and’>
<condition attribute=’objectid’ operator=’eq’ value=’#incidentid#’ />
</filter>
</entity>
</fetch>

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!

Happy 365-ing!