Fixing blank lookup columns in SharePoint using PowerShell

Here is the scenario: You have a site containing a list used by a lookup column from another list in that same site. When you save both lists as list templates and create them in another site, the lookup column no longer works and just shows blank options.

You can use PowerShell to fix this by first running this function in the SharePoint 2010 Management Shell:

Function Fix-LookupColumn ($webURL, $listName, $columnName, $lookupListName)
{
    #Get web, list and column objects
    $web = Get-SPWeb $webURL
    $list = $web.Lists[$listName]
    $column = $list.Fields[$columnName]
    $lookupList = $web.Lists[$lookupListName]
   
    #Change schema XML on the lookup column
    $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 confirmation to console and dispose of web object
    write-host "Column" $column.Title "in list" $list.Title "updated to lookup list" $lookupList.Title "in site" $web.Url
    $web.Dispose()
}

…followed by this command to call the function for the particular list and column that you wish to fix:

Fix-LookupColumn -webURL http://SiteURL -listName "Name of list containing the lookup column" -columnName "Lookup column name" -lookupListName "List used by the lookup column"

For example, the list used in the image at the top of this article is called “News” and contains two lookup columns called “Countries” and “Departments”. These columns look up values from two lists on the same site called “Country List” and “Department List”. Therefore, to fix the lookup columns in the “News” list on site http://portal, you would type the following commands:

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Countries" -lookupListName "Country List"

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Departments" -lookupListName "Department List"

The lookup columns now successfully wire up to their respective lists in the site:

But wait, there’s more. There could be a scenario where you have used a list on the root site of your site collection and created a lookup column as a site column for all lists to use throughout the site collection. This method allows you to centralise the lists used for lookup columns at the root site so that they can be consumed by all lists in that site collection.

However, the dreaded blank lookup columns issue will also occur if you save a list that uses a lookup site column from one site collection and create it on another site collection using the list template – even though an identical version of the lookup site column with the same internal name and ID has been created on both site collections.

To fix this issue, I simply ran a small script to push changes from the custom lookup columns on the root site to all lists using those columns. The result of this is that the script updates the custom lookup columns used in lists to match the schema published from the root site:

#Get the root site of the site collection
$rootWeb = Get-SPWeb http://portal

#Get all lookup columns from the group Custom Site Columns
[array]$siteColumns = $rootWeb.Fields | where { ($_.Type -eq "Lookup") -and ($_.Group -eq "Custom Site Columns") }

#Get each column and push changes to lists
$siteColumns | ForEach-Object {
    $siteColumn = $rootWeb.Fields[$_]
    $siteColumn.PushChangesToLists = $true
    $siteColumn.Update()
    write-host "Change site column:" $_
}

#Dispose of web object
$rootWeb.Dispose()

Note in the script that I get all lookup columns from a site column group called “Custom Site Columns”. You will need to change this to the group name(s) used for the custom lookup columns in your site.