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 - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

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

  • Worked perfectly! Thank you!

    Reply
  • This script doesn’t work correctly, There is no variable ‘$web’

    Reply

Leave a Reply

Your email address will not be published.