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: 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.
Powershell is not available for me as it’s restricted by my company’s IT. Is this possible to do in a workflow instead? … go through the entire list to update and relink the lists?
Worked perfectly! Thank you!
This script doesn’t work correctly, There is no variable ‘$web’
Fixed it! Thanks.