Sharepoint 2007 workflow vs powershell script

Question

In a Sharepoint 2007 Document List, I have a need where I have a column that is an expiry date, then another column that is an indicator if the expiry date has passed.

I have spoken to the end user and simply having additional views to show just documents that have expired is not good enough - they want the column to show the word "Expired".

I can achieve this by having a workflow on the item created event that simply waits for the date to be less than today. This will run the workflow and the work flow will remain in the "In progress" state until the item expires (which could be 5 years)

I can also do this via powershell and run a scheduled task every night to iterate all the items in the list and manually set the column text if the criteria is met.

My question is which is best as this document list will have over 100,000 documents. Is there any impact on having workflows run for 5 years.. and lots of them at that?

Both options sound overly complex and failure prone; use a calculated column or a sprig of JavaScript to show if the current date is past the expired date or not, at view time. — TessellatingHeckler
Unfortunately calculated columns are only calculated on creation or on edit of an item - I have seen javascript "hacks" but they only work on 2010 onwards. — DaGeezah
In the article I linked from RackSpace, item 4, use that approach to make a calculated column with HTML which includes JavaScript, in pseudocode =concatenate("<javascript>if ( date() >", ExpiryDate, ") { "expired" }</javascript>, or to insert an image link which loads off a simple web service <img src='http://server/isExpired.aspx?date=' + ExpiryDate /> and the service serves up an expired/valid image as appropriate. ? — TessellatingHeckler
I have read that exact same post - and tried this before posting... either way it doesn't work in 2007, just 2010 onwards — DaGeezah

Thriggle Thriggle · Accepted Answer · 2016-08-24T18:14:50

Having ten thousand workflows paused and waiting for five years is a bad idea. Not only would that put unnecessary load on your workflow timer service, but you'd run the risk of all ten thousand workflows entering an error state if your workflow timer service stops unexpectedly at some point within those five years, which would force you to terminate and restart all 10,000 workflows to keep the process going.

A scheduled task triggering the execution of a console app or a Powershell script is a better approach, or you could write your own SharePoint timer job in Visual Studio. Either way would use the SharePoint server-side object model to query the list, by means of the SPQuery object. The important thing when using the SharePoint server-side object model is to use the most efficient queries possible.

You do not need to iterate through all the items in the list, ever. Your CAML query should be specific enough to only return the subset of items where the indicator column is not equal to "Expired" and the expiry date is less than today's date.

Add column indexing to both of those columns to reduce the strain of that query on your database.

Sharepoint 2007 workflow vs powershell script

1 Answers