kwizcom banner advertisement

SharePoint Online: Get-Set Lookup Column Values using Powershell

Requirement: Get or Set Lookup field values in SharePoint Online using PowerShell.

Scenario: We've 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 scripts to get and set lookup field values.

sharepoint online get set lookup field values using powershell

PowerShell to Get Lookup Field Value in SharePoint Online: 
To retrieve the value of lookup column in a 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"

#Set parameter valuesss
$SiteURL="https://crescent.sharepoint.com/Sites/PMO"
$ListName="Project Milestones"
$FieldName="ParentProjectName" #Internal Name
$ListItemID="25"

#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 List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Lookup Field Value
$Lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$ListItem[$FieldName]
Write-host $Lookup.LookupValue

SharePoint Online: Retrieve Multi-Value Lookup Field Values using PowerShell:
When "Allow Multiple Values" is enabled, it becomes multi-valued lookup column. So the script to read lookup column goes here.
#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"

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/PMO"
$ListName="Projects"
$FieldName="ParentProjects" #Internal Name
$ListItemID="25"

#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 List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Multi-Lookup Field Value
$LookupValueCollection = [Microsoft.SharePoint.Client.FieldLookupValue[]]$ListItem[$FieldName]

Foreach ($LookupValue in $LookupValueCollection)
{
    write-host $LookupValue.LookupValue
 
}

Set Lookup Field Value using PowerShell in SharePoint Online:
To update a lookup column in SharePoint Online, 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"
$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!"
    }
}

#Update Lookup Field 
If($LookupValueCollection.length -gt 0)
{
    #Convert the array to Lookup value collection
    $LookupValueColl = [Microsoft.SharePoint.Client.FieldLookupValue[]]$LookupValueCollection 

    #Set Multi-Lookup colum value
    $ListItem[$ChildListLookupField] = $LookupValueColl
    $ListItem.Update()
    $Ctx.ExecuteQuery()
    Write-host -f Green "Multi-Lookup Field value has been updated!"
}

Here is my another PowerShell script to get or set lookup field values in SharePoint On-premises.
SharePoint Online: Get-Set Lookup Column Values using Powershell SharePoint Online: Get-Set Lookup Column Values using Powershell Reviewed by Salaudeen Rajack on November 17, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.