SharePoint Online: PowerShell to Update Lookup Field Value

Requirement: Update the Lookup field using PowerShell in SharePoint Online.

We have a parent list called "Parent Projects" and child list "Project Milestones". The "Parent Project Name" field in the child list is being looked up from the parent list's "Project Name" field. Here is my PowerShell script to set lookup field values in the SharePoint Online list.
update lookup field using powershell in sharepoint online


SharePoint Online PowerShell to Update Lookup Field  
To update a lookup column value in the SharePoint Online list, use this PowerShell.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Function to get the ID from Parent Lookup List -  Based on the Provided value
Function Get-ParentLookupID($ParentListName, $ParentListLookupField, $ParentListLookupValue)
{
    $ParentList = $Ctx.Web.lists.GetByTitle($ParentListName)

    #Get the Parent List Item Filtered by given Lookup Value
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='$($ParentListLookupField)'/><Value Type='Text'>$($ParentListLookupValue)</Value></Eq></Where></Query></View>"
    $ListItems = $ParentList.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    #Get the ID of the List Item
    If($ListItems.count -gt 0)
    {
        Return $ListItems[0].ID #Get the first item - If there are more than One
    }
    else
    {
        Return $Null
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/pmo"
$ListItemID="25"
$ParentListName= "Parent Projects" #Lookup Parent List
$ChildListName="Project Milestones" #List to add new lookup value
$ParentListLookupField= "ProjectName" #Internal Name
$ChildListLookupField ="ParentProject" #Internal Name
$ParentListLookupValue="Cloud Development" #Parent Project value

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials

#Get the Child List & Item to update
$ChildList = $Ctx.Web.lists.GetByTitle($ChildListName) 
$ListItem = $ChildList.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get the Lookup ID from the Parent List
$LookupID = Get-ParentLookupID $ParentListName $ParentListLookupField $ParentListLookupValue

#Check if the given Lookup Item found in the Parent List
If($LookupID -ne $Null)
{
    #Update Lookup Field using Parent Lookup Item ID
    $ListItem[$ChildListLookupField] = $LookupID
    $ListItem.Update()
    $Ctx.ExecuteQuery()

    Write-host -f Green "Lookup Field value has been updated!"
}
else
{
    write-host -f Yellow "Lookup Item '$($ParentListLookupValue)' doesn't exist in the Parent List!"
} 

PowerShell to Update Multi-Valued Lookup Column Value
Here is the PowerShell example for setting a multi-valued lookup column in SharePoint Online.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Function to get the ID from Parent Lookup List -  Based on the Provided value
Function Get-ParentLookupID($ParentListName, $ParentListLookupField, $ParentListLookupValue)
{
    $ParentList = $Ctx.Web.lists.GetByTitle($ParentListName)

    #Get the Parent List Item Filtered by given Lookup Value
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='$($ParentListLookupField)'/><Value Type='Text'>$($ParentListLookupValue)</Value></Eq></Where></Query></View>"
    $ListItems = $ParentList.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    #Get the ID of the List Item
    If($ListItems.count -gt 0)
    {
        Return $ListItems[0].ID #Get the first item - If there are more than One
    }
    else
    {
        Return $Null
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/pmo"
$ListItemID="25"
$ParentListName= "Parent Projects" #Lookup Parent List
$ChildListName="Project Milestones" #List to add new lookup value
$ParentListLookupField= "ProjectName"
$ChildListLookupField ="ParentProject"
[email protected]("Address Books", "Holidays", "Invoices") #Parent Project value

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials

#Get the Child List & Item to update
$ChildList = $Ctx.Web.lists.GetByTitle($ChildListName) 
$ListItem = $ChildList.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

$LookupValueCollection = @()
ForEach($ParentListLookupValue in $ParentListLookupValues)
{
    #Get the Lookup ID from the Parent List
    $LookupID = Get-ParentLookupID $ParentListName $ParentListLookupField $ParentListLookupValue

    #Check if the given Lookup Item found in the Parent List
    If($LookupID -ne $Null)
    {
        $lookupValue = New-Object Microsoft.SharePoint.Client.FieldLookupValue
        $lookupValue.LookupId = $LookupID
        $LookupValueCollection += $LookupValue
    }
    else
    {
        write-host -f Yellow "Lookup Item '$($ParentListLookupValue)' doesn't exist in the Parent List!"
    }
}

#sharepoint online PowerShell update lookup field
If($LookupValueCollection.length -gt 0)
{
    #Convert the array to Lookup value collection
    $LookupValueColl = [Microsoft.SharePoint.Client.FieldLookupValue[]]$LookupValueCollection 

    #update lookup field using powershell in sharepoint online
    $ListItem[$ChildListLookupField] = $LookupValueColl
    $ListItem.Update()
    $Ctx.ExecuteQuery()
    Write-host -f Green "Multi-Lookup Field value has been updated!"
}

PnP PowerShell to Set Lookup Field Value in SharePoint Online:
Lookup fields internally store the ID of the parent lookup item. To update lookup field value in SharePoint Online, use Values @{"Lookup" = "1"}, where the "1" is the ID of the parent lookup item.
#Config Variables
$SiteURL = "https://crescentintranet.sharepoint.com/sites/marketing"
$ListName = "ProjectMilestones"
$FieldName = "Parent_x0020_Project"
$ItemID = 1
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
#Update lookup field value using PnP PowerShell
Set-PnPListItem -List $ListName -Identity $ItemID -Values @{$FieldName = 3}
Here "3" is the ID of the parent lookup item. Similarly, to update a multi-valued lookup field, just specify the IDs of parent lookup items comma separated.

SharePoint Online: Update Lookup Field Value using PnP PowerShell 
We may not know the ID of the parent lookup item always and may want to set the lookup field value by text value. So, Here is how we can update lookup field value in SharePoint Online with a lookup text value.
#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/pmo"
$ListName = "Project Milestones"
$LookupFieldName = "Parent_x0020_Project"
$LookupValueText = "VMware Upgrade"
$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
}

To retrieve the lookup field column value in SharePoint Online using PowerShell, use: Get lookup field using PowerShell in SharePoint Online

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.