UpdateLookup field by Powershell Script

Target

Set up the value of the lookup filed(named Relation)in the SharePoint list by PowerShell script,

Summary

This script is based on the SortOrder filed as a unique key to configure the Relation filed, before running the script, please make sure two following points correctly:

1. The value of SortOrder field cannot be empty.
2. 确保以SortOrder字段作为操作逻辑的唯一标尺，逻辑如下：我们根据Current Item中的SortOrder字段值移除最后一位字符后，所得到值为父级的SortOrder值。如果SortOrder值只为一位，表示为顶级,额外处理.　如下案例：

   First Level: 1,2,3,4....

   Second LeveL: 11,12... , 21, 22..... 41, 42....

   Third Level: 111, 112..... 411, 412..... 421, 422...

   ....

3, Once we have completed this relation configuration. so we can run the powershell script to update

 Powershell Code:

 

Write-Host "PowerShell JScript Loading...."

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
 if ($snapin -eq $null) {
     Write-Host "Loading SharePoint Powershell Snapin..."
     Add-PSSnapin "Microsoft.SharePoint.Powershell"
 }

$siteUrl = "http://shndtpln0633:9200"    #Input the url of your country site.
$LeftNavListUrl = "lists/RelationList"        #Input the relative Url of the target List. 


#*****************User-Define Functions******************#

#update the Looupup Field value
Function UpdateFieldValue( $item, $value){
    try
    {
        $item["Relation"] = $value
        $item.Update()    
        
        Write-Host "the item($($item.Title)) was updated successfully" -f White
    }
    catch{
      write-host "Error, There an issue happened for updating the item($($item.Title))" -f red
    }
}

#Get Parent Value
Function GetParentValue($value){
    
    $result = $value
    if(([System.String]$value).length -cge 2){
    
        $result  = $value.substring(0, $value.length-1)
    }
    else{
        $result = "0"    
    }
    return $result
}

#Get the value from the parent item
Function GetLookupValue($List, $SortOrder){
    $res = ""
    try
    {
        $caml="<Where><Eq><FieldRef Name='SortOrder' /><Value Type='Number'>$SortOrder</Value></Eq></Where>"
        $query=new-object Microsoft.SharePoint.SPQuery
        $query.ViewAttributes = "Scope='Recursive'"
        $query.Query=$caml | Write-Output
        $items=$List.GetItems($query)        
        if ($items -cne $null){
            if($items.Count -cge  1){                            
                $res ="$($items[0].ID);#$($items[0].Title)"
            }
        }
    }
    catch
    {
        write-host "Error, there an error for getting Lookup value ." -f white
        write-host $Error[0] -f red
    }
    return $res
}
#*****************************************


#******************************************
#*********** Main Process******************
#******************************************

$site = Get-SPSite -Identity $siteUrl
$LeftNavList = $site.RootWeb.GetList($LeftNavListUrl)

foreach($item in $LeftNavList.Items){    
    if(($item.Title -ne "Top") -and ($item.Title -ne "Parent")) {    
        $sOrder = $item["SortOrder"].ToString()        
        if($sOrder -ne ""){        
        
            $pOrder =  GetParentValue $sOrder        
             $lookupParent = GetLookupValue $LeftNavList $pOrder
            if($lookupParent -ne ""){
                UpdateFieldValue $item $lookupParent 
            }
        }
        else{        
            write-host "The key(SortOrder field) has no value in the item $($item.Title), Please Check." -f red        
        }
        
    
    }
}
Write-Host "Operated Completedly!"
#Dispose of the site object
$site.Dispose()