Showing Workflow History After 90 Days

One of my clients recently had an issue with their Project Management system built on SharePoint.

Links to workflow history is, by default, removed after 60 days by a timer job. Unfortunately, there is no way to control this timer job (e.g. like a control list or Central Admin setting), and the guidance from MS is

turning off the timer job can have performance impacts.

However, the client required that the workflow history be easily accessible, mainly due to auditing requirements, and also due to user education requirements. I totally agree with the last one: we should NOT

be requiring our customers/users to have to jump through hoops to see history for a workflow. To me, this is just bad practice.

So, after a lot of Googling, and piecing together a few things, I came up with a solution that did not require a WSP or custom code solution, and instead relied on the out of the box SharePoint components to give a solution

Solution Overview

So what we were going for was rather than force users to do the 3-click process of viewing the workflows for an item, the client wanted an easier solution. What we provided was an extra column in a view that navigated

the user to the Workflow History list filtered on the ID of the item. This provided a very intuitive way for users to be able to view the Workflow Audit history for an item in one click:

Setting Up

So, to set this up, we need to do the following:

Create a Text Site Column to hold the Items ID

We cannot use the ID of the item directly. This is because we are going to be using a Calculated field to display a hyperlink to the Workflow History list filtered on the item's ID. Whenever a workflow task runs and updates the item, it will, for whatever reason, make the calculated field invalid, as the ID will not be used correctly. I have seen a few blog post that state that using the ID of an item in a Calculated field is not recommended and is unreliable (e.g. http://sharepoint.stackexchange.com/questions/22977/how-to-use-calculated-field-to-retrieve-the-id-of-the-item-in-the-sharepoint-200)

  1. Add the Site Column created above to the Document Library/List in question
  2. Create a new Calculated Column in the target Document Library/List called AuditWorkflowHistory with the following formula (Do not worry too much - I will give the full PowerShell script later to show how to set this dynamically):
=IF ([ItemID] <> "", "<a href='http://<pathtowebsite/Lists/Workflow%20History/AuditView.aspx?FilterField1=Item&FilterValue1=<itemID>>Workflow History</a>","")

 

  1. Create a new View in the target Document Library/List that includes the fields you want to see, as well as the AuditWorkflowHistory create above
  2. Create a new Global Workflow that will set the ItemID to the Item's ID on creation. Beware, however, that the workflow will need to put a pause in to cater for any potential edits and other workflows running. This may just be my system, but I have seem this on more than 1 occasion where multiple workflows on the same event cause all sorts of issues. I suspect that the workflow engine for 2010 just does not handle multiple workflows updating the same item at the same time terribly well. I would love to be proven wrong on this
  3. Associate the new workflow to the list
  4. Trigger the new workflow on existing content to force the ItemID to be set to something

In isolation, this is perhaps not a big deal. However, in my case, we are talking about a solution that has already been deployed, and there are existing documents and workflows in place. Writing code and producing a solution just did not "smell" right in this instance, particularly since I am loathe to write custom solutions (not because I do not want to - just that the clients I deal with have invested a large amount of money in a SharePoint environment, and I feel that forcing them to then invest further into custom development as a waste of money - you may as well have invested in building your own Web app - a rant for another day perhaps)

So the solution that I present here is a PowerShell solution, including a way to retract the changes if necessary ( a requirement if you are testing)

Creating the Text Column Using PowerShell

The PowerShell given below will crate a new Site Column to hold the ID of the item:

$siteColUrl = "<URL of your Site Collection>"
$fieldType = "Text"
$fieldName = "ItemID"
$fieldDescription = "Used to hold the Item ID for use in the Audit Workflow Calculated Field. Ensure a workflow updates this field on create/update"
$fieldDisplayName = "ItemID"
$fieldStaticName = "ItemID"
$fieldGroup = "<My Group Name>"
$fieldHidden = "FALSE"
$fieldRequired = "FALSE"
$fieldSealed = "FALSE"
$fieldShowInDisplayForm = "FALSE"
$fieldShowInEditForm = "FALSE"
$fieldShowInListSettings = "TRUE"
$fieldShowInNewForm = "FALSE"
$fieldFormula = ""

