How to Update Newly Added Lookup Column using Value from the existing Choice column through powershell

Question

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.

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
      $item.Update()
}
$web.Dispose()

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