Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a library containing a large amount of items.

The library contains a column of type 'Single line of text' with a code in it. The code was taken from an external system and entered manually.

There is a now a library on the SharePoint site itself containing all the codes. I need to change the library column containing the codes to type 'Lookup' referencing the new SharePoint library containing the codes.

I know this cannot be done directly.

I need a way to take the value from old code column and use it to set the new lookup column referencing the code in an external library.

I really have no idea about how to go about this.

Has anyone encountered a similar problem and hence be able to offer me some advice about how to go about this? The library have over 15,000 items, so doing this manually would be out of the question.

share|improve this question

2 Answers 2

I would definitely go for powershell script.

First, create new lookup column in your list with data - say codeLookup.

Pseudocode:

$listWithCodes = $web.Lists["listname"];
$listWithData = $web.Lists["listname2"];

$listWithData.Items | foreach-object {
  # do caml query or something similar to get lookup 
  # to list with codes
  $codeItem = GetLookUp($_.["Code"]);
  # set lookup value
  $_.["codeLookup"] = New-Object Microsoft.Sharepoint.SPFieldLookupValue($codeItem.ID,$codeItem.Title);
}

$listWithData.Update();
$web.Dispose();

Example of caml query in PS: $queryString = "1"

$query = new-object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.Query = $queryString
$result = $list.GetItems($query)
share|improve this answer
    
Hi there, thank you for the pseudocode. I can't say I've used powershell much before, but would of guessed that it would be the best way to go about this. I shall try my best to implement this with my limited experience in PS & SP. –  MalcolmDurling Jan 14 '13 at 19:42
1  
Just replace listname and listname2 with names of your lists and replace method GetLookup with calling caml to get item with specified code. I'll modify my answer after I get back to computer to help you. –  luccio Jan 14 '13 at 21:04
    
Hmmm, unfortunately what I failed to bring up was the fact that the $listWithCodes list is an external list using a BCS connection. It seems to be having trouble accessing the list through powershell saying it attempted to peform an unauthorized operation. –  MalcolmDurling Jan 15 '13 at 3:39
    
Then do export of that list to xml and in powershell you can process xml file. # get xml contents; $xml = [xml](get-content $xmlPath); # get root node; $root = $xml.Offices; –  luccio Jan 15 '13 at 8:44
    
Again, thank you for your suggestion, but external lists don't seem to behave in the same as normal lists when it comes to exporting to XML. Everything I have read relating to this seems to fail and only work for normal lists.. –  MalcolmDurling Jan 15 '13 at 20:25

How about creating a new lookup column in the first library and then batch updating all the items in the library by using the values stored in the old text column. And once every item is updated, delete the old text column.

share|improve this answer
    
Yes, that's pretty much what I'm trying to achieve. However my experience with SharePoint is somewhat limited. I'm trying to get more detailed advice about how to carry this out. I assume you would use powershell for batch updating? –  MalcolmDurling Jan 14 '13 at 19:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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