Skip to content

SharePointRyan

Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

Tag: Preserve Metadata

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

December 2, 201113 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

Share:

  • Tweet

Like this:

Like Loading...

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

December 2, 20114 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!

Share:

  • Tweet

Like this:

Like Loading...

Most Popular Posts

  • Retrieve SharePoint Groups using PowerShell
  • Create SharePoint Groups using PowerShell
  • Get SharePoint Site Quota Templates using PowerShell

Tag Cloud

Access Denied August 2011 CU Bulk Page Creation Bulk Site Creation content organizer Content Types Convert to Lowercase Correlation ID Create SharePoint Quota Template using PowerShell CSOM CSS Custom 404 DAYSPUG Document Management Dog Food Conference ECM Fileshare ForEach-Object Generic nonhelpful errors Get-Random Get-SPGroup Get-SPSite Get-SPWebApplication Heartland Region SharePoint Conference InfoPath InfoPath 2010 List Items List Settings Managed Metadata Master Page Microsoft.SharePoint.PowerShell Microsoft Heartland Region New-SPGroup New-SPPage New-SPWebFromXml Object Model Office 365 PowerShell PowerShell;SharePoint 2010; New-SPList;SP2010 Object Model PowerShell Saturday 002 Preserve Metadata Print JavaScript Print List View Print SharePoint Page Publishing Pages Quota Templates Records Management Remove-SPGroup REST Service Pack 1 Set-SPCustomLayoutsPage Set-SPPortalSiteUrl Setup-SPWebAppAndSite SharePoint SharePoint 2010 SharePoint 2013 SharePoint Automation SharePoint List SharePoint Online SharePoint Online PowerShell SharePoint Saturday Site Collection Site Collection Templates SP2013 Speaking Engagements SPQuotaTemplate SPSCincinnati SPSDayton SPSite SPWeb Subsite toLower() user adoption Workflow XML

Recent Tweets

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Facebook

Facebook

RSS

  • RSS - Posts
  • RSS - Comments

Blog Stats

  • 481,434 hits

SharePointRyan

Ryan Dennis is a SharePoint Solution Architect with a passion for SharePoint and PowerShell

Footnotes

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com
Blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • SharePointRyan
    • Join 64 other followers
    • Already have a WordPress.com account? Log in now.
    • SharePointRyan
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.

    %d bloggers like this: