Get / Set Lookup Field Values in SharePoint List using 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.

update lookup field value powershell

Get lookup field value from SharePoint list using PowerShell:

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue

#Variables
$WebURL="http://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="http://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

    } 
} 
update lookup column value powershell

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="http://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="http://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 is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

One thought on “Get / Set Lookup Field Values in SharePoint List using PowerShell

  • November 17, 2018 at 2:44 PM

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

    Reply

Leave a Reply