Saturday, December 21, 2013

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:
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()   



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...