Wednesday, 19 January 2011

Fixing blank lookup columns in SharePoint using PowerShell

Here is the scenario: You have a site containing a list used by a lookup column from another list in that same site. When you save both lists as list templates and create them in another site, the lookup column no longer works and just shows blank options.

Lookups_NotWorking

You can use PowerShell to fix this by first running this function in the SharePoint 2010 Management Shell:

Function Fix-LookupColumn ($webURL, $listName, $columnName, $lookupListName)
{
    #Get web, list and column objects
    $web = Get-SPWeb $webURL
    $list = $web.Lists[$listName]
    $column = $list.Fields[$columnName]
    $lookupList = $web.Lists[$lookupListName]
   
    #Change schema XML on the lookup column
    $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 confirmation to console and dispose of web object
    write-host "Column" $column.Title "in list" $list.Title "updated to lookup list" $lookupList.Title "in site" $web.Url
    $web.Dispose()
}

…followed by this command to call the function for the particular list and column that you wish to fix:

Fix-LookupColumn -webURL http://SiteURL -listName "Name of list containing the lookup column" -columnName "Lookup column name" -lookupListName "List used by the lookup column"

For example, the list used in the image at the top of this article is called “News” and contains two lookup columns called “Countries” and “Departments”. These columns look up values from two lists on the same site called “Country List” and “Department List”. Therefore, to fix the lookup columns in the “News” list on site http://portal, you would type the following commands:

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Countries" -lookupListName "Country List"

Fix-LookupColumn -webURL http://portal -listName "News" -columnName "Departments" -lookupListName "Department List"

The lookup columns now successfully wire up to their respective lists in the site:

Lookups_Working

But wait, there’s more. There could be a scenario where you have used a list on the root site of your site collection and created a lookup column as a site column for all lists to use throughout the site collection. This method allows you to centralise the lists used for lookup columns at the root site so that they can be consumed by all lists in that site collection.

However, the dreaded blank lookup columns issue will also occur if you save a list that uses a lookup site column from one site collection and create it on another site collection using the list template – even though an identical version of the lookup site column with the same internal name and ID has been created on both site collections.

To fix this issue, I simply ran a small script to push changes from the custom lookup columns on the root site to all lists using those columns. The result of this is that the script updates the custom lookup columns used in lists to match the schema published from the root site:

#Get the root site of the site collection
$rootWeb = Get-SPWeb http://portal

#Get all lookup columns from the group Custom Site Columns
[array]$siteColumns = $rootWeb.Fields | where { ($_.Type -eq "Lookup") -and ($_.Group -eq "Custom Site Columns") }

#Get each column and push changes to lists
$siteColumns | ForEach-Object {
    $siteColumn = $rootWeb.Fields[$_]
    $siteColumn.PushChangesToLists = $true
    $siteColumn.Update()
    write-host "Change site column:" $_
}

#Dispose of web object
$rootWeb.Dispose()

Note in the script that I get all lookup columns from a site column group called “Custom Site Columns”. You will need to change this to the group name(s) used for the custom lookup columns in your site.

