Monitoring Rollup Field Values with Workflows

In an earlier post I demonstrated how you could leverage the new Rollup Fields feature of Dynamics CRM 2015 to summarize the behavior of your customers and produce interesting metrics that could be used for targeting your sales activities towards the most active individuals who have reacted to your email and website content. The example included using data collected by ClickDimensions on the clicks on email marketing message links and page views on sites that contain the visitor tracking script. With the help of Rollup Fields and this marketing automation data stored directly into Dynamics CRM database under the contact records we were able to add the following custom fields onto our contact entity form:

• Latest Email Link Click
• Total Number of Link Clicks
• Latest Page View
• Total Number of Page Views

While this allowed us to create a nice, sorted view of the contacts who’ve been interacting with our online content most recently, this information is still just data sitting in the CRM system, waiting for the users to go and discover it. Wouldn’t it be great if we could actually build an automated business process around it and make sure that the owners of these contacts who are clicking the links and visiting our website would be notified about these events? Sounds like something that a CRM workflow could help us with, right?

Unfortunately there are a few limitations when it comes to the new Rollup Fields in CRM 2015, as we discovered in my previous article. For example, if we would like to trigger a workflow process instance whenever the number of Link Clicks goes above a certain threshold, this isn’t something we can do directly by tapping onto the onChange event of the Total Number of Link Clicks field, because Rollup Fields cannot be used to trigger a workflow.

Ok, what if we lower our expectations a bit and don’t even attempt to perform these actions in real-time for each event? It might be perfectly acceptable from a business perspective to have a process run once every night, inspect which contacts had clicked on the tracked links and then send out notifications to the record owners in one go. Surely that’s something Dynamics CRM can do, right? Well, let’s see if we could put together a solution like this.

Batch Processing CRM Data with Workflows

It’s quite a common requirement to perform checks or updates to CRM records based on a predetermined schedule. Although you can use workflow processes to be triggered on an event that takes place on CRM records (as long as it’s not on a special attribute like a Rollup Field), there isn’t actually any ready-made feature available in the CRM platform that would allow you to schedule the workflows to run every X hours, every night, once a week etc.

The traditional approach for meeting such requirements for scheduled updates would have been to develop a small custom service to run on the CRM server machine. If you had access to other systems with interfaces to CRM like SQL Server Integration Services (SSIS) then these could naturally be also leveraged here. In the brave new cloud era where the number of Windows servers at your disposal for running these type of applications is rapidly decreasing it’s sometimes challenging to find a place where such schedulers could be deployed to, for performing small batch jobs that your CRM business processes would require. Wouldn’t it be convenient if you could build all of this by using just CRM Online and nothing more?

Get ready for the good news: yes, you can schedule a recurring workflow to handle batch updates within Dynamics CRM, no external servers needed. The one thing you need, though, is a clever little custom workflow activity to extend the standard features of the CRM workflow engine. In this example we’ll use the Scheduling recurring Dynamics CRM workflows with FetchXML solution developed by Lucas Alexander. What this solution does is it gives us the possibility to:

• Determine a query criteria for the bulk job
• Schedule this job to be run hourly/daily/weekly
• Run our own workflow process for all records returned by the query

The above diagram by Lucas outlines the logic behind the solution, but you really should go and read his blog post on the details of this approach, or check out the source code for the custom workflow activity on MSDN code gallery if you really want to dig deeper into the topic. For the purpose of today’s scenario, I’ll show you how I’ve used the solution in conjunction with the aforementioned Rollup Fields.

Sending Out Notification Emails Based on Rollup Field Data

In my example I’ve determined that it’s the Latest Page View datetime field on a contact that should be driving the business process. Once a week I would like to notify the owners of customer contact records if the contact has visited our website during the last 7 days. Turning this into a query criteria, it would mean that during the time of each batch processing I’d want to retrieve all contacts where the Rollup Field for Latest Page View contains a value greater than today minus 7 days.

I will need to turn that query definition into a language that Dynamics CRM understands, and that is FetchXML. Sounds a bit tricky, eh? Lucky for us, CRM comes with a nifty lil’ FetchXML generator called Advanced Find, which I’m sure you’re already familiar with. All we need to do is find Advanced Find, specify the aforementioned query criteria for the contact entity and then click the “Download Fetch XML” button in the ribbon to grab the XML text into Notepad.

Next we should think about what action we want to perform on the contacts who match the query criteria. In this scenario it will be the sending of an email message to the owner of the contact. It only needs to be available as an on-demand process and there isn’t even a requirement to have any query criteria enforced here, but I added the 7 day rule here as well, just in case I end up using the same process in some other scenario.

The final step is to create a record for the new Scheduled Process entity, which has been added to CRM in the solution packaged developed by Lucas. On this form I’ll first give a descriptive name to the Scheduled Process and then define it to be related to the contact entity. In the Workflow lookup field I’ll pick the workflow process you see above, and for the Query field I’ll paste in the FetchXML we grabbed from our Advanced Find.

All that’s left for us to do is setting the actual schedule for this process. The weekly option in the dropdown menu suits our purpose best in this scenario. By adjusting the Next Run Date I can configure the email notifications to go out in the morning, so that they reach the inbox at an optimal time.

Alright, that concludes our scenario for using CRM 2015 Rollup Fields together with workflow processes to deliver actionable insights to our CRM users on how customers are responding to our marketing activities. What I personally find very interesting in this example is the ability to take a piece of existing data that’s sitting inside our CRM database and first turn it into a metric that’s easily viewable in the CRM UI, then further amplify its business impact by configuring conditional processes to deliver it as a notification to the user who should become aware of it.

None of this required a huge technical development effort or investments into separate reporting systems. All we needed was to take a new feature that was added to the Dynamics CRM platform as a part of the 2015 version rollout (this being a CRM Online environment there wasn’t even an upgrade project to worry about), combine that with an excellent open source enhancement to the platform’s workflow functionality, then just design a solution that delivers new business value from existing data. If you look at your own Dynamics CRM system and the data that’s being collected into it as a part of your business processes, then I’m pretty sure you could also identify potential use cases for similar type of enhancements, built with CRM’s ever evolving process automation toolkit.