I am working with SharePoint on prem 2019 and in one of the document libraries, I have a field called 'Trust' of type choice, this library contains about 7000 files. Now we have to convert this choice field into a lookup column and I am aware its not doable OOTB.

So we added all choice values for Trust field into a list (titled 'Trust List') on the same subsite where the library resides. Then created a site lookup column 'TrustName' that points to the title field of Trust list and added it to the library as well.

Now I need to find a solution to sort of grab Trust (choice) value for each row and paste it into the TrustName (lookup), maybe there would be need of filtering of ID from lookup list based on the match of values of 'Trust' field and Title from lookup list, not sure, I have little expertise in powershell. So looking for a PowerShell script solution for this that I can run. FYI, there are 3 other lookup fields in the same library, couple of text fields, people picker etc.

Please help, thanks in advance. enter image description here

1 Answer 1


This can help you.

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue

$mySiteUrl = "https://some_portal/sites/yoursite"
$myList = "Some Library"

#lookup list name
$myListLU = "Lookup List"

$spSite = Get-SPSite -Identity $mySiteUrl
$web = $spSite.OpenWeb()

# Retrieve the lists
$list = $web.GetList($myList)
$listLU = $web.GetList($myListLU)

# Enumerate the list and update column
$itemCount = $list.Items.Count
ForEach ($item in $list.Items) {
  $choiceValue = $item["choiceColumn"]
  #get matching item from lookup list
  $lookupItem = $listLU.Items | where { $_['columnNameInLookUpList'] -eq $choiceValue }

  #fill the fetched item into new lookup column
  $NewLookupFieldValue = New-Object Microsoft.Sharepoint.SPFieldLookupValue $lookupItem["ID"], $lookupItem["columnNameInLookUpList"]
      $item["newLookupColumn"] = $NewLookupFieldValue

Please try executing where SharePoint is installed and let me know if any errors.

  • Thank you so much Harshal for a quick response, I am going to try this shortly and let you know.
    – mdevm
    Sep 27, 2019 at 20:15
  • Hey harshal, i guess there are couple of unexpected parenthesis at the end, can you check please. Also on the dev machine/en, when I opened SP 2019 management shell as admin, I see a message: "the local farm is not accessible, CMDlests with feature dependecyID are not registered. " the message comes up first before running any script. However I don't see that on production when SP19 management shell is launched. Any thoughts? Dev is single server farm and different user account is used than prod. FYI.
    – mdevm
    Sep 28, 2019 at 17:01
  • Open it as farm account and then try. Please adjust the parenthesis. Sep 28, 2019 at 17:24
  • Thanks Harshal, I am using farm account only
    – mdevm
    Sep 28, 2019 at 17:26
  • Try running it as admin. Still if you get the same error, then try running this Add-SPShellAdmin -username DOMAIN\YourUser Sep 28, 2019 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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