Search This Blog

Tuesday, July 26, 2011

SharePoint Designer 2007 – Business Days Task Reminder Workflow

1. In this article we will discuss how to use SharePoint Designer (SPD) 2007 to create a Business Days Task Reminder Workflow. Here is our scenario:

   a. A business leader assigns a task or tasks to an employee with a due date

   b. The employee receives an initial task alert e-mail message

   c. Four business days before the due date, if the task has not been completed, the employee receives a reminder e-mail

   d. Two business days after the due date, if the task has not been completed, the employee receives the first late reminder e-mail

   e. Seven business days after the due date, if the task has not been completed, the employee receives the first second reminder e-mail

2. To setup our environment we will need to create a custom task list. From the front page of your SharePoint site follow this click path: Site Actions -> Create -> Tracking -> Tasks. Name the new list “Business Days Task Reminder Workflow”. Choose “No” for the “Send e-mail when ownership is assigned” function. The list will have the following standard fields:

  • Title - Single Line of Text
  • Priority - Choice
  • Status - Choice
  • % Complete - Number
  • Assigned To - Person or Group
  • Description - Multiple lines of text
  • Start Date - Date and Time
  • Due Date - Date and Time
3. To build this application we will need four custom components, the SPD workflow to send the e-mail alerts, and three calculated columns to define the proper business day ranges, i.e. four days before due date, two days after the due date, and seven days after the due date.

4. To build the calculated columns we need to first configure our calculated columns to count business days (Monday through Friday) and ignore weekends (Saturday and Sunday). This can be done using the calculated column’s Weekday function and some simple conditional logic. The Weekday function returns an integer for each day of the week, as shown below:

  • Sunday 1
  • Monday 2
  • Tuesday 3
  • Wednesday 4
  • Thursday 5
  • Friday 6
  • Saturday 7
Now using the Weekday function we can use some simple conditional logic to determine business day, such as shown below:

   If Weekday > 1 and Weekday < 7

5. Unfortunately it’s not enough to simply know that the date is a business day, we also have to know how many business days it is from the Due Date, and we have to know this for both before and after the due date. So let’s look at the logic to determine these dates, first here is the logic for calculating 4 business days before the Due Date:

   =IF(WEEKDAY([Due Date])<6,[Due Date]-6,IF(WEEKDAY([Due Date])=6,[Due Date]-4,[Due Date]-4))

Essentially this logic can be explained as follows:

   • If the Due Date is before Friday, subtract 6 from the Due Date to derive the date 4 business days before the Due Date

   • If the Due Date is a Friday, subtract 4 from the Due Date to derive the date 4 business days before the Due Date

   • We don’t need logic for a Due Date of Saturday or Sunday since those aren’t business days

6. Next, here is the logic for calculating 2 business days after the Due Date:

   =IF(WEEKDAY([Due Date])>4,[Due Date]+4,IF(WEEKDAY([Due Date])>1,[Due Date]+2,[Due Date]+2))

Essentially this logic can be explained as follows:

   • If the Due Date is after Wednesday, add 4 to the Due Date to derive the date 2 business days after the Due Date

   • If the Due Date is after Sunday, add 4 to the Due Date to derive the date 2 business days after the Due Date

   • We don’t need logic for a Due Date of Saturday or Sunday since those aren’t business days

7. Finally, here is the logic for calculating 7 business days after the Due Date:

   =IF(WEEKDAY([Due Date])<5,[Due Date]+9,[Due Date]+11)

Essentially this logic can be explained as follows:

   • If the Due Date is before Thursday, add 9 to the Due Date to derive the date 7 business days after the Due Date

   • If the Due Date is Thursday or later, add 11 to the Due Date to derive the date 7 business days after the Due Date

8. Next we need to add all the other lists elements as shown below (Picture1):



There is one other calculated column, Start Date – No Time, its calculation is shown below:

      =TEXT([Start Date],"mmm-dd-yyyy")

And finally, there are two choice columns, they have these values:

   • Priority:

       (1) High

       (2) Normal

       (3) Low

   • Status:

      No Action Taken

      Approved

     Denied

9. Now that we have all the date calculations and other columns in place we can easily create a workflow to send reminders on those dynamic dates. The workflow will be configured to run on "Item Created". Our workflow will have the following four steps:

   a. Send Initial Email

   b. Remind 4 Days Before

   c. Remind 2 Days After

   d. Remind 7 Days After

10. Here is the logic for Step 1 – Send Initial Email (Picture 2):



The “Due Date – No Time does not contain Dec-30-1899” and the “Start Date – No Time does not contain Dec-30-1899”. These conditions are present to prevent the workflow from running if the user has not provided a Due Date or a Start Date. Normally these would be mandatory fields but in this case the customer wanted the ability to do a draft task, and not have to worry about the workflow sending out partial data. If the user doesn’t supply a Due Date or a Start Date the calculated column will generate a date of Dec-30-1899 (because the date is blank) and the workflow will not start.

I use a Boolean called First Run to tell me if the workflow has been rerun and if so to protect against certain conditions causing errors. As you will see, all the other steps will only be run if First Run equals “No”. The First Run value is changed when the Action “Update Items in Tasks” runs (the third Action in this step).

