kwizcom banner advertisement

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
Lets say, we want to filter and get all projects where "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 user field using UserID property. You can also filter user field based on 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
Lets get all list items where a particular user is listed under 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 my another post written for SharePoint On-premises to filter user field using CAML queries: SharePoint CAML Query to Filter by User Field in PowerShell
SharePoint Online: CAML Query for Person or Group Field in PowerShell SharePoint Online: CAML Query for Person or Group Field in PowerShell Reviewed by Salaudeen Rajack on April 04, 2017 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.