SharePoint Repair lookup columns which are not connected to a list

A story from real life. There was a list to store many elements which were basically used for some other lists by using lookup columns. After this big important list was deleted and recovered from backup, the connection between the lookup column and the lookup list ist broken. The column does not show any entries nor can it be repaired using the UI.

Normally it displays that it gets its information from List X, but this is empty. What happened?

The lookup column is always connected by some properties:

  • WebId – This is the web where the list is stored
  • ListId – This is the List where the information comes from
  • ShowField – This is the field from source list, which will be displayed

After backup the listId is broken, maybe the WebId too. Therefore the following powershell script could be very useful. But keep in mind, that you have to decide between a list column with lookup and site column with lookup.

Fix List Column with lookup



$webURL = "http://server/sites/XY"
$listName = "MyList"
$columnName = "MyColumn"
$lookupListName = "Category"
$lookupWebURL = "http://server/sites/X"

RepairListLookupColumns -webURL $webURL -listName $listName -columnName $columnName -lookupListName $lookupListName -lookupWeb $lookupWebURL
Function RepairListLookupColumns($webURL, $listName, $columnName, $lookupListName, $lookupWebURL)
{
#Get web, list and column objects
$web = Get-SPWeb $webURL
$lookupWeb = Get-SPWeb $lookupWebURL
$lookupWeb = Get-SPWeb $lookupWebURL
$list = $web.Lists[$listName]
$column = $list.Fields[$columnName]
$lookupList = $lookupWeb.Lists[$lookupListName]
$newLookupListID = "{"+$lookupList.ID.ToString()+"}"
$newLookupWebID = "{"+$lookupWeb.ID.ToString()+"}"

#Change schema XML on the lookup column
$column.SchemaXml = $column.SchemaXml.Replace($column.LookupWebId.ToString(), $newLookupWebID)

$column.SchemaXml = $column.SchemaXml.Replace($column.LookupList.ToString(), $newLookupListID)
$column.Update()

#Write confirmation to console and dispose of web object
write-host "In Site" $web.Url "column " $column.Title "in Liste" $list.Title "is connected with"  $lookupList.Title "from" $lookupWeb.Url
$web.Dispose()
$lookupWeb.Dispose()
}


Fix Site Column with lookup



$webURL = "http://server/sites/XY"
$listName = "MyList"
$columnName = "MyColumn"
$lookupListName = "Category"
$lookupWebURL = "http://server/sites/X"

RepairSiteLookupColumns -webURL $webURL -columnName $columnName -lookupListName $lookupListName -lookupWeb $lookupWebURL
Function RepairSiteLookupColumns($webURL, $columnName, $lookupListName, $lookupWebURL)
{
#Get web, list and column objects
$web = Get-SPWeb $webURL
$lookupWeb = Get-SPWeb $lookupWebURL
$column = $web.Fields[$columnName]
$lookupList = $web.Lists[$lookupListName]
$newLookupListID = "{"+$lookupList.ID.ToString()+"}"
$newLookupWebID = $web.ID.ToString()

$column.SchemaXml
#Change schema XML on the lookup column
$column.SchemaXml = $column.SchemaXml.Replace($column.LookupWebId.ToString(), $newLookupWebID)

$column.SchemaXml = $column.SchemaXml.Replace($column.LookupList.ToString(), $newLookupListID)
$column.Update()
$column.SchemaXml
#Write confirmation to console and dispose of web object
write-host "In Site" $web.Url "column " $column.Title "in Liste" $list.Title "is connected with"  $lookupList.Title "from" $lookupWeb.Url
$web.Dispose()
$lookupWeb.Dispose()
}


Hope this helps you.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

3 comments on “SharePoint Repair lookup columns which are not connected to a list

  1. Katrina Marques

    HI there – this looks like a nice script but what version of SharePoint did you run this on? I am trying to repair broken lookup columns and site columns on SP2013 due to upgrade/migration project and I get these errors:
    You cannot call a method on a null-valued expression.
    At line:8 char:1
    + $newLookupListID = “{“+$lookupList.ID.ToString()+”}”
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Exception setting “SchemaXml”: “0x80070057</nativehr ”
    At line:11 char:1
    + $column.SchemaXml = $column.SchemaXml.Replace($column.LookupList.ToString(), $ne …
    +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

    Is this because we need to call client object model?
    Any help would be appreciated.

    • Yaron Buskila

      Hi,

      You got null-valued expression. because powershell was not able to get the list by calling:
      $lookupList = $web.Lists[$lookupListName]

      when you try to get a list that has a corrupted column this is not working. For me it is the error of the field is not install properly go to setting page and remove the column….

      I would like to fix it but cannot get it, any Ideas?

      Yaron

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.