Fix SharePoint Lookup Column Values Blank Issue using PowerShell

Problem: SharePoint lookup columns are broken in 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 generated every time when the object is recreated. So, when you export-import or re-create the parent lookup lists, this connectivity is broken.

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 re-link 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:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

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

#Get the Objects
$Site = 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 lookup column blank values issue.

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

Leave a Reply