SharePoint Diary » SharePoint » Get / Set Lookup Field Values in SharePoint List using PowerShell

Get / Set Lookup Field Values in SharePoint List using PowerShell

June 24, 2021 Salaudeen Rajack 2 Comments get lookup field value sharepoint powershell, set lookup field value sharepoint powershell, sharepoint powershell update lookup field value, update multi value lookup field sharepoint powershell

Here is my PowerShell code snippets to read/write lookup field values in SharePoint lists.

Scenario: Consider you have a parent list called “Parent Projects” and child list “Project Milestones”. The “Parent Project Name” field from child list is being looked up from the parent list’s “Project Name” field.

Get lookup field value from SharePoint list using PowerShell:

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$WebURL="https://projects.crescent.com"
$ListName="Project Milestones"
$LookupFieldName="Parent Project"

#Get Web and List objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists.tryGetList($ListName)

if($List -ne $null)
{
    #Get a Row from list
    $Item = $List.Items[0]
    
    #Get Lookup Field Value
    $Lookup = New-Object Microsoft.SharePoint.SPFieldLookupValue($Item[$LookupFieldName])
    
    #Send the output to screen
    write-host $Lookup.LookupValue
}

Read from Multiple-Value Lookup Field Value:

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$WebURL="https://projects.crescent.com"
$ListName="Project Milestones"
$LookupFieldName="Parent Project"

#Get Web and List objects
$Web = Get-SPWeb $WebURL
$List = $Web.Lists.tryGetList($ListName)

if($List -ne $null)
{
    #Get a Row from list
    $Item = $List.Items[0]
    
    #Get Lookup Field Value
    $LookupValueCollection = New-Object Microsoft.SharePoint.SPFieldLookupValueCollection($Item[$LookupFieldName].ToString())

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

    } 
}

How to update lookup column in SharePoint using PowerShell?

To update a lookup column, you can simply use: $ListItem[LookupFieldName]=”Parent-Item-ID;#LookupValue”
E.g. Say you have a lookup column called “Parent Project Name” in “Project Metrics” list, referencing the “Project Name” field from the “Parent Projects” list as in the above image. Now, to set the lookup column, you can use:

$ProjectMetrics["ParentProjectName"]="2;#Cloud Devlopment"

Where: 2 is the ID of the row in the Parent Projects list where Project name is: Cloud development.

PowerShell to Set lookup field in SharePoint:

Here is how to update lookup field in SharePoint using PowerShell

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$WebURL="https://projects.crescent.com"
$ParentListName="Parent Projects" #Lookup Parent List
$ChildListName="Project Milestones" #List to add new lookup value
$ParentListLookupField="Project Name" 
$ChildListLookupField ="Parent Project"
$ParentListLookupValue="Cloud Development" #Parent Project value

#Get Web and List objects
$Web = Get-SPWeb $WebURL
$ParentList = $Web.Lists.tryGetList($ParentListName) 
$ChildList = $Web.Lists.tryGetList($ChildListName) 

#Add new item in Child List - Project Milestones
$NewItem=$ChildList.Items.Add()

#Set Title field for the new list item
$NewItem["Title"]="Dec 2014 Milestone"

#Get Lookup Item from Parent List
$ParentListLookupItem = $ParentList.Items | where {$_[$ParentListLookupField] -eq $ParentListLookupValue}
	
#Set Lookup Field Value in Child List
$NewItem[$ChildListLookupField] = $ParentListLookupItem.ID
$NewItem.Update()

#Send the output to screen
write-host "Lookup Field value has been updated!"

Update Multiple Values Lookup column using PowerShell:
How about updating lookup field with “Allow Multiple Values” enabled?

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$WebURL="https://projects.crescent.com"

#Get Web and List objects
$Web = Get-SPWeb $WebURL
$ParentProjects = $Web.Lists.tryGetList("Parent Projects") 
$ProjectMileStones = $Web.Lists.tryGetList("Project Milestones") 

#Add new item in Child List - Project Milestones
$NewMilestone=$ProjectMileStones.Items.Add()

#Set Title field for the new list item
$NewMilestone["Title"]="Dec 2014 Milestone"

#Object for MultiValue Lookup Field Values
$LookupValues = New-Object Microsoft.SharePoint.SPFieldLookupValueCollection

#Get a Lookup Item from Parent List
$ParentProjectItem = $ParentProjects.Items | where {$_["Project Name"] -eq "Cloud Development"}
$LookupValue = New-Object Microsoft.SharePoint.SPFieldLookupValue
$LookupValue.LookupId = $ParentProjectItem.ID
$LookupValues.Add($LookupValue) #Add to Collection

#Get an another Lookup Item from Parent List
$ParentProjectItem = $ParentProjects.Items | where {$_["Project Name"] -eq "Smart Home"}
$LookupValue = New-Object Microsoft.SharePoint.SPFieldLookupValue
$LookupValue.LookupId = $ParentProjectItem.ID
$LookupValues.Add($LookupValue) #Add to Collection

#Set multi-value lookup field value
$NewMilestone["Parent Project"] = $LookupValues

$NewMilestone.Update()

To get or set Lookup column values in SharePoint Online, use my another post: PowerShell to Retrieve/Update Lookup Column Value in SharePoint Online

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

2 thoughts on “Get / Set Lookup Field Values in SharePoint List using PowerShell”

Andre
November 24, 2022 at 1:17 PM
Permalink

I could only save multiple lookup values after converting the collection to a string
Reply
Anonymous
November 17, 2018 at 2:44 PM
Permalink

Hi Salaudeen, Could you provide the CSOM PowerShell script to read/write Lookup column values in SharePoint Online, please?
Reply

SharePoint Diary