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
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="[email protected]"
#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>"
Here is another post for SharePoint On-premises to filter user field using CAML queries: SharePoint CAML Query to Filter by User Field in PowerShell
