0

I have a PowerShell script that add's a new item to a list which has 2 lookup columns to different lists

first part of the script add's the item with the correct lookup value:

$ListLookupitem = $ListLookup.Items | where {$_["Title"] -eq "TEST789"}
    
$NewItem[$ColumnName] = [string]$ListLookupitem.ID + ";#" + $ListLookupitem.Title

But when I am using CAML query to get the 2nd Item from the next list using:

 $query = New-Object Microsoft.SharePoint.SPQuery

     $query.Query = "@
        <Where>
        <And>
        <Eq>
        <FieldRef Name='TESTCOLUME2'/>
        <Value Type='Text'>"TEST123"</Value>
        </Eq>
        <Eq>
        <FieldRef Name='TESTCOLUME1'/>
        <Value Type='Text'>"TEST345"</Value>
        </Eq>
        </And>
        </Where>";

     $ListLookup = $ListLookup.GetItems($query)
    
   $NewItem[$ColumnName2] = [string]$Lookupitem.ID + ";#" + $Lookupitem.Title

  $NewItem.Update()

it doesn't add anything into the lookup column. Reason I am using CAML query for 2nd part is the 2nd list has duplicate value for one of the columns. So, I am using CAML query to filter on 2nd list's columns.

1 Answer 1

0

Modify as this:

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
 
#Variables
$WebURL="http://sp2016/sites/dev"
$ParentListName="List3" #Lookup Parent List
$ChildListName="List4" #List to add new lookup value


 
#Get Web and List objects
$Web = Get-SPWeb $WebURL
$ParentList = $Web.Lists.tryGetList($ParentListName)
$ChildList = $Web.Lists.tryGetList($ChildListName)
$query = New-Object Microsoft.SharePoint.SPQuery

 $query.Query = "@
    <Where>
    <And>
    <Eq>
    <FieldRef Name='TESTCOLUME2'/>
    <Value Type='Text'>TEST123</Value>
    </Eq>
    <Eq>
    <FieldRef Name='TESTCOLUME1'/>
    <Value Type='Text'>TEST345</Value>
    </Eq>
    </And>
    </Where>";

$Lookupitem = $ParentList.GetItems($query)

$NewItem=$ChildList.Items.Add()
$NewItem["Title"]="Dec 2014 Milestone"
$NewItem["ColumnName2"] = [string]$Lookupitem.ID + ";#" + $Lookupitem.Title
$NewItem.Update()  
 

write-host "Lookup Field value has been updated!"

If you are using some fixed value in CAML Query, no need to append "" and $ListLookup should be modified to $Lookupitem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.