SharePoint Online: CAML Query for Person or Group Field in PowerShell

Requirement: SharePoint Online CAML Query for Person or Group field

SharePoint Online: CAML Query to Filter Person or Group Field in PowerShell

In SharePoint Online, the Person or Group field allows users to select one or more people or groups from a SharePoint site. CAML (Collaborative Application Markup Language) is an XML-based query language used in SharePoint to query data from lists and libraries. In this article, we will discuss how to create a CAML query for a Person or Group field in PowerShell.

Let’s say we want to filter and get all projects where the “Project Manager” user field value is a particular person.

#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"
$UserID="salaudeen@crescent.com"
 
#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 and User Objects
$List = $Ctx.Web.lists.GetByTitle($ListName)
$User = $Ctx.web.EnsureUser($UserID)
$Ctx.Load($User)
$Ctx.ExecuteQuery()
$UserID = $User.Id

#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ProjectManager' LookupId='TRUE'/><Value Type='User'>$UserID</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)
{ 
    Write-host $Item.id
    Write-host $Item["Title"]
}

SharePoint CAML Query Filter by User Name:

The above PowerShell script filters the user field using the UserID property. You can also filter the user field based on the user’s display name:

<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ProjectManager'/><Value Type='Text'>Salaudeen Rajack</Value>
            </Eq>
        </Where>
    </Query>
</View>

Get List Items Where the Person or Group Field is Current User:
SharePoint CAML to filter based on current user

<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='ProjectManager'/><Value Type='Integer'><UserID /></Value>
            </Eq>
        </Where>
    </Query>
</View>

SharePoint CAML Query for Multi-User Field
Let’s get all list items where a particular user is listed under the multi-valued people picker column “Project Members”.

<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Includes>
                <FieldRef Name='ProjectMembers' LookupId='TRUE'/><Value Type='User'>$UserID</Value>
            </Includes>
        </Where>
    </Query>
</View>" 

Following the scripts and examples provided in this article, you can easily create a CAML query for a Person or Group field in PowerShell and query data from a SharePoint list. Here is another post for SharePoint On-premises to filter user fields using CAML queries: SharePoint CAML Query to Filter by User Field in PowerShell

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published. Required fields are marked *