Tag: Preserve Metadata

Use PowerShell to update a [today] column daily using a Scheduled Task

13 Comments

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…

  1. Create a column called “Todays Date” which is a Date/Time column with a default value of [today]
  2. Create a column called “Days Open” which has the following calculated formula: =DATEDIF(Created,TodaysDate,”D”)
  3. 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
  4. 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

Advertisement

Preserve “Modified By” values while editing List Items using PowerShell

4 Comments

Today I was working on a simple PowerShell script to update a Date/Time column in SharePoint 2010 – I simply needed to iterate through each list item; updating the value of a column to be today’s date.

As I was doing that, I thought “it sure would be nice if I could preserve the ‘modified by’ person while updating each of these list items.”

Well not only is this possible, but it’s almost too easy!

Here is a screenshot of my list before touching it with PowerShell, two list items – one created by the System Account and one created by me:

What I’m going to do using PowerShell is modify the “Title” of the second list item – the one created by me. However, I’m going to run this code on the server with the farm account.

The key piece is storing the value of the “Modified By” column into a variable so I can then put the value back into the column when I’m done.

Here is the simple PowerShell code to do this:

$web = Get-SPWeb http://siteurl
$list = $web.lists["List Name"]
$item = $list.items.getitembyid(2)
$modifiedBy = $item["Editor"] #Editor is the internal name of the Modified By column
$item["Title"] = "Modified in PowerShell"
$item["Editor"] = $modifiedBy
$item.Update()
$list.Update()
$web.Dispose()

And finally, here’s a screenshot after doing the PowerShell modification:

Cool!