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.
Leave a comment