How to Update the Formula Field with the Formula on SharePoint 2013 using PowerShell


Sathish Nadarajan
SharePoint MVP
Published On :   25 Aug 2015
Visit Count
Today :  1    Total :   7416
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


In Many situations, the formula fields will get updated based on the requirement change. For example, during the initial development phase, the formula for a field would have been assumed as something. During a period of time, this may get changed. At that time, we cannot delete and recreate this field, as this may cause a lot of effort. In that case, we need to update the field. Let us see how to do that using PowerShell.

##================================================================================================

## Description : Update the Site Columns

## Author : Sathish Nadarajan

## Date : 13-Aug-2015

##================================================================================================

# ============================================ Setup Input Paths ===========================================================

$Host.UI.RawUI.WindowTitle = "-- Update SiteColumns --"

$StartDate = Get-Date

Write-Host -ForegroundColor White "------------------------------------"

Write-Host -ForegroundColor White "| Update SiteColumns |"

Write-Host -ForegroundColor White "| Started on: $StartDate |"

Write-Host -ForegroundColor White "------------------------------------"

$LogTime = Get-Date -Format yyyy-MM-dd_hh-mm

$LogFile = ".\UpdateSiteColumn-$LogTime.rtf"

#start-transcript $logfile

$scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent

Set-Location $scriptBase

$ErrorActionPreference = "Stop"

################################# Functions #####################################################

function AddPowerShellSnapin()

{

try

{

Write-Host "Adding PowerShell Snap-in" -ForegroundColor Green

# Try to get the PowerShell Snappin. If not, then adding the PowerShell snappin on the Catch Block

Get-PSSnapin "Microsoft.SharePoint.PowerShell"

}

catch

{

if($Error[0].Exception.Message.Contains("No Windows PowerShell snap-ins matching the pattern 'Microsoft.SharePoint.PowerShell' were found"))

{

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

}

}

Write-Host "Finished Adding PowerShell Snap-in" -ForegroundColor Green

}

Function UpdateField ([string]$siteUrl)

{

$site = Get-SPSite -Identity $siteUrl

$web = $site.RootWeb

write-host "Updating the Formula" -fore yellow

$column = $web.Fields.GetFieldByInternalName("MyFormulaField")

if($column)

{

$column.Formula = "=TEXT([MyOtherField],""yyyy"")"

$column.update($true)

}

$web.Dispose()

$site.Dispose()

}

################### End of Functions ###############################################

try

{

AddPowerShellSnapin

$SiteColumnUpdateDetailsCSV = $scriptBase + "\" + "SiteColumnUpdateDetails.csv"

import-csv $SiteColumnUpdateDetailsCSV | where {

UpdateField $_.siteurl

}

Write-Host "Script Execution Completed Successfully" -ForegroundColor Green

}

catch

{

Write-Host "Custom Exception Happened on Main : " + $Error[0].Exception.Message -ForegroundColor Red

}

#stop-transcript

 ##================================================================================================
 ## Description	: Update the Site Columns 
 ## Author		: Sathish Nadarajan
 ## Date			: 13-Aug-2015
 ##================================================================================================
 
 # ============================================ Setup Input Paths ===========================================================
 
  
 $Host.UI.RawUI.WindowTitle = "-- Update SiteColumns --"
 
 $StartDate = Get-Date
 Write-Host -ForegroundColor White "------------------------------------"
 Write-Host -ForegroundColor White "| Update SiteColumns |"
 Write-Host -ForegroundColor White "| Started on: $StartDate |"
 Write-Host -ForegroundColor White "------------------------------------"
 
 $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
 $LogFile = ".\UpdateSiteColumn-$LogTime.rtf"
 
 #start-transcript $logfile
 
 $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
 Set-Location $scriptBase
 
 $ErrorActionPreference = "Stop"
 
 
 
 ################################# Functions #####################################################
 
 function AddPowerShellSnapin()
 {
     try
     {
         Write-Host "Adding PowerShell Snap-in" -ForegroundColor Green
         # Try to get the PowerShell Snappin.  If not, then adding the PowerShell snappin on the Catch Block
         Get-PSSnapin "Microsoft.SharePoint.PowerShell" 
     }
     catch
     {
         if($Error[0].Exception.Message.Contains("No Windows PowerShell snap-ins matching the pattern 'Microsoft.SharePoint.PowerShell' were found"))
         {
             Add-PSSnapin "Microsoft.SharePoint.PowerShell"
         }
     }
     Write-Host "Finished Adding PowerShell Snap-in" -ForegroundColor Green
 
 }
 
 
 Function UpdateField ([string]$siteUrl) 
 {
     $site = Get-SPSite -Identity $siteUrl
     $web = $site.RootWeb 
 
     
     
 
     write-host "Updating the Formula"  -fore yellow
 
     $column = $web.Fields.GetFieldByInternalName("MyFormulaField")
         if($column)
         {
             
             $column.Formula = "=TEXT([MyOtherField],""yyyy"")"
             $column.update($true)
         }
         
    
         
     $web.Dispose()
     $site.Dispose()
 }
 
 
 ################### End of Functions ###############################################
 
  
 try
 {
      AddPowerShellSnapin
      
     $SiteColumnUpdateDetailsCSV = $scriptBase + "\" + "SiteColumnUpdateDetails.csv"
 
     import-csv $SiteColumnUpdateDetailsCSV | where {
         UpdateField $_.siteurl 
     }
     Write-Host "Script Execution Completed Successfully" -ForegroundColor Green 
 }
 catch
 {
     Write-Host "Custom Exception Happened on Main : " + $Error[0].Exception.Message -ForegroundColor Red  
 }
 
 #stop-transcript
 

The script is self-explanatory and does not require any explanations I guess.

Happy Coding,

Sathish Nadarajan.

SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

KWizCom Forms App