PowerShell Script: Fix a SharePoint Lookup Field

Sometimes, normally after deploying a field, the List Id or the Web Id of the field definition can be wrong. Maybe its pointing to your Dev or UAT environment. In any case I've had to on a number of occasions fix the schema to point to the new id's.

Here is a script that uses CSOM to correct the ID's.

#PowersHell

# Fix the List Id or Web Id in a Lookup Column using CSOM.  Works with 2013 and SP Online.  # Not tested with 201
# Author: Tim Wheeler 2018 http://blog.timwheeler.io
# Note: Download appropriate CSOM version from Nuget
param(
    $siteUrl = $("https://somesite.sharepoint.com/sites/site"),
    $spOnline = $($true),
    $username = $(""),
    $password = $($null),
    $pathToCSOM = $($PSScriptRoot + "\..\CSOM\16"),
    $fieldInternalName = "InternalName",
    $lookupListName = $("ListTitle"),
    $readOnly = $false
)

Add-Type -LiteralPath ([IO.Path]::Combine($pathToCSOM, "Microsoft.SharePoint.Client.dll")) -PassThru | out-null #Load the CSOM assembly
function Get-SPOnlineContext($url, $username,$password)
{
    if($password -eq $null )
    {
        $psCredential = Get-Credential $username
        $username  = $psCredential.GetNetworkCredential().UserName
        $password = $psCredential.GetNetworkCredential().Password
    }
    $securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force
    $context = New-Object -TypeName Microsoft.SharePoint.Client.ClientContext -ArgumentList ($url)
    $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username,$securePassword) -ErrorAction:Stop
    return $context
}
function Get-SP2013Context($url, $username, $password)
{
    $context = New-Object -TypeName Microsoft.SharePoint.Client.ClientContext -ArgumentList ($url)
    if(-not ([string]::IsNullOrEmpty($username) -and [string]::IsNullOrEmpty($password)))
    {
        $securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force
        $context.Credentials = New-Object System.Net.NetworkCredential($username, $securePassword) -ErrorAction:Stop
    }
    return $context
}


try {
    $context = $null
    if($spOnline)
    {
        $context = Get-SPOnlineContext $siteUrl $username $password 
    }
    else {
        $context = Get-SP2013Context $siteUrl $username $password 
    }

    $web = $context.Web
    $context.Load($web)
    [Microsoft.SharePoint.Client.List] $list = $web.Lists.GetByTitle($lookupListName)
    $field = $web.Fields.GetByInternalNameOrTitle($fieldInternalName)
    $context.Load($field)
    $context.Load($list)
    $context.Load($web.Fields)
    
    $context.ExecuteQuery()
    $schema = $field.SchemaXml
    write-host "Current Schema:" $schema
    [Xml]$schemaXml = $schema
    $requiresUpdate = $false
    if($schemaXml.Field.Attributes["WebId"].'#text'.Replace("{","").Replace("}","") -ne $web.Id)
    {
        Write-Information "Found issue with Web Id, Is: ${$schemaXml.Field.Attributes["WebId"].'#text'} should be ${$web.Id}"
        $schemaXml.Field.Attributes["WebId"].'#text' = $web.Id.ToString()
        $requiresUpdate = $true
    }
    if($schemaXml.Field.Attributes["List"].'#text' -ne $list.Id.ToString())
    {
        "Found issue with List, Is: $($schemaXml.Field.Attributes["List"].'#text') should be $($list.Id.ToString())"
        $schemaXml.Field.Attributes["List"].'#text' = $list.Id.ToString()
        $requiresUpdate = $true
    }
    

    if($requiresUpdate -and $readOnly -eq $false)
    {
        $schema = $schemaXml.OuterXml
        write-host "New Schema:" $schema
        $field.SchemaXml = $schema
        $field.Update()
        #$web.Fields.Update()
        $context.ExecuteQuery()
    }
    else {
        write-host "No changes made"
    }
    

}
finally {
    if ($context -ne $null) {
        $context.Dispose()
        $context = $null
    }
}