The initial e-mail is sent to the Assignee in the second Action of this step. Obviously the content of the e-mail can be whatever the user requires but essentially it is informing the recipient about the details of the tasks they must perform.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Minus 4 BDs”.

11. Here is the logic for Step 2 - Remind 4 Days Before (Picture 3):



In the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The second e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but basically it’s a reminder that the task has still not been completed.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Plus 2 BDs”.

12. Here is the logic for Step 3 - Remind 2 Days After (Picture 4):



Again, in the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The third e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but again basically it’s a reminder that the task has still not been completed.

As you can see in the pause action, the pause is set to a date and the date is set equal to the calculated column “Due Date Plus 7 BDs”.

13. Here is the logic for Step 3 - Remind 7 Days After (Picture 5):



Again, in the conditions the First Run value should now be changed to “No”, if the value is “Yes” it means the conditions of the first step were not met and therefore this step should not run either.

The final e-mail is sent to the Assignee in the second Action of this step. The content of the e-mail can be whatever the user requires but basically it’s a notice to the end user that the task has still not been completed within the allotted time.

14. OK, now both our calculated columns and our workflow are ready! Now let’s try out our application. Create a new task in the task list. Fill out the task as normally but in the Status field choose “No Action Taken”. For testing choose yourself as the “Assigned To”. Select the current date as the “Start Date”, and a day a week from now as the “Due Date”. Finally, click the OK button to start the workflow.



15. If everything is built correctly the workflow should send out an initial e-mail, a reminder e-mail 4 business days before the Due Date, another reminder e-mail 2 business days after the Due Date, and a final reminder e-mail 7 business days after the Due Date.

16. As always the power and utility of SharePoint Designer workflows amazes me – all this function without any code – now that is awesome!


I hope that helps!

Tom Molskow












12 comments:

Anonymous said...

Hi, nice article but Step 3 Remind 7 Days After and Remind 2 Days After is not working with me, please help

Regards,

Tom Molskow said...

Hello,

Can you tell me what is happening in the solution? Are you getting an error message, does the task not fire, is the calculated column showing an error? Any detailed information you can provide would help me solve the issue for you.

Tom

Anonymous said...

Hi Tom,

I have scenario where e-mail alert should be sent to user 30 days prior to Due Date of progress report.

its not like assigning a task and send alert based on task due date.

Basically the progress report is due every 6 months or 3 months and I need to generate alert which would sent to user 30 days prior to the date of report.

Could you please any hint on how can I achieve this.

Thanks in advance.

Tom Molskow said...

Hello,

Please review another one of my blogs located here -

http://sharepointgypsy.blogspot.com/2011/01/sharepoint-designer-workflows-3.html

This Blog will tell you how to design a Daily Timer Loop using SharePoint Designer workflows. Next create an Alert Date field in your list, set that date to thirty days prior to the due date (this can also be generated dynamically using calculated columns). Now you can create some additional logic in the Daily Timer Loop workflows that will check the Alert Date field everytime it runs, and then when the Alert Date is equal to Today, trigger a send e-mail message action.

I hope that helps!

Tom

Anonymous said...

Hello Tom,
How do you trigger the workflow? I only have three choices: manually start it, whenever item is created and when item is changed. Is there a way to run it once a day using scheduled task or something?

Tom Molskow said...

Hello,

Sorry about that, the workflow should be configured to start on item created, I will update the blog to add that information.

I hope that helps!

Tom

shafi said...

Hi.
This a nice one. Thanks for your post.

But there is a limitation for this method. If we modify the due date after creating one task, then the workflow will not send the email on correct date

Tom Molskow said...

Hey Shafi,

Yes, this particular solution is not designed to support changing the due date once it has been set.

Thanks!

Tom

Marj said...

Hello, I am very new to SharePoint and am hoping this will help me with a project I'm working on. I have a question about #4 above. Where do you find the calculated column's Weekday function? If I go to create a column and select "calculated" I don't get any option for a Weekday function. I am using SharePoint Services 3.0, so I don't know if that is where my limitation is. Thanks for any help you can provide! Marj

Tom Molskow said...

Hey Marge,

Sorry for the late reply, I have been busy working on a project.

The WEEKDAY function can be used in calculated columns to convert dates to the text for the day of the week (see this article for more information - http://msdn.microsoft.com/en-us/library/bb862071(v=office.12).aspx).

In step 5,6, and 7 you simply add the formulas I provided into the calculated field and everything will work great.

I hope that helps!

Tom

Anonymous said...

Hello. Thank you for this wonderful set of instructions!

I have a question about Step Name: 1wfSendInitialEmail

In this Step, you include the Action: "then Update item in Tasks"

What did you actually update?

Thank you,
-imber

Anonymous said...

Tom,

Please disregard my earlier comment. Upon re-reading, I see clear as day:

I use a Boolean called First Run to tell me if the workflow has been rerun and if so to protect against certain conditions causing errors. As you will see, all the other steps will only be run if First Run equals “No”. The First Run value is changed when the Action “Update Items in Tasks” runs (the third Action in this step).

Again, thank you for sharing your wonderful work!

-imber