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 ::..
Thanks .. Worked like a charm…
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.
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
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
its done with script but not able to get data updated
Do you have the GUIDs correct?