How-To: Overcome Rollup Field Limitations with Rolling BatchProcessing.. Even In the Cloud

Rollup fields are great. But their filters are limited. The most common use case I can imagine is something like year to date sales. But you can’t do that with rollup fields because the filters don’t have relative dates. Want quarter-to-date sales? Sorry folks.

Here’s how to make it happen. This works.. even in the cloud. It should also work in 2011 although I have not tested it there.

In this scenario, we are using a Connection between a Product and an Account to hold quarter to dates sales numbers. I want to regularly calculate the sum of Invoices against the Account for the given Product and save it on the Connection. I’m calling the Connection my “target entity.”

Overcoming the Depth Property and Timeouts

Normally, you could create a recursive workflow that processes something, then calls itself. But you run into a problem with the Depth property; CRM will stop the process after so many iterations and consider it a runaway. So if your workflow calls itself, the 2nd time it runs the Depth property will be 2. After so many times, if Depth = x (I think 15 by default), CRM will kill the process.

The secret comes from Gonzalo Ruiz: the Depth property is cleared after 60 minutes.

The other issue is CRM’s timeouts; you need to make sure the update process doesn’t croak because it’s chugging through too many records.

So we’re going to chunk up our data into 1000 batches and run each batch asynchronously every 61 minutes. A lot of processes doing a little bit of work each. I don’t recommend this with synchronous processing.

The Approach

Here’s the process we’re going to create.
  1. Upon create, assign your target entity a random batch number. I’m using 1000 batches.
  2. An instance of a custom entity contains a batch number (1000 batch controller records for 1000 batches). A workflow fires on this custom entity record every 61 minutes.
  3. The workflow contains a custom workflow activity that updates all target records in its batch with a random number in a “trigger” field.
  4. A plugin against your target entity will listen for the trigger and fire the recalc.

First, create a custom entity. I’ve called mine rh_rollingcalculationtrigger. All you need on it is one integer field.

Now, on your Connection (or whatever entity you want to store the rolling calculated fields), create two fields: rh_systemfieldrecalculationtrigger, and rh_systemfieldrecalculationbatch.

Now create a simple plugin to set the batch number to between 0-999 when the record is created. If you have existing records, you can export to Excel and reimport them with a random batch assignment – the Excel formula randbetween() is great for this.

(Side note: In C#, Random.Next() returns a value exclusive of the upper bound. So each record will get a value 0-999 inclusive.)

Now, we create a custom workflow activity. This inputs the batch number and fires a process called FireBatch. So when this workflow runs on a rh_rollingcalculationtrigger entity with Batch ID = 5, it will call FireBatch against all records with batch ID = 5.

In my case, I like to assemble the service, context and tracing service in the plugin and call my own class.

FireBatch: query all records with batchID = x and update them with a random number.

Warning: use ExecuteMultiple to avoid timeouts.
Now, create a plugin against the Connection to do whatever it is you want. It should fire on change of the rh_systemfieldrecalculationtrigger field.

The final piece is to, well, get it rolling. First, create 1000 instances of rh_rollingcalculationtrigger, setting Batch ID’s 0-999.

Remember, we can create a recursive workflow with the 60 minute workaround. I’m setting it to 61 just to be safe.

Manually fire the workflow once on each of your 1000 recalculation entities. Congratulations, you have perpetual recalculation.

I recommend setting up bulk deletion jobs to remove the system job records this creates. It can be a lot.