Task Escalation using Workflows

I was recently showing one of the students in my class (who, by the way, was one of four students that traveled all the way from Ghana to attend four different SharePoint Solutions classes) how to assign tasks and escalate the tasks using a SharePoint Designer workflow. Before we started, he was convinced that they needed to do this in Visual Studio. I quickly told him "that's completely doable with a SharePoint Designer workflow" and began showing him a quick demo of how to do it.
When you create workflows that assign tasks and you want to escalate those tasks to other people when the tasks are not completed in a certain time period, it is best to use an additional list that stores this configuration information. You only want to create the workflow once and, if the people change over time, simply update a SharePoint list with the new people (and time periods).
So, let's begin by creating our list that drives the assignment of tasks and their escalation. Here's a list for our four regions: North, East, South and West. In the list we have three levels; the first person to assign the task to, the second person to escalate the task to and the final person (head honcho) that will get the task if the first two fail to perform their duties in the prescribed time periods. These time limits are defined as: 1st Person Task Time and 2nd Person Task Time, which define how many hours (or minutes or days) we want to give each person before escalation occurs.


I got bit doing this the first time and I'm not sure why. When I created and used an Issues list, SharePoint didn't want to set the "Assigned To" field during the workflow. However, when I recreated the issues list and tried again, it worked. Not sure why.
Anyway, I created an Issues list because it has all the columns you would need for creating a "trouble ticket."

On the trouble ticket issues list, I created a matching field called Region, because tickets are opened up for a region and assigned to a person that is responsible for the region. In this case I created a lookup field on Region in the Escalation List.
The first thing I want to do in my workflow is to assign the issues list item (we'll refer to it as the trouble ticket) to the first person AND assign them a task. Let's start with assigning the trouble ticket to the first person.
Create a new workflow on the list and automatically start it when an item is created.

In the first step, we're going to Set Field in Current Item (i.e. we are going to set the "Assigned to" field).

Now we begin the lookup on who we're going to assign the item to.

Here's the final lookup definition. We are selecting 1st Person from Escalation List where Escalation List:Region = Trouble Tickets:Region. In SQL syntax:
Select
[1^st Person]
From
[Escalation List]
Inner Join [Trouble Tickets]
ON [Escalation List].Region = [Trouble Tickets].Region



We know this is not guaranteed to return a single value, except for the fact that in our Escalation list we only have ONE record for each region; therefore, we are guaranteeing it to be unique.
Okay, now let's add a trouble ticket and see what happens!

Hey, it works! (This is the part that didn't work for me the first time I did it. Hmmmm…). So, you can see that the first part of our workflow is functional. Whenever a person creates a trouble ticket, it is automatically assigned to the person in the list. Can you imagine creating a time window for each person (1^st shift, 2^nd shift, 3^rd shift)? The correct person could be assigned based on when the ticket was created. Perhaps these people are in different time zones…just food for thought.
Okay, let's work on the next step. The next step will assign a task to the first person in the Escalation list. This is the same person that the trouble-ticket was assigned to.

The task is assigned to the same person as the trouble ticket, so the workflow lookup will be the same.



We could, of course, add other actions that would execute after the task was completed, but for the sake of brevity, we'll stop here. If the option is turned on in the task list to send an email when the task is assigned, then you don't have to create a custom email in the workflow (assuming, of course, the out-of-the-box email works for you).
When we create a new trouble ticket, we can see that the Assigned To is completed (Jose Curry) and checking our Tasks list we see that Jose has also been assigned a task.



Now for the next, and most important, step. In order for the task to be reassigned (escalated) after a time period, we need to create a workflow on the tasks list. This workflow will start when a new item is created and pause until the time period has expired. At this point, it will perform a test to determine if the task should be escalated.
Create the workflow on the Tasks list.

Here's the completed lookup. We want to select the 1^st Person Task Time from Escalation List where the Region in the Escalation List matches the Region in the Trouble Tickets list.


We can find the Region in the Trouble Tickets list because the Current Item (our task) has a reference back to the ID of the item that created the task (Workflow Item ID). This is a very important concept of which to be aware when you are creating workflows and tasks. Each task has a reference back to the ID of item that created the task. Therefore, you can always "find" the original item that relates to the task and use the information from the original item to drive your workflow.
Our "Pause for duration" step shows that we will pause the workflow for the number of minutes (we could have just as easily used hours or days) specified in the 1^st Person Task Time field of the Escalation List. After pausing, we'll perform a test to determine if the task needs to be reassigned (escalated). We'll perform this test by checking to see if the status has been changed to something other than "Not Started." If the task is reassigned, we'll also send an email to the 1^st Person (copying the 2^nd Person and 3^rd Person) informing them they have failed and the task has been reassigned to someone more capable (actually the 1^st Person was on vacation). Before doing this we'll assign some variables to the people involved so we don't have to perform the lookups multiple times.

Assign some variables to lookups to avoid doing the lookup work multiple times.

Here's an example lookup to find the 2^nd Person in the Escalation List.




Next we'll send an email to the original "Assigned To" on the Trouble Ticket list informing them the task has be reassigned to another person. Again we'll use the variables we created to assign the To: and CC: fields.

Now we just need to compile the workflow and test it by waiting the appropriate amount of time. We can see that the task has been assigned to Patricia and is "In Progress."

Pausing for 5 minutes…

After 5 minutes (more or less, as pausing is a "lazy" process), we see that the task is now assigned to Gail. Additionally Gail will be copied on an email notifying her of the task reassignment.

So, that is one way task escalation can be handled. To handle the next level of task reassignment, you'll need to either add additional steps in the workflow to pause the workflow and then perform another check, or start multiple workflows that will handle each case.
That was easy!