In today's post I will show how to set up a recurring process in Dynamics CRM that executes a FetchXML query to return a set of records and then starts a workflow for each of those records without requiring any external processes or tools. This is a generalized approach to solving a class of problems that includes the following scenarios:
- The birthday greetings problem: How can you, on a daily basis, send an e-mail to every contact with a birthday == today (where the date value for today is obviously different every day)?
- The monthly update problem: How can you, on a monthly basis, generate an activity for every account with status reason == X (where it's important that the process only runs on a certain day of the month based on status reason values as of that exact date)?
While you could accomplish both of these using workflows with timeouts, I think that would be a bad approach. In the case of the birthday greetings problem, I don't like to have workflows in a waiting state for an entire year. For the monthly update problem, a timeout-based approach could get extremely complex. Another more general issue with waiting workflows is that business rules can change, so if you have thousands of workflows in a waiting state and the business owner says she wants to update the process to do X instead of Y, it can be a serious pain to cancel the waiting workflows, update the definition and then restart them. (And yes, I speak from personal, painful experience.)
My solution requires three things:
- A custom workflow activity (StartScheduledWorkflows) that can execute a supplied FetchXML query and initiate the workflow for each retrieved record.
- A custom entity (Scheduled Process) to hold the FetchXML query and scheduling details.
- A workflow (Scheduled Workflow Runner) to run the StartScheduledWorkflows activity on a recurring schedule.
How it works
A Scheduled Process record is created, which starts a corresponding Scheduled Workflow Runner workflow in a timeout state. When the next run date == the current time, the Scheduled Workflow Runner workflow initiates the StartScheduledWorkflows activity with the FetchXML query and workflow lookup from the Scheduled Process record. The StartScheduledWorkflows activity advances the next run date of the Scheduled Process record, and then it executes the FetchXML, loops through the results and starts the workflow from the lookup for each record. A newly started Scheduled Workflow Runner workflow then waits for the next run date to start the process again. More...