SharePoint CAML query examples of working with People and Groups

Some of the more elegant and powerful solution built with SharePoint have some level of personalization for the user. Here are some example CAML queries that allow you to query a SharePoint list and return items where the current user is listed in a Person column or explicitly in a Person/Groups column and even if they a a member of a group in a Person/Groups column. First let’s give our examples a bit of context. Here’s a SharePoint list of Projects where each project has:

  • Status (Choice column with valid values of Open, Closed, Cancelled)
  • Project Manager (single valued Person column – no groups allowed)
  • Project Team (multi value Person/Group column – People allowed explicitly and/or Groups allowed)

sharepoint-office365-people-group-caml-cameron-dwyer-02-list-data   And the column definitions: sharepoint-office365-people-group-caml-cameron-dwyer-01-list-columns   Now here’s some of the things you might want to do when querying this list and the CAML to achieve it.

Where current user is in a single values Person/Groups column

This will give us all items where the current user is in the Project Manager column


<Where>

<Eq>

<FieldRef Name='Project_x0020_Manager'/>

<Value Type='Integer'>

<UserID Type='Integer'/>

</Value>

</Eq>

</Where>

Where current user is a member of a groups that is listed in a person/groups column (that allows single or multiple values)

This will give us all items where the current user is a member of a group listed in the Project Team column. Note: it will not give you items where the user is listed explicitly by name in the Project Team column.


<Where>

<Membership Type='CurrentUserGroups'>

<FieldRef Name='Project_x0020_Team'/>

</Membership>

</Where>

Where current user may be named explicitly or may be a member of a group listed in a person/groups column (that allows single or multiple values)

This will give us all items where the current user is either listed explicitly by name or is a member of a group listed in the Project Team column.


<Where>

<Or>

<Membership Type='CurrentUserGroups'>

<FieldRef Name='Project_x0020_Team'/>

</Membership>

<Includes>

<FieldRef Name='Project_x0020_Team'/>

<Value Type='Integer'>

<UserID Type='Integer'/>

</Value>

</Includes>

</Or>

</Where>

Combining Item Metadata with User/Groups Column Queries

This will give us all items: Where the Project Status (choice type) column has a status of “Open” AND Where current user may be named explicitly or may be a member of a group listed in the “Project Team” person/groups column (that allows single or multiple values) OR Where current user is explicitly named in the single value “Project Manager” Person/Groups column


<Where>

<And>

<Eq>

<FieldRef Name='Project_x0020_Status' />

<Value Type='Choice'>Open</Value>

</Eq>

<Or>

<Eq>

<FieldRef Name='Project_x0020_Manager' />

<Value Type='Integer'>

<UserID Type='Integer' />

</Value>

</Eq>

<Or>

<Membership Type='CurrentUserGroups'>

<FieldRef Name='Project_x0020_Team'/>

</Membership>

<Includes>

<FieldRef Name='Project_x0020_Team'/>

<Value Type='Integer'>

<UserID Type='Integer'/>

</Value>

</Includes>

</Or>

</Or>

</And>

</Where>

Advertisements

About Cameron Dwyer

Architect and developer at OnePlace Solutions. Passionate about delivering compelling solutions on the Office 365/SharePoint platform. Addicted to coffee.

Posted on February 26, 2015, in Office 365, SharePoint and tagged , , , , , , , . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: