Fixing blank lookup columns in SharePoint when creating new site from a site template

One of the common issues when creating a new site from a site Template is that the lookup columns values are blanks. The full scenario is as below :

• Create a SharePoint site
• Add lists with lookup columns
• Insert some data in the lists
• Save the site as Template with its content
• Create a new site from the Template

When you access the list which contains the lookup column, you will notice that the lookup values are empty. The lookup values still exist but they are not rendered. We have to fix the list schema by updating some properties of the lookup column. Two properties have to be updated :

• The Web Id
• The List Id

There are two ways to fix the list schema :

• Using C#
• Using Powershell

We will see how to fix the error using Powershell. The script below will let you fix the column.

$web = Get-SPWeb "Your web url"
$list = $web.Lists["Your list name"]
$column = $list.Fields["Your column name"]
$lookupList = $web.Lists["Your source list name"]
#Updating the column properties
$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 "Column properties have been updated"
$Web.Dispose()

  

More details about the lookup columns on MSDN.