$site = Get-SPSite $siteColUrl
$rootWeb = $site.RootWeb
# Assign fieldXML variable with XML string for Site Column
$fieldXML = '<Field Type="'+$fieldType+'"
Name="'+$fieldName+'"
Description="'+$fieldDescription+'"
DisplayName="'+$fieldDisplayName+'"
StaticName="'+$fieldStaticName+'"
Group="'+$fieldGroup+'"
Hidden="'+$fieldHidden+'"
Required="'+$fieldRequired+'"
Sealed="'+$fieldSealed+'"
ShowInDisplayForm="'+$fieldShowInDisplayForm+'"
ShowInEditForm="'+$fieldShowInEditForm+'"
ShowInListSettings="'+$fieldShowInListSettings+'"
ShowInNewForm="'+$fieldShowInNewForm+'"></Field>'

$rootWeb.Fields.AddFieldAsXml($fieldXML)

Add The New Column to Appropriate Document Libraries

In my situatation, I needed to add this column to a large number of existing document libraries. Luckily, all the sub-sites had the same document library name, which made this task a bit easier. Pleas note the escape sequences I had to use to get this to work with PowerShell. This was by far the hardest part of this:

foreach($web in $site.AllWebs) {
 #Create the CalculatedField for the Audit View Link
 Write-Host "Processing" $web.Title -ForegroundColor Green
 $list = $web.Lists["<DISPLAY NAME OF TARGET DOCUMENT LIBRAY>"]
 if(!$list.Fields.ContainsField($fieldDisplayName))
 {
  $addCol = $site.RootWeb.Fields[$fieldDisplayName]
  $list.Fields.Add($addCol)
 }
 if (!$list.Fields.ContainsFieldWithStaticName("AuditWorkflowHistory"))
 {
  $list.Fields.Add("AuditWorkflowHistory","Calculated",0)
  $field = $list.Fields["AuditWorkflowHistory"]
  $field.Description = "Contains a link to any Workflow History for the item"
  $field.Title = "Workflow History"
  $formula = "=IF([ItemID] <> `"`",`"<a href='" + $web.Url + "/Lists/Workflow%20History/AuditView.aspx?FilterField1=Item&FilterValue1=`"&[ItemID]&`"'>Workflow History</a>`",`"`")"
  $field.Formula = $formula
  $field.OutputType = "Number"
  $field.Update()
 }
 $list.Update()
}

 

Create a New View in the Target Document Library

The next step is to create a new View in the document libraries to allow users to easily select eh workflow history. Once again, I was faced with an existing solution that needed to be managed:

foreach($web in $site.AllWebs) {
$list = $web.Lists["<DISPLAY NAME OF TARGET DOCUMENT LIBRAY>"]
#Create the new Views
#Create the Audit History View in the PDS Document Library
# Audit Workflow
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
$awViewTitle = "Audit Workflow"
$awViewFileName = "AuditWorkflow"
$awViewFields = New-Object System.Collections.Specialized.StringCollection
$awViewFields.Add("DocIcon") > $null
$awViewFields.Add("LinkFilename") > $null
$awViewFields.Add("PMSLastApprovedBy") > $null
$awViewFields.Add("PMSLastApprovedDate") > $null
$awViewFields.Add("_ModerationStatus") > $null
$awViewFields.Add("View_x0020_Workflow_x0020_Status") > $null
$awViewFields.Add("_UIVersionString") > $null
$awViewFields.Add("PMSWorkflowStatus") > $null
$awViewFields.Add("AuditWorkflowHistory") > $null

 #Query property
#$awViewQuery = "<Query><GroupBy Collapse=`"FALSE"" GroupLimit=`"30`"><FieldRef Name=`"PMSPhases`"/></GroupBy><OrderBy><FieldRef Name=`"Title`"/><FieldRef Name=`"Modified`"/></OrderBy></Query>"
$awViewQuery = "<GroupBy Collapse=`"FALSE`" GroupLimit=`"30`"><FieldRef Name=`"PMSPhases`" /></GroupBy><OrderBy><FieldRef Name=`"Title`" /><FieldRef Name=`"Modified`" /></OrderBy>"
#RowLimit property
$awViewRowLimit = 50
#Paged property
$awViewPaged = $true
#DefaultView property
$awViewDefaultView = $false
$oldView = $list.Views[$awViewTitle]
if($oldView -ne $null)
{
$list.Views.Delete($oldView.ID)
}
$newview = $list.Views.Add($awViewFileName, $awViewFields, $awViewQuery, $awViewRowLimit, $awViewPaged, $awViewDefaultView)
$newView.Title = $awViewTitle
$newView.Scope = [Microsoft.SharePoint.SPViewScope]::Recursive
$newView.Update()

#Create the new view in the Workflow History list
$wfList = $web.Lists["Workflow History"]
$avViewTitle = "Audit View"
$avViewFileName = "AuditView"
$avViewFields = New-Object System.Collections.Specialized.StringCollection
$avViewFields.Add("Occurred") > $null
$avViewFields.Add("User") > $null
$avViewFields.Add("Event") > $null
$avViewFields.Add("Outcome") > $null
$avViewFields.Add("Description") > $null
$avOldView = $wfList.Views[$avViewTitle]
if($avOldView -ne $null)
{
$wfList.Views.Delete($avOldView.ID)
}
$avNewview = $wfList.Views.Add($avViewFileName, $avViewFields, $avViewQuery, $avViewRowLimit, $avViewPaged, $avViewDefaultView)
$avNewView.Title = $avViewTitle
$avNewView.Update()
$list.Update()
$web.Update()
}

 

Create New Workflow to Update the ItemID On Creation

To enable the Calculated column to work, we need to create a workflow that updates the ItemID column of the associated list to the ID of the associated item. This needs to be done at creation, with a delay of 1 minute (as a minimum - your millage may vary)

msohtmlclipclip_image001

I will leave it as an exercise for the reader to create such a simple 2010 Global workflow

Associate New Workflow with Document Library

The next step is to associate the new workflow created above with the relevant document library. Once again, I was faced with an existing system, so the PowerShell below reflects that:

foreach($web in $site.AllWebs) {
 #Create the CalculatedField for the Audit View Link
 Write-Host "Processing" $web.Title -ForegroundColor Green
 $list = $web.Lists["<DISPLAY NAME OF TARGET DOCUMENT LIBRAY>"]
 #Deploy new Workflow to each document library --> set to run on create/update
 $template = $web.WorkflowTemplates.GetTemplateByName("SetItemID",[System.Threading.Thread]::CurrentThread.CurrentCulture);
 $taskList = $web.Lists["Tasks"];
 $historyList = $web.Lists["Workflow History"];
 if($historyList -eq $null)
 {
  $web.Lists.Add("Workflow History", "Workflow History","Lists/Workflow History", "00BFEA71-4EA5-48D4-A4AD-305DE7030140", 140, "101");
  $historyList = $Web.Lists["Workflow History"];
 }
 $association=[Microsoft.SharePoint.Workflow.SPWorkflowAssociation]::CreateListAssociation($template, "Set Item ID", $taskList, $historyList);
 $association.AllowManual = $true;
 $association.AutoStartChange = $false;
 $association.AutoStartCreate = $true;
 $workflow = $list.WorkflowAssociations.Add($association)
 if($site -ne $null)
 {
  Write-Host "Workflow has been attached successfully." -foregroundcolor "Green";
  $list.Update();
 }
 else
 {
  Write-Host "Workflow could not be attached." -foregroundcolor "Yellow";
 }
}

 

Force New Workflow To Run On All Items

Now that the workflow has been associated with the document library, we now need to run the workflow on all existing items. You may not need to do this on a blank system, but in my case, I had to:

$site = Get-SPSite $siteColUrl
foreach($web in $site.AllWebs){
 $list = $web.Lists["<DISPLAY NAME OF TARGET DOCUMENT LIBRAY>"]
 #Run the new workflow across all items in the document library
 $manager = $web.Site.WorkFlowManager
 $assoc = $list.WorkflowAssociations.GetAssociationByName("Set Item ID",[System.Threading.Thread]::CurrentThread.CurrentCulture)
 $data = $assoc.AssociationData
 $items = $list.Items
 foreach($item in $items)
 {
  try {
   $wf = $manager.StartWorkFlow($item,$assoc,$data,$true)
  }
  catch (exception ex){
   Write-Host $item.Title + " - " $item.Url -ForegroundColor Red
  }
 }
 $manager.Dispose()
 $web.Dispose()
}

Richard

Richard is a Director and the principal Consultant at Dev iQ Pty Ltd. He specialises in SharePoint, Team Foundation Server/Visual Studio and .NET Development.

Subscribe to richard angus

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!