I know, that title is very exciting right? Can’t wait to read more? Here is what it all means…
I had a need to create a “Days Open” column in an InfoPath Form Library that would display the amount of days a case has been open. This part is simple, I used a DATEDIF function in a Calculated column to calculate the amount of days between the “Created” date and another column called “Todays Date”.
That’s great, and it works – as long as each list item is updated daily; but that’s obviously not the case. I need the “Todays Date” column to be updated daily regardless of whether or not the list items are touched by the assignee. It seems like this kind of functionality should be out of the box – a date column with a default value of the [today] function should always be correct – right? Wrong!
I thought of a few different ways to do this, one being a custom workflow to run which would set “Todays Date” to [today] – but I still need a way for it to run daily. I could probably do that with a policy on the library. I could probably get a developer to write a timer job to kick-off the workflow daily too.. I could probably do it a bunch of ways…
But – I’m a PowerShell dude!
Sooo, here’s what I did do…
- Create a column called “Todays Date” which is a Date/Time column with a default value of [today]
- Create a column called “Days Open” which has the following calculated formula: =DATEDIF(Created,TodaysDate,”D”)
- Create the following PowerShell Script, save it as Update-SPListItemsWithTodaysDate.ps1 (Note: I also preserve the ‘Modified By’ values – as described in this post:
Start-SPAssignment -Global $SPWeb = Get-SPWeb http://site $List = $SPWeb.Lists["My Form Lib"] $Items = $List.Items foreach ($item in $items) { $modifiedBy = $item["Editor"] $formStatus = $item["Form Status"] if ($formStatus -ne "Rejected" -and $formStatus -ne "Completed") { $item["TodaysDate"] = Get-Date $item["Editor"] = $modifiedBy $item.Update() $list.Update() } } $SPWeb.Dispose() Stop-SPAssignment -Global
- Create a Windows Scheduled Task on my server to run daily at 12:05 AM. This scheduled task is configured to launch powershell.exe and my Update-SPListItemsWithTodaysDate.ps1 script.
Note: I’ve added logic to my script so that if a column called “Form Status” equals “Rejected” or “Completed” then the script will ignore those items… Obviously adjust all of the values to your environment…
RD
You must be logged in to post a comment.