Fill sharepoint list with lookup field
function DefineLookupValue
{
param ([object]$sourceList, [string] $fieldName, [string]$value)
try
{
#prepare lookup value-getting Id of 'Section' from another list
$spQuery = new-object Microsoft.SharePoint.SPQuery
$camlQuery = "<Where>
<Eq>
<FieldRef Name='" + $fieldName + "' />
<Value Type='Text'>"+$value+"</Value>
</Eq>
</Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 1
$spListItemCollection = $sourceList.GetItems($spQuery)
if($spListItemCollection.Count -ne 0)
{
$returnValue=$spListItemCollection[0].Id.ToString() +";#" + $value
return $returnValue
}
else
{
write-host "Value "$value " was not found in " $sourceList "." -ForegroundColor "red"
$returnValue=""
return ""
}
}
catch [Exception]
{
write-error $_
return ""
}
}
function ListItemExists
{
param ([object]$sourceList, [string] $fieldName, [string]$value)
try
{
#prepare lookup value-getting Id of 'Section' from another list
$spQuery = new-object Microsoft.SharePoint.SPQuery
$camlQuery = "<Where>
<Eq>
<FieldRef Name='"+ $fieldName + "' />
<Value Type='Text'>"+$value+"</Value>
</Eq>
</Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 1
$spListItemCollection = $sourceList.GetItems($spQuery)
if($spListItemCollection.Count -ne 0)
{
write-host "Item '" $value "' is already present in the list '" $sourceList.Title "'"
return $true
}
else
{
return $false
}
}
catch [Exception]
{
write-error $_
return $false
}
}
function FindListItem
{
param ([object]$sourceList, [string] $fieldName, [string]$value)
try
{
#prepare lookup value-getting Id of 'Section' from another list
$spQuery = new-object Microsoft.SharePoint.SPQuery
$camlQuery = "<Where>
<Eq>
<FieldRef Name='"+ $fieldName + "' />
<Value Type='Text'>"+$value+"</Value>
</Eq>
</Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 1
$spListItemCollection = $sourceList.GetItems($spQuery)
if($spListItemCollection.Count -ne 0)
{
return $spListItemCollection[0]
}
else
{
return $null
}
}
catch [Exception]
{
write-error $_
return $null
}
}
function DefineLookupValueMulti
{
param ([object]$sourceList, [string] $fieldName, [string]$value)
try
{
if (-not [string]::IsNullOrEmpty($value))
{
$tags=$value.Split(',')
$result=[string]::Empty
foreach($tag in $tags)
{
$tag=$tag.Trim()
#prepare lookup value-getting Id of 'Section' from another list
$spQuery = new-object Microsoft.SharePoint.SPQuery
$camlQuery = "<Where>
<Eq>
<FieldRef Name='"+ $fieldName +"' />
<Value Type='Text'>"+ $tag +"</Value>
</Eq>
</Where>"
$spQuery.Query = $camlQuery
$spQuery.RowLimit = 1
$spListItemCollection = $sourceList.GetItems($spQuery)
if($spListItemCollection.Count -ne 0)
{
$addTag=$spListItemCollection[0].Id.ToString() +";#" + $tag
$result+=$addTag + ";#"
}
else
{
write-host "Value "$tag " was not found in " $sourceList "."
}
}
#remove last ; from string
$result = $result.substring(0,$result.length-2)
return $result
}
else #if no value specified
{
return [string]::Empty
}
}
catch [Exception]
{
write-error $_
return ""
}
}