Repair Lookup Column In SharePoint Online Using PowerShell

This article will help you to repair the lookup column in SharePoint online list.

This script will help you to repair the lookup column in child list which is broken during migration.

 

Suppose the lookup column looks like this,

 

 

 

To download SharePoint Online SDK and pass the credential online then please refer to my previous blog, Copy List Items from One Site to Another With Versions using Powershell

 

Then copy paste the powershell script and run it. 

#Load SharePoint CSOM Assemblies
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'


$siteUrl="Your Corrupt Column site Url"
$listName="Your Corrupt Column Listname"
$columnName="Your Corrupt Column Name"
$lookupWebURL="Lookup Site Url"
$lookupListName="Lookup List Name"

RepairListLookupColumns $siteUrl $listName $columnName $lookupWebURL $lookupListName

Function RepairListLookupColumns()
{
     param
    (
        [Parameter(Mandatory=$true)] $siteUrl,
        [Parameter(Mandatory=$true)] $listName,
        [Parameter(Mandatory=$true)] $columnName,
        [Parameter(Mandatory=$true)] $lookupWebURL,
        [Parameter(Mandatory=$true)] $lookupListName
    )
    #Passing Credentials
    $credPath = 'D:\Arvind\safe\secretfile.txt'
    $fileCred = Import-Clixml -path $credpath
    $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)

    #Get site context
    $siteCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
    $siteCtx.Credentials = $Cred

    #Get Loookup context
    $lookupCtx = New-Object Microsoft.SharePoint.Client.ClientContext($lookupWebURL)
    $lookupCtx.Credentials = $Cred

    #Get web, list and column with problems
    $siteWeb = $siteCtx.Web
    $siteList = $siteWeb.Lists.GetByTitle($listName)
    $siteColumn = $siteList.Fields.GetByInternalNameOrTitle($columnName)
    $siteCtx.Load($siteWeb)
    $siteCtx.Load($siteList)
    $siteCtx.Load($siteColumn)
    $siteCtx.ExecuteQuery()

    #Get web and lookuplist that is source of the lookup column
    $lookupWeb = $lookupCtx.Web
    $lookupList = $lookupWeb.Lists.GetByTitle($lookupListName)
    $lookupCtx.Load($lookupWeb)
    $lookupCtx.Load($lookupList)
    $lookupCtx.ExecuteQuery()

    # Prepare the IDs that are going to be updated
    $newLookupListID = $lookupList.ID.ToString()
    $newLookupWebID = $lookupWeb.ID.ToString()

    # Replaces the XML that defined the Lookup Column
    Write-Host $siteColumn.SchemaXml -f DarkYellow
    $schema = $siteColumn.SchemaXml
    [Xml]$schemaXml = $schema
    #$schemaXml.Field.Attributes["WebId"].'#text' = $newLookupWebID
    $requiresUpdate = $false
    Write-Host "ID's: " $schemaXml.Field.Attributes["List"].'#text' $newLookupListID
    if($schemaXml.Field.Attributes["List"].'#text' -ne $newLookupListID)
    {
        Write-Host "Found issue with List, Is:" $schemaXml.Field.Attributes["List"].'#text' "should be" $newLookupListID -f red
        $schemaXml.Field.Attributes["List"].'#text' = $newLookupListID
        $requiresUpdate = $true
    }
    if($requiresUpdate) {
        $siteColumn.SchemaXml = $schemaXml.OuterXml
        Write-Host "Fixing the list lookup Id to "$schemaXml.OuterXml -f DarkGray
        $siteColumn.Update()
        $siteCtx.ExecuteQuery()
        Write-Host "Lookup Field has fixed...Enjoy"
    }
}

Once this script runs successfully then the lookup column will restore it on the corrupt column list.