SharePoint Online: Fix Lookup Field using PowerShell

Problem: SharePoint Online lookup column in a list is broken when the parent lookup list is recreated!

Root Cause:

SharePoint Online list lookup columns are connected with its parent lookup list column through the “List” (GUID of the parent list) and “ShowField” attributes. When the parent lookup list is re-created, its GUID is regenerated, and the connectivity between parent and child lookup lists is broken, and lookup field values become empty!

sharepoint online fix lookup field using powershell

How to Fix Broken Lookup Column in SharePoint Online?

If you come across the above scenario and your lookup column is broken with empty values, you have to re-link the lookup column with its parent list. Use this PowerShell script to fix the lookup columns in the SharePoint Online list.

$SiteURL = ""
$ParentListName = "Projects"
$ChildListName = "ProjectMilestones"
#Field display names - Case sensitive!
$ParentFieldTitle = "Title"
$ChildFieldTitle = "Project"

#Connect to SharePoint Online
Connect-PnPOnline $SiteURL -Credentials (Get-Credential)

#Get Parent List & Child field
$ParentList = Get-PnPList -Identity $ParentListName
$ChildField = Get-PnPField -List $ChildListName -Identity $ChildFieldTitle

#Get the Field Schema XML
[Xml]$Schema = $ChildField.SchemaXml

#Update Field Schema with New Parent List and Field
$Schema.Field.Attributes["List"].'#text' = "{$($ParentList.Id.Guid)}"
$Schema.field.Attributes["ShowField"].'#text' = $ParentFieldTitle
$ChildField.SchemaXml = $Schema.OuterXml

Also, Remember, We have the parent lookup list saved as a list template with content and created a new list from it so that the associated row values remain the same! Because Field value in each row of the lookup field of the child lookup list will be pointing to a row ID in the parent lookup list.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

9 thoughts on “SharePoint Online: Fix Lookup Field using PowerShell

  • Hello,

    I do have the Same Issue, after Migrating a List of Content that is linked to Lookup Columns List, I can find the Data by searching the Name of it in the Search Bar, but the List is damaged so it doesn’t show any Content.
    I think i have the Same Error as previosly commented, and i am willing to use the PowerShell Scrip that you provided to solve the Issue.
    and here is a Simple Question (Beginner Question);

    do I run the Script on the Devices that i am using for the Migration (in the Local PowerShell after logging in as an Administrator). ? or where exactly should i rund the Script ?

    Thank you for your Support.

    • You can Run the PowerShell script from your client machine. Make sure you have the PnP.PowerShell module installed in your local machine.

  • Hi, Thank you for the posting!
    Im getting the error below,

    The property ‘#text’ cannot be found on this object. Verify that the property exists and can be set.
    At line:20 char:48
    + $Schema.Field.Attributes[“List”].’#text’ = “{$($ParentList.Id.Guid)}”
    + ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    The property ‘#text’ cannot be found on this object. Verify that the property exists and can be set.
    At line:21 char:1
    + $Schema.field.Attributes[“ShowField”].’#text’ = $ParentFieldTitle
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

    • Hi Vagsfyr,

      Confirmed the above script works just fine and fixes the broken lookup list. Make sure the field you are fixing is a child lookup field in the list.

  • Thank you for the posting. I ran your scripts and “Get information from” is still blank. {$($ParentList.Id.Guid)} got the correct Guid and $Schema.field.Attributes[“ShowField”].’#text’ = $ParentFieldTitle got the field name. Can you please let me know what did I miss?

  • This has been a thorn in my side FOREVER! It works great (If you fix the $ChildListName variable in row 14)!

  • Worked like a charm, thank you very much for this helpful tutorial!


Leave a Reply

Your email address will not be published. Required fields are marked *