Update Sharepoint Lookup field External Data - pnp powershell

Question

I have an External List with name "DataCentral" and I have another SharePoint List called "Mailbox".

In the Mailbox there is a column of type lookup where the data is coming from DataCentral.

Now I have to update these lookup column's using the pnp-Powershell

I am using the following code

Set-PnPListItem -List "Mailbox" -Identity 1 -Values @{"AccessProvidedTo" = "__bh01007300730073008300" }

The above code doesnt give any error but its not getting updated.

From the Display URL I found that the ID=__bh01007300730073008300 thats the reson I have used the text instead of 1,2,3 ...

Answer 1

Hi @Pavan Kumar Paruchuru ,

In External List, id like "__bh01007300730073008300" is a string. We are not able to use it to update lookup column with PnP.

You could update lookup field value with a lookup text value as a workaround.

PnP PowerShell Commands:

#Config Variables  
$SiteURL = "https://XXX.sharepoint.com/sites/XXX"  
$ListName = "your list name"  
$LookupFieldName = "your lookup field name"  
$LookupValueText = "you lookup value text"  
$ItemID = 1  
   
#Connect to PnP Online  
Connect-PnPOnline -Url $SiteURL -UseWebLogin  
   
#Get Parent Lookup List and Field from Child Lookup Field's Schema XML  
$LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName  
[Xml]$Schema = $LookupField.SchemaXml  
$ParentListID = $Schema.Field.Attributes["List"].'#text'  
$ParentField  = $Schema.field.Attributes["ShowField"].'#text'  
$ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValueText} | Select -First 1  
   
If($ParentLookupItem -ne $Null)  
{  
    #Update lookup field value using PnP PowerShell  
    Set-PnPListItem -List $ListName -Identity $ItemID -Values @{$LookupFieldName = $ParentLookupItem["ID"]}  
    Write-host "Lookup Column Value Updated Successfully!" -f Green  
}  
Else  
{  
    Write-host "Lookup Column Value '$LookupValueText' Not found in the Parent Lookup List!" -f Yellow  
}  

For Reference: SharePoint Online: PowerShell to Update Lookup Field Value
Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.