Wednesday, October 15, 2008

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:
[1st Person]
[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 (1st shift, 2nd shift, 3rd 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 1st 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 1st 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 1st Person (copying the 2nd Person and 3rd Person) informing them they have failed and the task has been reassigned to someone more capable (actually the 1st 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 2nd 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!


Anonymous said...

Excellent post.

Anonymous said...

Good Keepit up.

Anonymous said...

A related question I have is - How can I find the date a workflow is completed so that I can populate a document library item with that information as metadata.

Also, willl this approach update the list item for each completed workflow date/time (future versions / instances of the workflow), or will it just be a fixed value after the first instance?

Russell Wright said...

To find the date a workflow is completed you can do a couple of things. First, if you have another list that you are logging to you can make an update to the list and, following that, get the Modified date/time on the item in the list and capture that to a workflow variable, as it will contain your current date/time.

Another approach is to create a list (let's call it Tweak) with a Title set to "Today" and another column we'll call "Toggle." Before you end your workflow you can update Toggle to some value where Title = "Today" and then get the modified date on the record and use that as the current date/time.

Anonymous said...

Is it possible to complete a task without user intervention like automatically complete a task?

Russell Wright said...

I have not been able to find a way to "complete" a task from a SPD workflow the same way it gets marked completed from the task completion form. :(

Rob Dixon said...

Very useful, but very labor intensive, like many areas of SharePoint. How could we take this concept and apply it to a reusable workflow developed within Visual Studio? I can see how one could conceivably create a completely generic workflow with some basic behaviors, but configured from an outside table or SharePoint list to handle the variable aspects like escalation settings or time durations. Does anyone know of any examples showing best practices for looking up configuration data for Visual Studio workflows?

Sushil .L. Dhiver said...

i am trying to implement your logic into my application, but am having problems..
i have a list which will take the feedback/suggestion from the user based on the category..
there's another list for the category
which is a master n is a lookup in the feedback/suggestion list..
i've also created another list called the feedback access control..
which contains the category, process owner, turn around days, escalation id,
the process owner and the escalation id are the person lookups and the turnaround days is the no. of days after which the feedback should be escalated if no response is given..
the approver will be the category owner , so i cannot hard code it..
its defined in the access control..
but i cant seem to apply this in the workflow..
can u please provide me with a video of the above tutorial or can u tell me where i am going wrong ?

thanks in advance..

Unknown said...


Very informative post.
I am facing the same problem as you mentioned "SharePoint didn't want to set the "Assigned To" field during the workflow."

I have a workflow attached to document library. when I assign task to reviewers, their name doesn't show in the 'assigned to' column if I put a conditional checking (if status equals to in progess )in my secondary work attached to task list.

R Dilip Kumar said...

This is a excellent tutorial. But I am facing a slight problem.
First, when a TT is created, the person creating it is not getting an email. Also he doesnt get an email when the status is changed. Is there something I am doing wrong?
Second, when I do the pause for duration workflow step, I am not getting all the options from the list to display in the define workflow lookup box.

Ricky Spears said...

R Dilip - If emails aren't being sent, it could mean that Outgoing Email hasn't been configured on your server. If emails are being sent by the server, but aren't being received by the user, they may be getting flagged as spam or junk, so check those folders.

When you initially define the task by using the Custom Task Wizard, SharePoint Designer creates a new Content Type named Trouble Ticket on the task list. Unfortunately, Workflow Designer doesn't know about this, so it doesn't know about the columns associated with it. You'll need to compile the workflow and then open it back up for editing to see those fields.

I hope this helps!

If you want to dig deep into all this stuff, you may want to consider attending our 4-day InfoPath 2010 and SharePoint Server 2010 No-Code Workflow Deep Dive class.