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 re-generated and the connectivity between parent and child lookup lists is broken and lookup field values become empty!
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 SharePoint Online list.
#Parameters
$SiteURL = "https://crescentintranet.sharepoint.com/sites/pmo"
$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 $ChildList -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
$ChildField.UpdateAndPushChanges($true)
Invoke-PnPQuery
Also, Remember, We've 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 same! Because, Field value in each row of the lookup field of child lookup list will be pointing to a row ID in parent lookup list.
Worked like a charm, thank you very much for this helpful tutorial!
ReplyDelete