PowerShell-CAML SPQuery to Filter List Items based of Person or Group Field Value
Requirement:
In a project tracking SharePoint list, want to quickly get the list of projects where a particular user is listed as Project Manager – person or group (People picker) field.
Solution: Use SPQuery with PowerShell as in the below example.
PowerShell to Filter List Items based on Person or Group Field:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
#Configuration parameters
$SiteURL = "http://portal.crescent.com/projectpipeline/"
$ListName = "Projects"
$FieldName= "ProjectManager"
$UserAccount="Crescent\Omar"
#Get the User Account
$User = Get-SPUser -Identity $UserAccount -Web $SiteURL
#Query to filter List Items which contains user account
$SPQuery = new-object Microsoft.SharePoint.SPQuery
$Query = "<Where><Eq><FieldRef Name='ProjectManager' LookupId='TRUE'/><Value Type='User'>$($User.ID)</Value></Eq></Where>"
$SPQuery.Query=$Query
#Get site and List objects
$web = Get-SPWeb $SiteURL
$List = $web.Lists.TryGetList($ListName)
#Filter List Items by Query
$ListItems = $List.GetItems($SPQuery)
foreach($item in $ListItems)
{
write-host $Item["ProjectName"]
}
write-host "Total Number of Projects Found:" $ListItems.Count
