Triggering workflow on a date basis

[EDIT] Although this process still works, it's highly recommended that you use Power Automate (Flows) to do this job. It is way easier to create and maintain your process.

For a long time, I found myself thinking about how to deal with events that should be triggered by specific dates, for example, an automatic email that should be sent every time it's one's birthday.

I've found some good explanations about solutions using Wait and Timeout workflow (here and here), but this also made me aware of the problems of a misconfiguration can cause.

Consider this situation: you want to change the status of a record when its end date has reached. You just set the creation of the entity as the trigger to a timeout workflow that does this job, based on the end date field, right? Well, unfortunately, the end date value is not mandatory, and the user can set it later. No issues, let's use the change of the date as a trigger as well. Only with this basic case, we already have some problems:

  • If the user creates a record with no end date, the timeout will wait forever to run, so validation is necessary.
  • Once the change of the date is also a trigger, if the user corrects the value, another workflow will run. Now we have two processes running.
  • For every record created, a new timeout workflow is also created. Such a waste of resources.

For our luck, Alexander Development came with a clever solution: a single process that is triggered on the time-space wanted (daily, on the proposed situation), and queries the cases that should be affected and run your process over the returned results. Smart right? Let's see how it works.

Install
Download the solution from the official repository and import it on the system.

The workflow
Create the workflow process that will make the changes you want. Don't worry about conditions, we're taking care of it with the fetchXML query. In the example below, we are just setting the appointment status as Completed:
*Important: since the workflow will be called by another process, we need to set it as a child process.
Also, uncheck all the start when conditions to avoid it to be called on other situations.

The entity
The solution creates an entity called Recurring process that will be responsible to hold the configuration for every time-ruled process you want to run in the system. So go ahead and create a new record for it.
In my case, the solution didn't create a shortcut by default, so you can either insert it to your menu or just find it using the advanced filter.

Set up
This is how the entity looks like. Let's understand the fields and set it up.

Name: The name for your process
Entity type: The system name of the target entity
Workflow: The workflow you want to trigger
Frequency: Hourly | Daily | Weekly
Next run date: Here you should insert the first run date. This value will be automatically updated at every run
Last run date: Read-only. It shows the last time it ran
FetchXML query: This is the important part. Create a query that will return all the records you want to be affected by the process. For example, if the frequency set is daily, filter all the appointments where end date = yesterday, and don't worry about the fields retrieved, they're not going to be used.
To create the query, use the plugin FetchXML Builder available on the XrmToolBox.

<fetch>
  <entity name="appointment" >
    <attribute name="serviceidname" />
    <filter>
      <condition attribute="scheduledend" operator="yesterday" />
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

The query is also making sure that the actual status of the appointment = 0 (open). So here is where you set all the filters and criteria, whereas the called workflow will be just the endpoint with the action. Test the query multiple times and make sure you have just the results you want.

And that's all! Now, instead of having numerous workflows waiting and counting for the right time to act, we have a single task that does all the job, running the main process just against the wantedcrecords.

Don't stop learning!

Cassio Milanelo

Skilled and self-motivated Dynamics 365 CE developer and Power Platform jack of all trades. Acknowledged for high productivity and enthusiasm. Slightly workaholic and coffee lover ☕