How to get an SPD Workflow to Run at a Specific Time – Employee Vacation Reminder: Part 1

Dessie Lunsford
Points-of-Sharing 

In this article, we’re going to look at how to fire off an SPD workflow at a specific time of day for a fixed number of days.  Thanks to “IAIN” on the “STP Forums” (once again) for asking the question on how to build this out for use with an employee vacation tracking list.

The Setup

The question we have is, how do we tell a workflow to fire off at a specific time of day (8 AM for example) in order to send out an email reminder to let people know who’s on vacation for that day?  Additionally (and adding in another level of complexity), how can we make it somewhat-automated so that when the employee returns from vacation, the workflow process ends?
Both pieces of this would be rather simple in the programming world, but for an SPD Workflow, we’ll have to get a bit creative in order to create this functionality (all “out-of-the-box” as well).

Building the List

To start, let’s get our list setup and begin adding in the first pieces we’ll need to get this going.
Create a new custom list called “Employee Vacations”.

On the list, add in a few new columns to make it more appropriate for tracking people and dates (Note – I’m only going to add in the basics needed for the actual employee data, so build this out as required for your specific scenario):

Column Name: “Employee”
Type: Single line of text (or a “lookup” if you don’t want to manually enter in their name).

Column Name: “Status”
Type: Choice (add choices of: “On Vacation” and “Off Vacation”)
Display: Drop-Down Menu (Radio Buttons would be fine also)

Column Name: “Total Days Gone”
Type: Number
(In reality, you’d probably want to build this out as two fields – “Vacation Start Date” and “Vacation End Date”, then just calculate the difference between the two dates to come up with the total number of days the employee will be gone for, but to keep this post shorter, I’m forgoing that process and just using a simple number field).

Column Name: “Email Reminder Time”
Type: Date and Time
Set the Format as: Date & Time

Column Name: “Count”
Type: Number (this will keep track of how many times the workflow has looped)
Set the default value to “0” (zero)
When completed, your list should look like:

SharePoint Designer

Now that our list is setup, open SharePoint Designer, connect to the site and create a new SharePoint Workflow:

Name the workflow “Employee Vacation Daily Reminder”, choose our new list in the attachment dropdown, set the workflow “Start options” to both when a new item is created and whenever an item is changed then click “Next”.

For those of you that have read pretty much any article I’ve ever written on “Calculated Columns”, you know that I tend to focus on the logic that needs to take place in order for something to work in the manner you want it to – this is definitely no exception.  For us to be able to have the workflow do what we want there are several steps we have to run through and several levels of logic that will be used in order to run through the different scenarios that will be present in the list.

To avoid some of the confusion at this point, lets briefly look at the different cases we’ll see on the list – each will be a different “stage” of the workflow process (although we’ll be writing them into a single step that uses a series of “IF-ELSE IF” conditional branches to determine the stage).

Case By Case

Case 1: Employee is now on vacation and the workflow initiates.  All values are the defaults, so the first stage of the workflow gets processed.

Case 2: Employee is on vacation and the workflow has already run at least one time.  On the initial firing of the workflow, a counter is incremented to keep track of how many times the workflow has looped.  This count is compared to how many days total the employee will be on vacation and will keep looping until the counter reaches the total number.

Case 3: The employee is on vacation and is about to return.  Our counter from the previous stage now is equal to the total number of days that the employee was to be gone for.  Since the numbers now equal each other, the final stage is processed which updates their status and ends the workflow.

That’s all there is to it – 3 simple cases to look for, and based on which one the workflow sees, different actions will take place.

Tomorrow, Dessie will get us started with Case 1.

Dessie Lunsford
Points-of-Sharing