Scheduling recurring Dynamics CRM workflows with FetchXML

by Lucas Alexander

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:

  1. 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)?
  2. 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:

  1. A custom workflow activity (StartScheduledWorkflows) that can execute a supplied FetchXML query and initiate the workflow for each retrieved record.
  2. A custom entity (Scheduled Process) to hold the FetchXML query and scheduling details.
  3. 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.

Creating the custom workflow activity

The custom workflow activity is easy to implement. As described above, a FetchXML query and a workflow lookup are supplied as input parameters, and then the workflow is started for each record returned by the FetchXML query. Here's the code for my StartScheduledWorkflows class.

public sealed class StartScheduledWorkflows : CodeActivity  
{  
[Input("FetchXML query")]  
public InArgument<String> FetchXMLQuery { get; set; }  
  
[Input("Workflow")]  
[ReferenceTarget("workflow")]  
public InArgument<EntityReference> Workflow { get; set; }  
  
//name of your custom workflow activity for tracing/error logging  
private string _activityName = "RunDailyProcess";  
  
/// <summary>  
/// Executes the workflow activity.  
/// </summary>  
/// <param name="executionContext">The execution context.</param>  
protected override void Execute(CodeActivityContext executionContext)  
{  
// Create the tracing service  
ITracingService tracingService = executionContext.GetExtension<ITracingService>();  
  
if (tracingService == null)  
{  
throw new InvalidPluginExecutionException("Failed to retrieve tracing service.");  
}  
  
tracingService.Trace("Entered " + _activityName + ".Execute(), Activity Instance Id: {0}, Workflow Instance Id: {1}",  
executionContext.ActivityInstanceId,  
executionContext.WorkflowInstanceId);  
  
// Create the context  
IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();  
  
if (context == null)  
{  
throw new InvalidPluginExecutionException("Failed to retrieve workflow context.");  
}  
  
tracingService.Trace(_activityName + ".Execute(), Correlation Id: {0}, Initiating User: {1}",  
context.CorrelationId,  
context.InitiatingUserId);  
  
IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();  
IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);  
  
try  
{  
EntityCollection recordsToProcess = service.RetrieveMultiple(new FetchExpression(FetchXMLQuery.Get(executionContext)));  
recordsToProcess.Entities.ToList().ForEach(a =>  
{  
ExecuteWorkflowRequest request = new ExecuteWorkflowRequest  
{  
EntityId = a.Id,  
WorkflowId = (Workflow.Get(executionContext)).Id  
};  
  
service.Execute(request); //run the workflow  
});  
}  
catch (FaultException<OrganizationServiceFault> e)  
{  
tracingService.Trace("Exception: {0}", e.ToString());  
  
// Handle the exception.  
throw;  
}  
catch (Exception e)  
{  
tracingService.Trace("Exception: {0}", e.ToString());  
throw;  
}  
  
tracingService.Trace("Exiting " + _activityName + ".Execute(), Correlation Id: {0}", context.CorrelationId);  
}  
}

Creating the custom entity

To run the scheduled processes, we will need a custom entity called Scheduled Process to hold at least four pieces of data:

  1. The FetchXML query to execute
  2. The workflow that will be started for the retrieved records
  3. The next run date/time on which the process should be executed
  4. How often the job should be run (frequency)

In addition to those, I also have fields to hold the relevant entity name and the last run date. Neither of these are required for my approach to work, but they make managing the processes easier if you have a lot. Here's a screenshot of a Scheduled Process record:

Creating the scheduling workflow

Finally we need a Scheduled Workflow Runner workflow that will manage the execution of each scheduled process record. It should look like the following:

The workflow runs when the next run date value of Scheduled Process changes. Once we reach the next run date for a Scheduled Process, the workflow updates the last run date to the process execution time and the next run date to the process execution based on the Scheduled Process frequency. There are three "loop" conditional branches to handle this, so if the frequency is "hourly," then the next run date is set to the process execution time + one hour. You can modify your frequency values on the the Scheduled Process record, but you'll have to add a separate conditional check in the workflow for each one. Alternatively you could use another custom workflow activity to intelligently handle different frequency values, but I wanted to keep the coding to a minimum for this example.

Because next run date is based on when the Scheduled Workflow Runner actually executes, your next run date times may end up skewing over time. That is, if you start a Scheduled Process running at 6:00 a.m., by the fifth iteration, you might end up with 11:05 a.m. instead of 11:00 a.m. For this reason, I suggest you have a process in place to occasionally check these values and reset them as needed. Potential automated solutions include using another Scheduled Process to fix the time skew, or custom code in a plug-in or custom workflow activity to re-baseline the time. If anyone has any other suggestions, please share them in the comments.

After the updates are made, the workflow executes the StartScheduledWorkflows custom workflow activity to start the workflows for the relevant records, and you're done.

Putting it all together

I've uploaded the custom workflow activity code and an export of my unmanaged CRM solution to the MSDN developer samples code gallery (direct download link here). The solution export includes a built assembly with the custom workflow activity ready for registration in your CRM system.

Happy workflow scheduling!