Fix SharePoint Lookup Column Values Blank Issue using PowerShell

Problem: SharePoint lookup columns are broken in the below scenarios:

  • If the parent lookup list is deleted and recreated – Then the connection between lookup list and the lookup column will be broken and you’ll get empty values!
  • When you export-import a subsite or lists, lookup fields become orphaned from its parent list!
  • When you save a lists as list templates and use it in another site, the lookup column no longer works – even though you created the parent lookup list in the target site!

Root Cause:

SharePoint lookup columns are connected with its parent lookup list column through WebID and ListID GUIDs, which are regenerated whenever the object is recreated. So, this connectivity is broken when you export-import or re-create the parent lookup lists.

sharepoint lookup column empty value

SharePoint: PowerShell to fix lookup column

If you come across any of the above scenarios and your lookup column is broken, you have to relink the lookup column with its parent list. Use this PowerShell script to fix the lookup columns in SharePoint.

PowerShell Script to fix SharePoint Lookup Column Blank Values:

This PowerShell relinks the lookup parent list with a lookup column:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteURL="https://intranet.crescent.com/"
$ListName="Project Milestones" 
$LookupColumnName="Parent Project"
$LookUpListName="Projects" #Parent List 

#Get the Objects
$Web = Get-SPWeb $SiteURL
$List = $web.Lists[$ListName]
$LookupList = $web.Lists[$LookUpListName]
$Column = $List.Fields[$LookupColumnName]

#Update column schema
$Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupWebId.ToString(), $Web.ID.ToString())
$Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupList.ToString(), $LookupList.ID.ToString())
$Column.Update()

Write-host "Lookup column fixed!" -f Green

This fixes SharePoint 2013/2016 lookup column blank values issue.

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

2 thoughts on “Fix SharePoint Lookup Column Values Blank Issue using PowerShell

Leave a Reply