Please Note: Laura Rogers uses this solution during her “Power User Workflow w/ SharePoint Designer: Multiple Lists and Variables” live, online workshop. To get hands-on experience with this solution and others, join us this Friday at 1:00pm EST.

From Paul Galvin:

SharePoint provides an alerting function out of the box.  The alert mechanism is reasonably flexible, but often times, we want more sophisticated alerts that SharePoint simply cannot provide without some extra elbow grease.  This issue comes up fairly regularly in online forums such as MSDN and EndUserSharePoint’s own Stump the Panel.  The business problem is stately fairly simply:

“I need SharePoint to send an alert whenever the status of the document changes.”

This is actually a pretty easy business requirement to meet using SharePoint designer.  This article explains how.

Solution Overview

The overall solution consists of the following components:

• A few site columns and a site content type.
• A SharePoint designer workflow.
• A document library or list.

We will create a SharePoint designer workflow that runs automatically whenever an in a library changes.   This library will use two site columns to determine whether the field in which we are interested changed value.  If it did change value, we send an email.  Otherwise, we go gracefully exit.

The trick here is to use a “shadow column.”  In my old (really old) COBOL days, we tended to call this a “break group” if that helps.

When the workflow runs it will compare the value of the current status column to the value of the shadow column.  If they are the same, we take no action.  If they are different, then we:

• Send the email.
• Copy the value of the current column to the shadow column.

The workflow may run over and over again.  This technique ensures that we always know when the “real” column changed value, allowing us to send out an email alert only when warranted.

Walk-Through

Create a content type and add two columns, Single Line of Text, as shown:

Figure 1: Create two site columns

Note that we want to hide “Old Process Approval Status” when it’s displayed on forms.  Click on the column name and update it as shown:

Figure 2: Hide the column on forms

Create a SharePoint designer workflow on the library.  I’m using the trusty “Shared Documents” library here, but any document library will do.

Figure 3: Create the SharePoint Designer Workflow

My workflow has three steps.

Step1: Determine whether the status was changed:

Figure 4: Step 1 – Determine whether the status has changed or not

Note that I am logging messages here because it’s easier to test these messages, as opposed to sending out an email and waiting for it to arrive in my inbox.
Also note that whenever I use the “Stop” action, I am always careful to put a message that includes “This is not an error.”  That’s very important because workflows frequently stop because of error conditions.  We want to be crystal clear that we aren’t stopping due to an error.

Step 2: Send the Email

Figure 5: Step 2 – Send the email

Finally, we set our “old” value equal to the current value as shown:

Figure 6: Update shadow column

This is the critical steps that allows us to send emails only when the field value changes.

Testing

We want to test two different scenarios:

• Upload a document.   We won’t expect any email to be sent in this case since no one has changed the approval status yet.
• Change the approval status.  In this case, we expect the email.
• Change another piece of meta data, but not the approval status.  In this case, we do not expect the system to send us an email.

Note: This workflow is configured to start automatically.  With Service Pack 1, Microsoft introduced a new security feature that prevents an administrator account from starting any SharePoint designer workflow automatically.  You can tell you’re logged in as an administrator account when your Welcome message looks like this:

Figure 7: Find the “Welcome message”

Conclusion

SharePoint designer workflow enables us to create flexible column-based alerts.

We can extend this solution to enable alerting on multiple columns by adding additional shadow columns.

It’s important to realize an important fact about this workflow.  Step #3 updates the list item.  Since the list item was changed, SharePoint automatically starts the workflow again.  That’s not a problem for us in this scenario because the second time around, the shadow column will match the real column and it will exit gracefully.  However, you do need to be aware of this because you may have other SharePoint Designer workflows set up to run automatically on this item.  If that happens, they may run at unexpected times, to be aware of that and account for it. 

The approach described by this article applies equally well to custom lists, calendars, contacts, etc.  It is not limited to document libraries.

Paul Galvin, Microsoft MVP – SharePoint
Web site: Paul Galvin’s SharePoint Space

Paul is a Solutions Architect currently working most closely with Microsoft Office SharePoint Server 2007. He was recently awarded Microsoft MVP – SharePoint status for his work with the SharePoint community.