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:

Let’s 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 in time, let’s use the lookup field ID instead of text. Here is the PowerShell CAML query for the 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"]
}

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

One thought on “SharePoint Online: CAML Query for Lookup Field in PowerShell

  • September 13, 2021 at 11:52 PM

    I have a small issue. I have a list that contains people picker fields requested by the customer. There are multiple people for each line item in the column. I am trying to use JavaScript to Extract email addresses to populate columns I use for emailing approval notices with custom forms. I am using a simple CAML query and the people picker are objects. How do I get the true email addresses to simplify my work?
    here’s the while I am using:
    while (listItemEnumerator1.moveNext())
    {
    var oListItem = listItemEnumerator1.get_current();
    var Group1 = oListItem.get_item(‘Group’);
    var Group2 = oListItem.get_item(‘Squad’);
    var Group3 = oListItem.get_item(‘XONames’);
    var Group4 = oListItem.get_item(‘GroupSafety’);

    I need to extract the info in Group3 and Group4. I think I am supposed to do a lookup but I have no idea what the syntax for that is. Can you help?

    Reply

Leave a Reply