Is there a way to update a lookup site column?

A colleague of mine deleted the list which the site column was referring to. We created a new list, but the existing site lookup column is practically a 'loose end'.

We want to avoid creating a new site column to look to the new list, instead we want to edit the existing lookup site column and to refer it to the new list.

  • Have you tried to recover the list from the recycle bin? – Marek Sarad Oct 23 '18 at 14:19
  • It is not about deleted list. I retrieved it. It is about changing destination of lookup site column. – Danilo Oct 24 '18 at 6:47

It's not possible to Edit lookup columns to deleted lists. You can use powershell to do so. Check the posts here and here. Or you can recover deleted list from Admin Recycle bins.

  • Can I do this in one place, as I see in those examples, I would ave to go in every list and change that lookup from there. I am talking about site lookup column. – Danilo Oct 24 '18 at 6:49
  • I checked many solutions and couldn't find anything to fix site columns directly. You will have to go through every list to do so. – harshal gite Oct 24 '18 at 7:08
  • I see. Well that is not an option as there are dozens of lists. Thank you on your time. – Danilo Oct 24 '18 at 8:23

If I understand correctly, you have a lookup column in one list that is pointed to a list that no longer exists, correct?

I believe you can fix this using PowerShell, but you have to manually update the SchemaXml property for the lookup column.

  1. Get the lists (assuming they are on different sites): $targetWeb = Get-SPWeb http://sitecollection/site1/ $sourceWeb = Get-SPWeb http://sitecollection/site2/ $targetList = $targetWeb.Lists.item("Target List Name") $sourceList = $sourceWeb.Lists.item("Source List Name")

  2. Get the source list ID

    $sourceListId = $sourceList.Id

  3. Get the Lookup field on the target list.

    $LookupField = $targetList.Fields["LookUpFieldName"]

  4. Update the LookupField LookupField & LookupWebId properties

    $lookupField.LookupWebId = $sourceList.ParentWeb.Id $LookupField.LookupField = $sourceList.Fields['SourceFieldName'].InternalName

  5. At this point, we need to update the field schema with the ID of the new Source list. I use Notepad for simplicity. Export the schema and the source list ID:

    $LookupField.SchemaXml > D:\Temp\Schema.txt $sourceListId > D:\Temp\SourceListId.txt

  6. Find the old list GUID in the schema file and replace it with the new list GUID (make sure to preserve the curly braces).

Locate the old list GUID in the field schema and replace it with the new list GUID

  1. In Powershell, import the updated Schema block and update the LookupField

    $schemaNew = Get-Content D:\Temp\Schema.txt $LookupField.SchemaXml = $schemaNew $LookupField.Update();

I'm not sure how this will affect old lookup values that were in the target list before it was disconnected from the source list. But, at least in my experimental case, they seem to be unaffected because nothing else changed besides the ID of the source list.

Partial credit goes to George Norberg for his answer to the below post: Getting lookup values from another site in the same site collection

  • Lookup column is site column and it is in multi lists, in dozens of them. – Danilo Oct 24 '18 at 6:46
  • In that case you may be able to fix it by updating the LookupList, LookupWebId and LookupField properties of the site column, then forcing it to re-publish throughout the site collection (i.e. making a change to the column through the web interface with the "Update all list columns based on this site column?" option set to Yes.) – Chris Odegard Oct 24 '18 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.