Use SharePoint Designer to Email Daily Task Reminders

Introduction

It never fails to happen.  Your company invests in SharePoint, takes time to define business processes and automate them using workflow (with a dash of InfoPath, perhaps), tests the solution to within an inch of its life and finally launches in production.  Two days later, it comes to a screeching halt because the user community isn’t completing system-assigned tasks.  Business processes don’t run to completion, KPIs are flashing amber and red.  Someone has just got around to blaming the problem on global warming …

Of course, failing to complete tasks won’t normally cause that much trouble.  It’s just plain inefficient, even in a simple scenario where a department manager manually creates tasks in a WSS task list.  The manager shouldn’t have to remember to look at an “incomplete tasks view” to find out which department members have overdue tasks.

Users always have plenty of excuses:

• “I can’t find the task.”
• “I didn’t know I was assigned a task.”
• “The dog ate my task.”

This a common business problem and we can solve it.

Companies often turn to some kind of automated reminder system to prod End Users into marking their tasks complete, thereby allowing those beautifuly orchestrated business processes to proceed as designed.  This article describes how “Power” End Users and Developers alike can use SharePoint Designer to create an automated task reminder system based on SharePoint’s core workflow capability.  This solution applies equally well to Windows SharePoint Services 3.0 (WSS) and Microsoft Office SharePoint Server (MOSS).  The article starts off with a little theory and then moves into a painstakingly detailed and lovingly created walk-through describing exactly how to create this workflow solution.   After the walk-through, it describes some important limitations, some technical implications and finally, suggests some useful extensions.

First, the business requirement:

• A task has been assigned to a user.
• The system should send an email to the user every 24 hours until the user has completed the task.

It doesn’t get much more straight-forward than that.

We will use SharePoint Designer to create a workflow solution that meets that requirement.  The solution leverages several SharePoint features:

• Windows Workflow Foundation.
• SharePoint Designer (of course).
• SharePoint workflow’s remorseless nature.

“Remorseless nature” means that a functioning SharePoint environment continues running a given workflow forever unless one of two things happens:

1. The task is deleted.
2. The workflow decides on its own to quit.

Before we fire up SharePoint Designer, we need to consider the workflow’s design:

This looping workflow has one choice to make among three options each time iterates:

1. Is the task complete? If yes, quit. Yeah, we’re done!
2. Is this the first time I’m sending a reminder? If yes, send a reminder and pause one day. Set a flag to indicate that the first reminder has been sent.
3. Is the second or more time I’m sending a reminder? If yes, send it and pause one day. Increment a counter to indicate how many reminders have been sent.

(Developer readers here may be thinking to themselves that these seem a lot like states and/or state transitions.  You’d be correct to think that.)

Pre-Configuration

To support a design like this, we need to add two columns to our task:

1. FirstEmailReminderSent
2. EmailReminderCount

The workflow uses these fields to maintain some information about the task (e.g. was an email reminder sent) as well as to trigger the workflow to run again for subsequent reminders.

These two columns, added via the task list’s settings, look like this:

It’s important to set the default value of FirstEmailReminderSent to “No”.

EmailReminderCount is simply an number with default value of zero.

Walk-through

With this configuration work complete, let’s see all of this in action by creating a workflow.  Firing up SharePoint Designer, we create the workflow in the normal way (i.e. open up the site with the task list, create new SharePoint content, create a workflow).  It looks like this:

The workflow has been named “Daily Reminder,” it’s associated with a list called “Tasks” (i.e. the task list) and here is the key: It’s set to run both when the task is created and when it’s changed.  The “whenever an item is changed” bit is very important, as we’ll see later.  This setting enable us to leverage SharePoint’s remorseless nature to our benefit.

The first step of the workflow is easy: Stop running if the task has been completed:

The second step is more interesting.  This step, labeled “Send Initial Email,” obviously sends the first email.  However, it also sets up the subsequent reminder.  Let’s see how that works:

Note that this task only runs if our task column, “FirstEmailReminderSent” is equal to No.  Since we were careful to specify “no” as this column’s default value, we know this step will run (provided that we got past the first step above).

First, we send the reminder email.

Then, we set FirstEmailReminderSent to Yes.  This ensures that this particular step never runs again.

Next, we pause for one day.

