none
Is there any way to rebuild the broken mapping of Lookup column? RRS feed

  • Question

  • Hello Everyone 

    I want to rebuild the broken mapping of SharePoint lookup column.

    When I click on the specific column I don't find the any list name under the Get information from option, it is blank as the parent list was got deleted.

    Now after recreating the parent list, is there any way to rebuild the lookup mapping by using any PnP script or any other way to do it or any other workaround ?

    Even the List Item count is beyond the threshold limit, so not able to delete those columns by using UI as well as PnP Powershell.

    Please suggest the useful solutions

    Thanks in Advance 

     
    Thursday, September 12, 2019 4:30 PM

All replies

  • You can try with the below powershell script to fix the lookup column which has Blank Values.

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    
    #Configuration Parameters
    $SiteURL="http://intranet.crescent.com/"
    $ListName="Project Milestones" 
    $LookupColumnName="Parent Project"
    $LookUpListName="Projects" #Parent List 
    
    #Get the Objects
    $Site = Get-SPWeb $SiteURL
    $List = $web.Lists[$ListName]
    $LookupList = $web.Lists[$LookUpListName]
    $Column = $List.Fields[$LookupColumnName]
    
    #Update column schema
    $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 "Lookup column fixed!" -f Green
    Thanks & Regards,


    sharath aluri

    Thursday, September 12, 2019 4:44 PM
  • Hi Nikhilp005,

    If a reply helps you, please remember to mark it as an answer.

    Thanks for your understanding. 

    Best Regards, 

    Lisa Chen 

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, September 13, 2019 1:25 AM
    Moderator
  • Hi Sharath,

    Thanks for your reply.

    I need to perform this for SharePoint Online, so I have change the syntax accordingly so below is my final script 

    ListName = TestDelete
    LookupListName=TestLookup


    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    $SiteURL= ""

    $cerd = Get-Credential 

    Connect-PnPonline -Url $SiteURL -Credentials $cred 

    $web= Get-PnPWeb

    $List = Get-PnPList -Identity Lists/TestDelete

    $LookupList = Get-PnPList -Identity Lists/TestLookup

    $Column = Get-PnPField -List "TestDelete" -Identity "TestCol"

    #Update column schema
    $Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupWebId.ToString(), $Web.ID.ToString())
    $Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupList.ToString(), $LookupList.ID.ToString())
    $Column.Update()


    while running the script I am not getting parameter as LookupId and LookupList for $Column, so I have pass hard coded values for both as below

    $Column.LookupWebId.ToString() = web ID in string format
    $Column.LookupList.ToString() = ListName (tried with both list Name)   

    I am not getting any error, script is running successfully but it is not mapping the broken Lookup column.

    Please provide your suggestion or if I am missing any thing.

    All suggestions are welcome.
    • Edited by NikhilP005 Friday, September 13, 2019 11:31 AM
    Friday, September 13, 2019 11:20 AM
  • Try using the below code for SharePoint Online Lookup Repair.

    using (var context = new ClientContext("https://xxx.sharepoint.com/sites/global/"))
                {
                    Console.ForegroundColor = ConsoleColor.Green;
                    string password = "pw";
                    SecureString sec_pass = new SecureString();
                    Array.ForEach(password.ToArray(), sec_pass.AppendChar);
                    sec_pass.MakeReadOnly();
                    context.Credentials = new SharePointOnlineCredentials("spadmin@O365E3W15.onmicrosoft.com", sec_pass);
    
                    var sourceList = context.Web.Lists.GetByTitle("ListA");
                    context.Load(sourceList);
                    context.ExecuteQuery();
                    var fields = context.Web.Fields;
                    Field lookupField = fields.AddFieldAsXml("<Field Type='Lookup' Name='myLKField' StaticName='myLKField' DisplayName='myLKField' List='" + sourceList.Id + "' ShowField='Title' />", true, AddFieldOptions.AddFieldInternalNameHint);
                    lookupField.Update();                
                    context.ExecuteQuery();           
                    Console.WriteLine("done");
                    Console.ReadKey();
                }
    Thanks & Regards,


    sharath aluri

    Friday, September 13, 2019 11:35 AM
  • Hi Nikhilp005,

    Please remember to mark the helpful post as an answer.

    Thanks for your understanding. 

    Best Regards, 

    Lisa Chen 

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, September 17, 2019 11:22 AM
    Moderator
  • Hi Nikhilp005,

    If Sharath's reply helps you, please remember to mark it as an answer.

    Thanks for your understanding. 

    Best Regards, 

    Lisa Chen 



    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, September 20, 2019 10:25 AM
    Moderator