38 comments:

  1. Thanks Phil, I have used your script to answer my own question on the sharepoint msdn forum

    http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/4e08cc47-3a20-468d-b21f-b9cafcb9347d

    Mick

    ReplyDelete
  2. My pleasure Mick - glad I could help

    ReplyDelete
  3. Huge help. Thanks for the post!!

    ReplyDelete
  4. Dear Phil,
    thank you for you post, but i'm still facing a problem when i tried to import lookup columns to siteColumns , the source list property to get info from is empty.

    please help!

    ReplyDelete
  5. Thanks for the Post. It helped me alot!!!!

    ReplyDelete
  6. Thanks for the post, helped me so many times !!!

    ReplyDelete
  7. You just made something that I thought was so difficult be, truly, so easy! Thanks for the post!

    ReplyDelete
  8. You rock. This was worth a million bucks.

    ReplyDelete
  9. This is one of the cult game now, a lot of people enjoy playing them . Also you can refer to the game :
    animal jam 2 | five nights at freddys 2 | hotmail login

    ReplyDelete
  10. Thank you very much for sharing this very useful information, I am very happy to have found this information that I have been looking for

    Obat Kerumut Alami
    Obat Tenggorokan
    Obat Cenang Herbal
    Obat Bisul Di Telinga
    Obat Rorombeheun
    Obat Gatal Pada Tangan
    Obat Tumor Parotis
    Cara Mengobati Hirschsprung

    ReplyDelete
  11. Thank you very much for sharing this very useful information, I am very happy to have found this information that I have been looking for

    Obat Gudik (scabies)

    Obat Budug Alami

    Pemesanan QnC Jelly Gamat

    Obat Lecet Pada Vagina

    Obat Penumbuh Jaringan Kulit & Daging

    ReplyDelete
  12. Air max horarios puente aereo barcelona madrid From A primary school." If maglia lazio the nike roshe run mood silently coach outlet recorded the name. After Wholesale Authentic NFL Jerseysmichael kors outlet online sale the michael kors outlet online sale name vans

    magliette calcio
    with nike roshe run her ray ban uk junior high school ralph lauren uk three nfl colts jerseys years mcm unimail tu dortmund bags Cheap NFL Jerseys From China of good times. If Cheap NFL Jerseys From China michael kors purses the situation is nfl falcons jerseys still air max thea a vans person hermes belt sitting bulls resultados de futbol jersey behind, nfl jaguars jerseys because ray ban she did valentino shoes not toms shoes outlet classmates coach camiseta atletico de madrid outlet online in boutique clothing this class, air force there is oakley vault no michael kors handbags understanding of air max pas cher people

    ReplyDelete
  13. Air max [url=http://www.cave-canem.es]horarios puente aereo barcelona madrid[/url] From A primary school." If [url=http://www.phptostart.it]maglia lazio[/url] the nike roshe run mood silently coach outlet recorded the name. After [url=http://www.langstrothshive.com/]Wholesale Authentic NFL Jerseys[/url] michael kors outlet online sale the michael kors outlet online sale name vans [url=http://www.phptostart.it]magliette calcio[/url] with nike roshe run her ray ban uk junior high school ralph lauren uk three nfl colts jerseys years mcm [url=http://www.flughafen-natofrei.de][i]unimail tu dortmund[/i][/url] bags [url=http://www.langstrothshive.com/]Cheap NFL Jerseys From China[/url] of good times. If [b][url=http://www.deboramanusama.com]Cheap NFL Jerseys From China[/url][/b] michael kors purses the situation is nfl falcons jerseys still air max thea a vans person hermes belt sitting bulls [url=http://www.adarme.es/][b]resultados de futbol[/b][/url] jersey behind, nfl jaguars jerseys because ray ban she did valentino shoes not toms shoes outlet classmates coach [url=http://www.camiseta-athletic.es/][b]camiseta atletico de madrid[/b][/url] outlet online in boutique clothing this class, air force there is oakley vault no michael kors handbags understanding of air max pas cher people.

    ReplyDelete

  14. This amount includes applicable customs duties, taxes, brokerage and other fees. This amount is subject to change until you make payment. For additional kate spade outlet locations information, see the kate spade secret sale Global Shipping Program terms and conditions opens in a new window or tab

    ReplyDelete
  15. thank you very useful information admin, and pardon me permission to share articles here may help :

    Obat herbal sakit tulang ekor
    Obat fatty liver
    Obat untuk penderita bronkitis

    ReplyDelete

  16. Pay Cheap NFL Jerseys From China for your purchases when it's right for you. Choose PayPal Credit to pay cheap michael kors bags all England National Football Team at once or enjoy coach factory outlet online login the flexibility of paying over time with special financing offers.

    ReplyDelete
  17. Khali se desvanece junto al club a través de un escaparate significativo para cuidar a Epico y Primo.123. Y debe describirse que Rosa camisetas de futbol baratas puede, Fundamentalmente hablando, referirse directamente a youtube para Cole, ha salido con su compañía.124. Ignoré a los individuos de la cadera y las extremidades inferiores.125. Mucha gente entendió que Kelly Felix siente que Chartier tiene grandes maglie calcio ingresos. Pero más Camisetas Baratas De Futbol bien Ken Chartier indudablemente aisló un pueblo marinero delantero mejorado además Starville camino a su presupuesto por qué en el proceso de ejecución hipotecaria él / ella. Por esa razón, Ken Chartier no invirtió en nada.

    ReplyDelete
  18. camisetas de futbol baratas y simplemente DeForest Buckner se engancharon a la escolta de la policía que tiene seis bolsas cada una. La gran noticia es que tanto la firma libre como la puesta en marcha consisten en botadores de pase.

    ReplyDelete
  19. El tipo de Maillot Equipe De France Foot Pas Cher handyroom tradicional save as always flexible, Strategy para obtener spread mantenido y crisol de las últimas estrategias de localización custom combina que incluye el proceso de conversión y tiempos pasados ​​es vital para el futuro cercano. Era la idea de organizar que la mayoría de los resultados apuntaran, matando el centenario de la fundación de la entidad corporativa, generar un número completo de selecciones delicadas, informes, camisetas de futbol replicas planos, imágenes y videos demostrando un centenar camisetas de futbol baratas de años involucrando cuento alternativo de lengua italiana. Encerrado sobre abitazione Zegna, todos estos repositorios incluyen a lo largo de una distancia medio informe (Esto consta de materiales biológicos que se guardan en la rutina de woll) Ofreciendo puntos de vista sobre el entorno comunitario y el activismo social respetuoso del medio ambiente que camisetas nba exhibe el Camisetas De Futbol Baratas Toda la percepción de Ermenegildo Zegna, Quien siempre había tenido el deseo de protegerse junto con aprender el lugar del área.

    ReplyDelete
  20. Thank you very much for sharing this very useful information, I am very happy to have found this information that I have been looking for
    Obat Cacar Air
    Obat Cacar Api / Cacar Ular
    Obat Kista Epidermoid
    Walatra Sehat Mata Softgel
    Obat Kerumut Alami

    ReplyDelete
  21. Muchos partidarios terminaron en Internet tierra en el corazón. En el título del juego de agarre de tu casa, solo una visión clara. Se les dio para ver toneladas de clasificación. Dos gran cantidad de nuevas oleadas con Gronk, como el juego previo al tanque con gran determinación. 'Creo que el programa secreto, Maillot Equipe De France Foot Pas Cher no hay ningún miembro secreto de personas que mienten en los alrededores del país de Kansas que permite maglie calcio la camisetas de futbol baratas riqueza de los niños pequeños, Claret menti uno el sábado, la verdadera otra carne de John demostrar. 'Cualquier situación en la que se encuentre cualquier jugador también llega a ser casi todo en tu ex a quien camisetas futbol baratas cumple con football team kits él localmente. Los entrenadores, así como la universidad, no tienen ninguna regulación mucho más que lo que el hombre trata de hacer.

    ReplyDelete