This script will help you to repair the lookup column in child list which is broken during migration.
Suppose the lookup column looks like this,
Repair Lookup Column In SharePoint Online Using PowerShell
To download SharePoint Online SDK and pass the credential online then please refer to my previous blog, Copy List Items from One Site to Another With Versions using Powershell
Then copy paste the powershell script and run it.
  1. #Load SharePoint CSOM Assemblies
  2. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
  3. Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'
  6. $siteUrl="Your Corrupt Column site Url"
  7. $listName="Your Corrupt Column Listname"
  8. $columnName="Your Corrupt Column Name"
  9. $lookupWebURL="Lookup Site Url"
  10. $lookupListName="Lookup List Name"
  12. RepairListLookupColumns $siteUrl $listName $columnName $lookupWebURL $lookupListName
  14. Function RepairListLookupColumns()
  15. {
  16. param
  17. (
  18. [Parameter(Mandatory=$true)] $siteUrl,
  19. [Parameter(Mandatory=$true)] $listName,
  20. [Parameter(Mandatory=$true)] $columnName,
  21. [Parameter(Mandatory=$true)] $lookupWebURL,
  22. [Parameter(Mandatory=$true)] $lookupListName
  23. )
  24. #Passing Credentials
  25. $credPath = 'D:\Arvind\safe\secretfile.txt'
  26. $fileCred = Import-Clixml -path $credpath
  27. $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)
  29. #Get site context
  30. $siteCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
  31. $siteCtx.Credentials = $Cred
  33. #Get Loookup context
  34. $lookupCtx = New-Object Microsoft.SharePoint.Client.ClientContext($lookupWebURL)
  35. $lookupCtx.Credentials = $Cred
  37. #Get web, list and column with problems
  38. $siteWeb = $siteCtx.Web
  39. $siteList = $siteWeb.Lists.GetByTitle($listName)
  40. $siteColumn = $siteList.Fields.GetByInternalNameOrTitle($columnName)
  41. $siteCtx.Load($siteWeb)
  42. $siteCtx.Load($siteList)
  43. $siteCtx.Load($siteColumn)
  44. $siteCtx.ExecuteQuery()
  46. #Get web and lookuplist that is source of the lookup column
  47. $lookupWeb = $lookupCtx.Web
  48. $lookupList = $lookupWeb.Lists.GetByTitle($lookupListName)
  49. $lookupCtx.Load($lookupWeb)
  50. $lookupCtx.Load($lookupList)
  51. $lookupCtx.ExecuteQuery()
  53. # Prepare the IDs that are going to be updated
  54. $newLookupListID = $lookupList.ID.ToString()
  55. $newLookupWebID = $lookupWeb.ID.ToString()
  57. # Replaces the XML that defined the Lookup Column
  58. Write-Host $siteColumn.SchemaXml -f DarkYellow
  59. $schema = $siteColumn.SchemaXml
  60. [Xml]$schemaXml = $schema
  61. #$schemaXml.Field.Attributes["WebId"].'#text' = $newLookupWebID
  62. $requiresUpdate = $false
  63. Write-Host "ID's: " $schemaXml.Field.Attributes["List"].'#text' $newLookupListID
  64. if($schemaXml.Field.Attributes["List"].'#text' -ne $newLookupListID)
  65. {
  66. Write-Host "Found issue with List, Is:" $schemaXml.Field.Attributes["List"].'#text' "should be" $newLookupListID -f red
  67. $schemaXml.Field.Attributes["List"].'#text' = $newLookupListID
  68. $requiresUpdate = $true
  69. }
  70. if($requiresUpdate) {
  71. $siteColumn.SchemaXml = $schemaXml.OuterXml
  72. Write-Host "Fixing the list lookup Id to "$schemaXml.OuterXml -f DarkGray
  73. $siteColumn.Update()
  74. $siteCtx.ExecuteQuery()
  75. Write-Host "Lookup Field has fixed...Enjoy"
  76. }
  77. }
Once this script runs successfully then the lookup column will restore it on the corrupt column list.
Repair Lookup Column In SharePoint Online Using PowerShell