Thursday, August 27, 2015

Update Calculated Column Formula in SharePoint List using PowerShell

Requirement: Update a Calculated Field's formula for a specific column of a list across entire site collection.

PowerShell to update calculated field formula in SharePoint:
This updates the site column's formula for all subsites in the site collection.

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$SiteURL = "http://intranet.crescent.com"
$ColumnName="Productivity"

#Iterate through each web in given site collection
$Webs = Get-SPSite $SiteURL | Get-SPWeb -Limit All | ForEach-Object {
    
    #Check if the column exists
    if($_.Fields.ContainsField($ColumnName))
    {
        $column = $web.Fields[$ColumnName]
        #Update calculated field formula
        $Column.Formula = "=(([PlannedEffort]/5)/ActualEffort)*8"
        $Column.update($true)
        write-host "Updated Calculated Field formula at $($_.URL)"
    }
}
Same code can be used for List level formulas as well.

PowerShell to Update Calculated Column formula in SharePoint List:
In an another case, we had to replace a field in the calculated column's formula for all sites.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Variables
$SiteURL = "http://intranet.crescent.com"
$ListName="Projects"
$ColumnName="Productivity"
$OldField="PlannedEffort"
$NewField="EstimatedHours"

#Iterate through each web in given site collection
$Webs = Get-SPSite $SiteURL | Get-SPWeb -Limit All | ForEach-Object {
    
    #Get the List
    $List = $_.Lists.TryGetList($ListName)

    If($List)
    {
        #Check if the column exists
        if($List.Fields.ContainsField($ColumnName))
        {
            $Column = $List.Fields[$ColumnName]
            #Update calculated field formula
            $Column.Formula = $Column.Formula.Replace($OldField,$NewField)
            $Column.update($true)
            write-host "Updated Calculated Field formula at $($_.URL)"
        }
    }
} 
PowerShell to Update Calculated Column Formula in SharePoint

Creating calculated field using PowerShell is in my another article: How to Add Calculated Column to SharePoint List using PowerShell



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...