Friday, August 3, 2012

How to update Lookup field through PowerShell

I am surprised that there was no PowerShell sample scripts to update lookup field. So I think it may help other developers with mine.

The point is, normally we don't have "lookup Id" if we need to update a lookup field through PowerShell script. So the function below "GetItemIdFromTitle()" can get the "id" through "title".

Hopefully it can save you some time.  :-)


Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction "SilentlyContinue"

$TargetSiteUrl = "http://server/sites/rootsite/subsite1"
$TargetListName = "LinkList"
$LookupListName = "LookupList"

$LookupFieldName = " LookupField"

$targetWeb = Get-SPWeb $TargetSiteUrl
if ($targetWeb -eq $null) {
    Write-Host -foregroundcolor Red "Web Site (" $TargetSiteUrl ") doesn't exists.  Action aborted."

$MyList = $targetWeb.Lists[$TargetListName]
# if lookup list are not in the same sub site as the targeted list
$lookupWeb = $targetWeb.Site.RootWeb
$LookupList = $lookupWeb.Lists[$LookupListName]

function GetItemIdFromTitle([string]$LookupTitle, [ref]$LookupId)
$LookupItem = $LookupList.Items | Where-Object {$_.Title -eq $LookupTitle}
$LookupId.Value = $LookupItem.ID

function NewSPListItem([string]$LookupTitle, [string]$itemDescription, [string]$itemUrl)
$spAssignment = Start-SPAssignment

$newItem = $MyList.Items.Add()

$LookupIdValue = "0"
GetItemIdFromTitle $LookupTitle ([ref]$LookupIdValue)
$lookupRegion = New-Object Microsoft.Sharepoint.SPFieldLookupValue($LookupIdValue, $LookupTitle)
$newItem[$LookupFieldName] = $lookupRegion

$urlValue = New-Object Microsoft.SharePoint.SPFieldUrlValue("")
$urlValue.Description = $itemDescription;
$urlValue.Url =  $itemUrl
$newItem["URL"] = $urlValue


Write-Host "List item '" $LookupTitle "' is inserted!"

Stop-SPAssignment $spAssignment

NewSPListItem "LookupTitleA" "google" ""
NewSPListItem "LookupTitleB" "outlook" ""
NewSPListItem "LookupTitleC" "abc" ""
NewSPListItem "LookupTitleD" "php" ""


rm function:/GetItemIdFromTitle
rm function:/NewSPListItem

Write-Host "Finished! Press enter key to exit." -ForegroundColor Green

1 comment: