0

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)
0

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)
|improve this answer|||||
  • Thank you! i just changed $venue = Get-ListItems -Context $context -ListTitle "Venues" -itemId $vId to use Get-ListItemById. Thank You! – Page F.P.T Mar 11 '19 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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