kwizcom banner advertisement

SharePoint Online: CAML Query for Lookup Field in PowerShell

Requirement: CAML Query for Lookup field in SharePoint Online using PowerShell

CAML for SharePoint Lookup Field Value:
Lets filter projects by parent project lookup field value "Address Book". Here is the SharePoint CAML for lookup field.
#Load SharePoint CSOM Assemblies
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"
 
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupValue="Address Book"
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ParentProject'/><Value Type='Lookup'>$LookupValue</Value>
            </Eq>
        </Where>
    </Query>
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{
    #Do something
    Write-host $Item.id
    Write-host $Item["Title"]
}

SharePoint CAML Query to Filter Lookup Field by ID:
Since the lookup text defined in the parent list may change at any point of time, lets use the lookup field ID instead of text. Here is the PowerShell CAML query for lookup column.
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupID="25" #ID of the Parent Lookup Item
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ParentProject' LookupId='TRUE'/><Value Type='Lookup'>$LookupID</Value>
            </Eq>
        </Where>
    </Query>
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{
    #Do something
    Write-host $Item["Title"]
}

SharePoint Online CAML for Multi-Lookup Field:
Here is the SharePoint CAML query for lookupmulti column which filters where the "Parent Project" lookup field value contains both "Address Book" and "Announcements".
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
 
#Get Credentials to connect
$Cred= Get-Credential
  
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <And>
                <Includes>
                    <FieldRef Name='ParentProject'/>
                    <Value Type='LookupMulti'>Address Book</Value>
                </Includes>
                <Includes>
                    <FieldRef Name='ParentProject'/>
                    <Value Type='LookupMulti'>Announcements</Value>
                </Includes>
            </And>
        </Where>
    </Query>
</View>"

#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()

Write-host "Total Number of Items:"$ListItems.count

#Loop through each List Item
ForEach($Item in $ListItems)
{
    #Do something
    Write-host $Item["Title"]
}
SharePoint Online: CAML Query for Lookup Field in PowerShell SharePoint Online: CAML Query for Lookup Field in PowerShell Reviewed by Salaudeen Rajack on April 05, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.