kwizcom banner advertisement

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 simple use:
$ListItem[LookupFieldName]="Parent-Item-ID;#LookupValue"
 E.g. Say you have a lookup column called "Parent Project Name" in "Project Metrics" list, referencing "Project Name" field from "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!"

Add 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()   
Get / Set Lookup Field Values in SharePoint List using PowerShell Get / Set Lookup Field Values in SharePoint List using PowerShell Reviewed by Salaudeen Rajack on December 21, 2013 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.