How to: roll up without limitations

By | August 15, 2017

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:

image

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:

image

– 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:

image

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

image

2. I need a lookup configuration record

image

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

image

image

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

image

And that’s it. It is time to roll up!

rollup

Happy 365-ing!

11 thoughts on “How to: roll up without limitations

  1. Joey

    Hello Alex! Great article! I have a need to create dozens of rollup fields counting up records related to the Account entity. However, I’m having a problem getting the FetchXML to work. I took your example and modified it to count the number of notes associated with an Account and I’m getting an Invalid XML error when using it. Can you see what I’m doing wrong? Thanks!

    Reply
  2. Jil

    I’m getting ‘opportunity With Id = 00000000-0000-0000-0000-000000000000 Does Not Exist’. I’ve checked and i did specify the correct guid. Please help.

    Reply
    1. Alex Shlega Post author

      Hi Jil, not sure – I’d need to see tcs configuration entity settings(including fetchXml) and, also, a screenshot of the workflow triggers/steps.. If you send it to [email protected], I’ll have a look

      Reply
  3. Muzahid Khan

    Nice solution, I’ve used this in 9.0 org and it’s working smoothly.

    Reply
  4. Ben

    Great article. How would you rollup a large dataset > 50000 rows, that being a limitation of fetchxml aggregate.

    Reply
    1. Alex Shlega Post author

      Hi Ben,

      I think you would have to split your data so you would rollup parts of it, and, then, sum up the results. You could possibly do it using createdon date.

      Also, there is, probably, a better way to do it these days since you can use Microsoft Flow (even though there would still be a limitation on the total # of rows in Fetch)

      Reply
  5. Colin

    Hi Alex,

    Any idea why or what i need todo to stop this error im getting?

    Error setting attribute value for new_fieldnamecount. Error converting attribute value to Property: Attribute [new_fieldnamecount], Attribute type [lookup] of Entity [new_entity] with value of type [System.String]: [System.InvalidCastException: Unable to cast object of type ‘System.String’ to type ‘Microsoft.Xrm.Sdk.EntityReference’. at Microsoft.Crm.BusinessEntities.LookupPropertyConverter.InternalFromEntity(ICrmConversionContext conversionContext, AttributeMetadata attributeMetadata, Object value, String formattedValue) at Microsoft.Crm.BusinessEntities.PropertyConverterBase.c__DisplayClass7_0.b__0() at Microsoft.Crm.BusinessEntities.PropertyConverterBase.InternalConvertFrom[T](Guid orgId, AttributeMetadata attributeMetadata, Object value, Func`1 converterFunction)]

    Reply
    1. Alex Shlega Post author

      Hi Colin,

      it seems there is a data type mismatch somewhere. If you could send me an email with the screenshot of how you are trying to set it all up, that might help ([email protected])

      Reply
  6. Stephan Jenner

    Ok looks like what I need I want to update a rentprice field for a tenancy from a rentprice child table that uses effective from so the rollup would select the row that is the highest date past today’s date and select the amount and populate the field is this possible with this scenario?

    Reply
  7. Boyd Moon

    Great solution Alex, I’ve used it for several rollups avoiding the 10 per entity limitation. Is there a way to get around the N:N limitation if I need to count child records?

    Reply

Leave a Reply to Andrei Cancel reply

Your email address will not be published. Required fields are marked *