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 a 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 example:

#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"
$ParentListLookupValues=@("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://Crescent.sharepoint.com/sites/marketing"
$ListName = "ProjectMilestones"
$FieldName = "Parent_x0020_Project"
$ItemID = 1
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
 
#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 -Interactive

#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

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

2 thoughts on “SharePoint Online: PowerShell to Update Lookup Field Value

  • Your method works when running through ISE but somehow this method does not work when I run through task scheduler.

    Reply
    • If you gonna schedule it with Task scheduler, The -Interactive won’t work unless you are logged in already! So, use either the AppID/Password method or to use store/retrieve user name password methods.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *