powershell update single line of text where lookup id is equal to value

Question

I would like to update a single line of text field of my list where the lookup id matches the id of the lookup value in the other list. i did it like this but i cannot figure out the logic to make it work.

what i needs that i need to get the four hour and eight hour value from the venues list and update it in the BookingList where venue is a lookup.so what i did was to use foreach venues and foreach bookings but that didn't work.

here is a snippet

Function Get-ListItems([Microsoft.SharePoint.Client.ClientContext]$Context, [String]$ListTitle) {
    $list = $Context.Web.Lists.GetByTitle($listTitle)
    $qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
    $items = $list.GetItems($qry)
    $Context.Load($items)
    $Context.ExecuteQuery()
    return $items 
}


$venues = Get-ListItems -Context $context -ListTitle "Venues" 

$list = $context.Web.Lists.GetByTitle("BookingList")
$context.ExecuteQuery()
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = @"
                <View Scope="RecursiveAll">
                    <Query>
                        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
                    </Query>
                    <RowLimit Paged="TRUE">5000</RowLimit>
                </View>
"@

do
{
    $started = Get-Date
    $items = $list.GetItems($query)
    $context.Load($items)
    $context.ExecuteQuery()

    $query.ListItemCollectionPosition = $items.ListItemCollectionPosition

    if ($items.Count -eq 0) { break }

   foreach($item in $items) 
   {

    foreach($v in $venues){
     #Get Lookup Field Value
        $lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$item["NG_Venues"][0]
        Write-host $item.ID
        Write-Host $lookup.LookupValue " " $lookup.LookupId
        Write-Host $v.ID
       if($v.ID -eq $lookup.LookupId){
            Write-Host $v["Title"]
            Write-Host $v["Four_Hour_Block"]
            $item["VenuePriceForBooking4HrFY"] = $v["Four_Hour_Block"]
            $item["VenuePriceForBooking8HrFY"] = $v["Eight_Hour_Block"]

       }

        $item["EventVenue"] = $lookup.LookupId
        $item.Update()
        $context.ExecuteQuery();
    }



   }

    $context.ExecuteQuery()
    Write-Host "Time elapsed: $((Get-Date) - $started)"
    # ListItemCollectionPosition is null if there is no other page
} while ($query.ListItemCollectionPosition -ne $null)

Answer 1

Try the following commands:

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$SiteUrl = "https://<site>"
$UserName = "<account>"
$SecurePassword = Read-Host -Prompt "Enter password" -AsSecureString
# Connect to site
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $SecurePassword)
$Context.Credentials = $credentials
$Context.ExecuteQuery() 


Function Get-ListItemById([Microsoft.SharePoint.Client.ClientContext]$Context, [String]$ListTitle,$itemId) {
    $list = $Context.Web.Lists.GetByTitle($listTitle)
    $item = $list.GetItemById($itemId) 
    $Context.Load($item)
    $Context.ExecuteQuery()
    return $item 
}


$list = $context.Web.Lists.GetByTitle("BookingList")
$context.ExecuteQuery()
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$query.ViewXml = @"
                <View Scope="RecursiveAll">
                    <Query>
                        <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
                    </Query>
                    <RowLimit Paged="TRUE">5000</RowLimit>
                </View>
"@

do
{
    $started = Get-Date
    $items = $list.GetItems($query)
    $context.Load($items)
    $context.ExecuteQuery()

    $query.ListItemCollectionPosition = $items.ListItemCollectionPosition

    if ($items.Count -eq 0) { break }

   foreach($item in $items) 
   {

     #Get Lookup Field Value
        $lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$item["NG_Venues"][0]
        Write-host $item.ID
        Write-Host $lookup.LookupValue " " $lookup.LookupId
        $vId = $lookup.LookupId
        $venue = Get-ListItems -Context $context -ListTitle "Venues" -itemId $vId
        Write-Host $venue["Title"]
        Write-Host $venue["Four_Hour_Block"]
        $item["VenuePriceForBooking4HrFY"] = $venue["Four_Hour_Block"]
        $item["VenuePriceForBooking8HrFY"] = $venue["Eight_Hour_Block"]
        $item.Update()
   }

    $context.ExecuteQuery()
    Write-Host "Time elapsed: $((Get-Date) - $started)"
    # ListItemCollectionPosition is null if there is no other page
} while ($query.ListItemCollectionPosition -ne $null)