SharePoint Repair lookup columns which are not connected to a list

1. December 2013 Karsten Schneider Administration, Powershell, SharePoint, Tipps&Tricks 6

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

[sourcecode language=”csharp”]

$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()
}

[/sourcecode]

Fix Site Column with lookup

[sourcecode language=”csharp”]

$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()
}

[/sourcecode]

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.

About Karsten Schneider 312 Articles
Consultant for Microsoft 365 Applications with a strong focus in Teams, SharePoint Online, OneDrive for Business as well as PowerPlatform with PowerApps, Flow and PowerBI. I provide Workshops for Governance & Security in Office 365 and Development of Solutions in the area of Collaboration and Teamwork based on Microsoft 365 and Azure Cloud Solutions. In his free time he tries to collect tipps and worthy experience in this blog.

6 Comments

  2. 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.

  3. 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

  4. Hi,
    nice script, I did some modifications:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    Function RepairListLookupColumns($webURL, $listName, $columnName, $lookupListName, $lookupWebURL)
    {
    #Get web, list and column objects
    $web = Get-SPWeb $webURL
    $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 List” $list.Title “is connected with” $lookupList.Title “from” $lookupWeb.Url
    $web.Dispose()
    $lookupWeb.Dispose()
    }

    $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 -lookupWebURL $lookupWebURL

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    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 List” $list.Title “is connected with” $lookupList.Title “from” $lookupWeb.Url
    $web.Dispose()
    $lookupWeb.Dispose()
    }

    $webURL = “http://server/sites/XY”
    $columnName = “MyColumn”
    $lookupListName = “Category”
    $lookupWebURL = “http://server/sites/X”

    RepairSiteLookupColumns -webURL $webURL -columnName $columnName -lookupListName $lookupListName -lookupWebURL $lookupWebURL

Leave a Reply