Give the gift of life.

What does this have to do with SharePoint workflows?  Last week I was teaching our Mission: Automation class where we cover InfoPath and workflows created with SharePoint Designer.  One of the things we like to do in these classes is take some ideas from our students on business problems that they are trying to solve using SharePoint workflows and attempt to outline the solution and partially implement it during class (if it doesn’t become overly complicated).  This particular idea was generated by Janice and here was her problem.

Janice is the blood drive coordinator at her place of business.  She wanted to figure out a way to use SharePoint and InfoPath to allow people to register for times to give blood.  I thought this sounded like a great example of a scheduling application that many people could find other applications for, so I started down the road of creating the “Blood Drive Scheduling Application” during the class.  It uses an InfoPath form that an employee fills out to reserve a time slot for donating blood.  In this case, there are two donating stations and each one is scheduled at 15 minute intervals during the day.  In other words, for each 15 minute interval during the day, we can schedule a maximum of two people to donate at one time.

If someone changes their mind about the time slot they’ve reserved, we want to give them a way to make a change.  However, we don’t want to give them the ability to change the original form…they will simply request a cancellation and submit another form for a new time slot.  Some of this has to do with who has security to the different lists that we’ll be using in SharePoint to solve this problem.

Now, before we get started let me say that there are a number of ways to solve this problem.  I was going for a solution that could be implemented during the class and illustrated some of the features of InfoPath and SPD workflows.  This is only one solution.  Much more elegant solutions could be implemented!

In this solution we’re going to use several lists.  Blood Drive Reservations will be a forms library where we’ll store the completed InfoPath forms.  Departments contains a list of the departments at the company.  Time Slots contains a list of all the available time slots during the day when a donation reservation can be made.  We’ll have a Cancellations list that we can use for someone to request a cancellation of their reservation.  Finally, we’ll use the built-in Tasks list to assign tasks when a cancellation is requested.

First we’ll start out by creating a custom list that has two columns.  One is the time for each of the 15 minute time slots during the day that we want to schedule and the other column is for recording how many reservations have been made for the time slot.  Here’s what the table looks like in datasheet view.

I created all the time slots for 8:00 AM thru 5:00 PM.

Next up is to create the InfoPath form and library in which the completed forms will be stored.  The form is pretty simple for our example.  Greg suggested we add the department field so there could be some competitive statistics compiled between the different departments.  Nothing like a little competition during blood letting!

The four fields defined in our form’s data source are DonorName, DonorEmail, DonorDept and TimeSlot.

The DonorName and DonorEmail are populated using default values obtained by calling the GetUserProfileByname method in the web service, UserProfileService, referenced at http://server/_vti_bin/userprofileservice.asmx.  This web service will return name/value pairs from the user profile store that is part of SharePoint and normally configured to synchronize with Active Directory.  The PreferredName and WorkEmail are used in this example.

The DonorDept field is populated by creating a data connection to a SharePoint list containing departments.  Nothing really complicated here.

Now, the time slot field is populated in a rather unique way.  The idea here is to filter the available time slots based on the value in Counter field in the Time Slot list.  We’re going to be updating the Counter field with a workflow whenever a new reservation is submitted.  We need to create a view on the field that is filtered to only show time slots when the value of the counter is less than 2.  I created a view called AvailableTimeSlots and added the simple filter as shown.

Now we’re going to use a trick to populate the DonorTimeSlot field.  We’re going to create a data source to an XML file and reference the SharePoint URL protocol supported by OWSSVR.DLL.  This is documented in the WSS SDK and is referenced as follows.  http://server/path/sitename/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={ListGUID}&View={ViewGUID}.  The exact URL for our example is http://portal.awbikes.local/sitedirectory/bd/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}&View={35F16EC8-64E9-4BCE-8C8A-CD3D142AD894}.

The GUIDs for the List and View can be found by selecting the Modify View link in the view selector and decoding the GUIDs in the URL that is displayed.

http://portal.awbikes.local/SiteDirectory/bd/_layouts/ViewEdit.aspx?List=%7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D&View=%7B35F16EC8%2D64E9%2D4BCE%2D8C8A%2DCD3D142AD894%7D&Source=http%253A%252F%252Fportal%252Eawbikes%252Elocal%252FSiteDirectory%252Fbd%252FLists%252FTime%2520Slots%252FAvailableTimeSlots%252Easpx

The List GUID and View GUID are URL encoded, and you can “un-encode” them if you want to (but you don’t have to) by replacing %7B with a left curly brace { and %7D with a right curly brace }.  %2D can be replaced with hyphens.  In the example above, %7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D becomes {BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}.

In the next dialog on the Data Connection Wizard, choose the option to Access the data from the specified location.

Give the data connection a name and make sure you automatically retrieve data when the form is opened and click Finish.

You can see the completed URL n the Summary section.  I like the way the URL looks when the GUIDs are un-encoded…just easier on the eyes.

On the list box entries for the DonorTimeSlot field you need to reference this data source, OWSSVR, and select the entries as shown.

This will populate the DonorTimeSlot list with the filtered list from the AvailableTimeSlots view.

When we publish our form we will promote all of our fields so they appear as metadata columns in SharePoint.

Now, on to our first workflow.

The first workflow is fairly simple.  When a new form is added we want to increment the value of the counter in the Time Slots list for the time slot selected.  If two people select the same time slot, the value will be incremented to 2 and the time slot will no longer show up as an available time slot on the drop-down list in our form (because of the filter we created for our AvailableTimeSlots view).  Within this workflow, we can also send a confirmation email to the donor.  If we really want to get fancy, we can send a reminder email 24 hours before the scheduled appointment.  Here’s the workflow in SPD.

