I recently had to build a workflow in SharePoint Designer to model a fairly lengthy approval process for policy documents involving multiple stages of review, each of which has to be passed before the draft document moves to the next review stage. The workflow itself was quite long but essentially boiled down to a series of repeated steps. Each step involved assigning a task to a reviewer (or reviewers) and then updating the status of the document to show what stage it was at. The Policies document library has enforced check in/out, version control, and content approval all turned on. I also needed a field called Review Stage to show what stage the document was at – that was a simple choice field with 7 different stages.
The obvious thing was to add the Review Stage field to the document library. After some playing around with the workflow, however, I decided to do things a more complex way. I created a new list called Document Review Stages with two fields, one was the choice field Review Stage and the other was a number field called DocumentID. For each policy document in the document library, there is a corresponding entry in the Policy Review Stages list. The DocumentID holds the ID (the built-in column that SharePoint populates automatically) of the policy document. In other words, I effectively created a one-to-one relationship between the Policies document library and the Policy Review Stages list. Of course, this being SharePoint and not a full-blown database, this isn’t a proper relationship – there’s no enforced referential integrity, no cascading updates, etc. But the basic principle is the same.
So why did I do it in this convoluted way? Well because the Policies document library has enforced check-in/out and major/minor version control. So suppose I had the Review Stage column on the Policies document library itself. That would mean any change to the document, even just changing its metadata, would create a new version. And that means that the workflow, if it wants to progress the document to the next review stage, would have to first check out the document, then set the Review Stage field, then check the document back in again. And you’d then have 2 versions of that document, both exactly the same in content, but with different Review Stage metadata values. This would result in a confusing version history for each document, with some genuinely different versions of the document (edited by different reviewers) but also many identical copies of each document created automatically by the workflow as it moves the document through the review stages. By moving the Review Stage column to a different list, the workflow can update the Review Stage field without creating a fresh copy of the document in the version history. That makes the version history much cleaner and more readable.
This being a SharePoint Designer workflow, not a Visual Studio one, I had to attach it to the Policies document library. I got the workflow to find the Policy Review Stages list item related to its corresponding policy document, and update the Review Stage column on the list. So far so good, but then right at the end of the workflow, the last step was to make the document go live by setting its approval status to be “Approved”. There’s a special workflow action to do just that, so it seems easy enough. But there’s a catch. If you want to make the document Approved status, you have to change it, and the workflow complains if you try to change it without first checking it out. OK, so you change your workflow to check the document out first, and then approve it. But now the workflow complains because you can’t approve a document that is checked out. Catch-22!
So how do you get round this? Well you can use the Start Another Workflow action to start the normal Approval workflow on the document. The built-in Approval workflow can work with a document that’s not checked out. So you end your workflow early, before the final approval task is allocated, and hand off control to a standard Approval workflow to manage the final approval step.