Once our pause completes, we want to start looping.  We do this by “poking” the task by changing any field of the current item.  When we do this, from SharePoint workflow’s  perspective, the item has been changed.  Since we checked “Automatically start this workflow when an item is changed,” it starts the workflow all over again.   We could pick any field but since we need to “poke” the current item, we may as well derive some business value at the same time.  We’ll use the EmailReminderCount field.  This way, we can poke it and know how many times the reminder has been sent, killing two birds with one stone.  The next series of screen shots show how to increment that counter.

First, we create a workflow variable as shown:

Copy the current value of EmailReminderCount to our TemporaryCounter variable using the “Set Workflow Variable” action:

Use the “Do Calculation” action to increment this by one.

Finally, update the Task’s EmailReminderCount using the “Update Field in Current Item” action.

The third and final step looks very similar to the previous:

This step only runs if the task was not marked as completed and if the first email reminder has already been sent.

Workflow pauses for a day and then increments the EmailReminderCount by one.  Performing this update causes the workflow to start over again.  Assuming the user never marked the task as complete, SharePoint workflow skips the first steps (since task is not completed) and the second step (since FirstEmailReminderSent is still Yes), landing back here on the third step.  It sends the email and pauses 24 hours, pokes itself and keeps looping like this, forever, until the task is completed or it’s deleted.

The following series of screenshots capture a test environment where the pause has been set to a short time interval:

In the above screen shot, the system has sent three reminders and is still waiting for the workflow to be completed.

Here is the initial workflow history:

Here is how the task is marked complete:

Here is the final workflow history:

Limitations

SharePoint Designer workflow is … limited.  There are several important limitations, but one of the most important for most users to understand is that these workflow solutions cannot be easily ported from one environment to another.  In fact, they cannot be easily ported anywhere, period.  There are clever workarounds to this problem, but that’s for a future article. (Hint -edit the XOML file directly, replacing task, list and other GUIDs manually).

Second, the workflow history is a bit garbled.  Conceptually, this is just one happy workflow.  However, at a machine/SharePoint level, we really have multiple instances of the same workflow.  This isn’t a problem for SharePoint, but it does sew confusion in a reporting sense.  As the final workflow history screen shows, even though we humans think just one workflow actually ran for this task (the Daily Reminder workflow), SharePoint shows five separate histories.  In our reminder scenario, this really isn’t a problem.  Each workflow history represents one email reminder.  However, if we had extensive logging and other complicated activities taking place, it would be hard to decipher the history and required a lot of clicking.  There is a clever solution to this problem as well, again for a future article.  (Hint – create entries in a custom list using a common key field to group them together).

Finally, it is important to realize that WSS and MOSS service pack 1 introduced a security feature that prevents a system administration account from starting any workflow automatically (though this was recently amended with the 07/15/08 infrastructure update).  If you’re testing this out in your environment and nothing seems to be happening, log in to the system as a non-admin account:

State Machines in SharePoint Designer?  Looping?  Preposterous!

As hinted at early on, this solution actually defines a state machine.  It uses the task itself as a persistent store for state data.  This workflow defines the following states:

• Starting state: Sending a first reminder.
• Reminder state: Sending a subsequent reminder.
• End state: Marking the task as complete.

It’s not hard to imagine creating other kinds of state machines using this approach.

There is a real risk of pushing beyond reasonable business scenarios for which SharePoint Designer was designed (and some might argue that we already have).  Keep that in mind.  A complicated state machine workflow should be developed using visual studio with its close-to-the-metal workflow building tool set.

Extending the Solution

Several common sense extensions come to mind:

• Weekends: Incorporate some logic to determine whether the current date is a non-business day. If it is, simply pause for 24 hours before running again.
• KPIs: Integrate this solution with a KPI. If the user requires many reminders, turn amber and eventually red to indicate a seriously overdue task.
• Escalation: if a given task requires too many reminders, fire off an email to a manager.
• Due Date Aware: Don’t send an email reminder for task unless it has passed its due date.

Conclusion

This article describes a fully functional email reminder system.  The solution leverages core WSS workflow technology and uses a relatively friendly tool, SharePoint Designer, to do it.  This means it does not require MOSS but works in MOSS (since MOSS extends WSS).

Although the business example focuses on tasks, it should be clear that the pattern described here can fit into many different business scenarios that require repetitive (looping) actions over a period of time.  Simply define how the loop should end, what it should do in the first iteration and what it should do for subsequent iterations.

Too often, we hear that SharePoint Designer workflow can’t loop, can’t create state machines, is terribly limited and crippled, etc.  With a little imagination, we can prove the critics wrong and build simple yet powerful workflow solutions using this tool.