SharePoint Online: PowerShell to Update Lookup Field Value

Requirement: Update 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 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 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 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 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 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!"
}
To retrieve the lookup field column value in SharePoint Online using PowerShell, use: Get lookup field using PowerShell in SharePoint Online
SharePoint Online: PowerShell to Update Lookup Field Value SharePoint Online: PowerShell to Update Lookup Field Value Reviewed by Salaudeen Rajack on March 20, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.