First, let calculate the new counter value by retrieving the value currently in the time slot and adding 1.  We’ll save this calculated value in a variable called IncrementedTimeSlotCounter.

To retrieve the current value of the counter we perform a lookup which reads as

Select Counter from Time Slots where Time Slots:Title equals Blood Drive Reservations:Donor Time Slot.

We are matching up the Title in the Time Slot list (e.g. 8:15 AM) with the time slot selected by the donor on the form from the drop-down list.  Then we simply add 1 to it and store it in a variable.

Next we update the value of the counter in the Time Slots list.  In this example, we’ve created another step in the workflow to do this.

Here’s the screen shot of selecting the Counter field to update in the Time Slots list.

In the “Find the List Item” section, here’s the lookup.  Again, we’re matching up the Time Slots:Title field with the Current Item:Donor Time Slot field (Current Item being our item we’re working on in the Blood Drive Reservations list).

Now let’s create a workflow that sends a confirmation email and a reminder email.  We’ll send the confirmation email right away and pause until early in the morning on February 3^rd and then send a reminder email.  We can do this with a new workflow that also starts when the item is created.  It is okay to have multiple workflows running.  Here’s what it looks like (nothing fancy).  We can reference the Donor Time Slot in the email by clicking the Add Lookup to Body button.

The second step we’ll use the Pause Until Date action and put in a pause with a hard date of February 3^rd and a time of 4 a.m.  Just a quick and dirty reminder email.

We’re all set on the reservation side.  Now, on to the cancellation process.  Here’s the way the cancellation process will work.

We really don’t want end users to be able to delete reservation in the list and we can control that by creating a permission level that is similar to Contribute but without the permissions to edit and delete.  We can alter the security settings on the reservation list so that our Visitors have this permission level for the reservation list.

If someone wants to cancel, they can insert an item in the Cancellations list.  They can select themselves from a drop-down list of people who have reservations and click OK to create the request.  I chose to do it this way so we can get a good match on the Donor Name.  There are other ways to handle this, but this is quick and dirty.

Once they create a cancellation request, a task is assigned to Connie via a workflow.  In this workflow I Iogged some variables to the workflow history list for debugging purposes and then assigned a task to Janice, er, I mean, Connie using the Collect Data from a User action.  Logging information to the history list is a very valuable technique for troubleshooting and validating information in the workflow.  I normally use three actions in sequence to do this:  Build Dynamic String, Set Workflow Variable and Log to History List.

Tasks are very important to understand in workflows.  They are so important they get their own categorization in the Actions selection list.

Collect Data from a User allows you to assign a task and, when that task is edited, a custom form is displayed with fields on it that you define in the workflow.  In this case we are going to ask Connie if it is okay to delete the reservation with a Yes/No drop-down.  Another workflow, Delete Reservation, was created to start on a change to the Tasks list.  It’s job is to determine what the response was (Yes or No) and lookup and delete the reservation.  One of the reasons this method was chosen was because the workflow runs with the credentials of the person who started it.  Connie will have the permissions required to delete the item in the reservations list, while the donor does not.  It also allows her to verify that the person has requested to delete their reservation and not somebody else’s.  Here’s what the Collect Data from a User action looks like.

Next we’ll take a look at the Delete Reservation workflow.  This workflow is created on the Tasks list.  It has three steps.  In the first step we set some variables that we’ll use in the workflow.  These are very important because we are looking up the IDs of the Blood Drive Reservation we need to delete as well as the Time Slot we need to decrement so it is again available.  The key to these lookups begins with the fact that workflow tasks hold some very important information.  The contain a “foreign key” back to the ID of the item in the original list.

The first thing we need is the ID of the reservation we might be deleting.  To get this we choose to lookup the ID field from the source Blood Drive Reservations.  We are going to find this by first matching the name selected in the Cancellations list with the Donor Name in the Blood Drive Reservations list.  To find the correct item in the Cancellations list, we need to reference the Workflow Item ID in the Tasks list (our current list) and match that to the ID of the item in the Cancellations list.  It is very important to understand that you have access to the Workflow Item ID in the Tasks list.  This can be very confusing if you are new to SharePoint Designer workflows, but with some patience it can be understood without having to be a programmer.

Once we have the ID of the reservation, we can find the ID of the time slot that is referenced on the reservation.  We’ll use this to update the counter on the time slot.  We want to find the ID of the time slot so we match the Donor Time Slot on the Blood Drive Reservations list (e.g. 8:30 AM) with the Title on the Time Slots list.  We can find the Donor Time Slot by matching the ID of the Blood Drive Reservation with the ID that we looked up in the previous action and stored in the variable ReservationID.

The next step is just some more logging of messages and variables to the history list for debugging purposes.  In this example I’m logging the IDs I looked up to insure that I have the right ones.

The final step is to actually delete the reservation.  First we look up the current value of the time slot of the reservation to be deleted and subtract 1 and store the result in a variable called DecrementedTimeSlotCounter.  This is easy to do since we have already looked up the value of the ID of the time slot that we need to update in the Set variables step.

We can then update the counter value, again using the ID of the time slot that we previously set in the variable TimeSlotID.

Finally we can delete the reservation by using the ID of the reservation that we previously stored in the variable ReservationID.

I know this has been a long post, but hopefully it helps some folks out there become more familiar with SPD workflows.  This solution is far from perfect and there are several other ideas I would probably incorporate, but it provides a good demonstration of creating a scheduling application with workflows.  I hope you’ve enjoyed it and learned at least one thing (always my goal).

I’m on my way to bed so I can get on a plane tomorrow to do some training in San Antonio, Texas…a short flight from Dallas.  Y’all take care!

Give the gift of life…donate often!