How do I handle expiration notification workflows?

SharePoint Standard and Enterprise versions contain the ability to implement retention policies.  These retention policies can delete an item/document after a certain period of time, move the item/document to a long term storage location, or even initiate a workflow on the item.

For users who have this functionality, creating an expiration notification for something like a certification is relatively easy.  Simply create the notification workflow that sends an email if the certification expiration date is less than the current date.  Then, setup a retention policy to kick off the workflow.  However, for SharePoint Foundation users, this becomes a little tricky.

The easiest way I have found is to create a PowerShell script and run it from a nightly timer job.  The script would essentially fire the workflow for each of the items in the target list created by U-LINC.  The workflow would look to see if the expiration date of the item is within the specified timeframe and if so, send an email.

Here is an example of a script to kick off a workflow for every item in a list:

Add-PSSnapin Microsoft.SharePoint.PowerShell –ErrorAction SilentlyContinue
$site = Get-SPSite http://tfranz6
$web = Get-SPWeb -Identity http://tfranz6/u-linc/department
$list = $web.Lists["Vendor"]
$wf = $list.WorkflowAssociations[0]
$result = $list.Items
foreach ($r in $result)
{
 $site.WorkflowManager.StartWorkflow($r,$wf,$wf.AssociationData)
}

In this script, you would need to modify the URL to your root SharePoint site and the site which contains the target list.  Additionally, you would need to change the list name.  Finally, you will need to determine the correct ID for the workflow association.  If you only have one workflow tied to the list, this is most likely "0" (e.g. $list.WorkflowAssociations[X]).  If you are unsure about the workflow association ID, you can find it by running the following script, incrementing the index ("X") until you find the correct ID.  There is probably a better way to do this, I just didn't find it easily.

$site = Get-SPSite http://tfranz6
$web = Get-SPWeb -Identity http://tfranz6/u-linc/department
$list = $web.Lists["Vendor"]
echo $list.WorkflowAssociations[0].Name

Once you have your PowerShell script created, simply save it with the .ps1 extension.  You can then create a batch file that launches PowerShell and executes your script.

powershell -noexit -file "c:\ExecuteNotificationWorkflows.ps1"

You can then call your batch file from a Task Scheduler job (Control Panel > Administrative Tools > Task Scheduler).

This approach should work with all of our timed notification workflow templates.

Now that' just cool, I don't care who ya are!