Have you ever created a custom document approval / document routing Workflow with Visual Studio which triggers off a change from another, out-of-the-box Workflow?  Were you rather upset when your Workflow Item Changed Activity implementation got lost in the woods because SharePoint reported the incorrect out-of-the-box Workflow value to your custom Workflow?  On an old project, before I conceived of the notion of an SPQuery Hack, we discovered this particular issue was happening on a client's SharePoint site.  I was unable to confirm the precise version number of the installation; however similar issues along these lines have happened since to SharePoint installations with the latest Cumulative Updates released after Service Pack 2 (see the older blog post SPQuery Hacks Part 1).  What did we do?  We turned to the SPQuery object for help.  This article is the second in our SPQuery Hacks series.

Use Case Scenario

NOTE: I'm not dealing with a simple use case here.

As with many of our clients, their "Approval Workflow" procedures are less than straight-forward.  SharePoint provides an out-of-the-box approval workflow which, if you're a configuration management buff, is fabulous because... it's already developed, tested, and bundled with SharePoint 2007.  To a Project Manager this means less risk, to a tester this means less test cases, and to a developer this means less code to write when putting together a complicated approval process involving multiple stages and, especially, a dynamic list of users.  Ergo, the Custom Approval Routing Workflow is born.

The Facts

Explain yourself... custom approval routing, isn't that already an out of the box feature?  Now I'm lost in the woods...

Yes, approval routing is out-of-the-box already, but rarely can it stay that way in a production, customer scenario.  Here's my scenario:

·      I have two departments who need to approve my document before it goes out the door

o   One department before the other

·       I need to filter my Document Library by Department Approval

o   Thus I need two distinct approval workflows running on my document library to have two distinct approval workflow columns

·      The people who need to approve the document change based on some piece of metadata

What do I do?

The Tool

Create a custom workflow with Visual Studio that programmatically starts each out-of-the-box workflow and dynamically sets the target Approver.  Implement all your custom business logic in that workflow and let the out-of-the-box workflows do the one thing they do better than you or I could ever do with code: request and record the actual approval.

I've tried rolling my own approval workflow... it's just not worth it.

The Problem

Everything works great until the out-of-the-box (OOTB) SharePoint Approval Workflow is finished running.  What happens is this:

·         Your Custom Workflow Enters the For Loop

o   Waiting for Item Changed to Happen

·         SharePoint Approval Workflow Reports "Approved"

·         Your Custom Workflow Item Changed Activity Kicks Off

·         Your Custom Workflow Sees "In Progress" for the SharePoint Approval Workflow

o   Wait a minute... what's going on here?

o   Your For Loop Activity Re-Enters the Same Loop

·         Your Custom Workflow Waits Indefinitely for Item Changed

Now your workflow is lost in the woods, again due to a race condition just like what I discussed in the first SPQuery Hacks blog post.  The SharePoint Approval Workflow is done running, it's not going to run again, the person already approved the document, you can't make them do it again and again until finally it works, and now your workflow is stuck waiting, indefinitely, for something that already happened.

Sometimes, the Custom Workflow will detect the correct value, but not 100% of the time.  To get around this problem, use an SPQuery after your Workflow Item Changed Activity fires.  Here again, edited for this post, and for your convenience, is the SPQuery Hack implementation.

Fetch the Same Item Again

Instead of using the Item Changed Activity to read values from the document's columns to get the SharePoint Approval Workflow status, we used an SPQuery to get a second instance of the SPListItem which represented our document using the code snippet below:

// First: Open new SPSite and new SPWeb with using statements

// Then: Fetch a new SPList reference as list with the code below

// Be safe: get the internal name of the built-in ID column

string idInternalName = list.Fields["ID"].InternalName;

// Get the ID of your item from the Workflow properties

int id = workflowProperties.ItemId;

// Construct a new SPQuery object and write the CAML

SPQuery query = new SPQuery();

query.Query = "<Where><Eq><FieldRef Name='" + idInternalName + "' /><Value Type='Text'>" + id + "</Value></Eq></Where>";

// Run the SPQuery against your new SPList reference

SPListItemCollection itemCol = list.GetItems(query);

// Your new SPListItem reference!

SPListItem item = itemCol[0];

Use the New SPListItem Reference

After getting your new object reference (remember, this is actually the same document your workflow is already running on), use this object instance to populate your serializable workflow class variables to evaluate in the For Loop Activity (and prevent the workflow from stalling):

this._status = item["HR Department Approval"].ToString(); // Replace "HR Department Approval" with your field

While this all seems a bit overkill for fetching one little string value, it does the trick and your workflow can continue running to start the next SharePoint Approval Workflow in the chain.