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.

Improve this question

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.