CAML Query – Show element not assigned to me or my groups

SP2013

Hello everyone,

Some time ago, I made a summary post on « Filter on current user and current user groups »

This query uses the “membership” parameter to recover the current user groups. However, some time ago, a request was made on the msdn forums for reverse query! Namely, retrieve elements which are not assigned either to the current user or to one of its groups!

After doing several tests, I did not find any conclusive parameter and I turned to a solution of dynamic creation of the query.

Here it is


SPQuery query = new SPQuery();

query.Query = CreateQuery(web.SiteGroups);

.....

string CreateQuery(SPGroupCollection groups)

{

string queryToReturn = "<Where>";

int countAnd = 0;

List<string> listGroupName=new List<string>();

//Get list of group Name and count the number

foreach (SPGroup group in groups)

{

if (group.ContainsCurrentUser)

{

listGroupName.Add(group.Name);

countAnd++;

}

}

//If there is a least one group where the current user is in, we will need an 'And' and to add all the possibilities

if (countAnd > 0)

{

queryToReturn += "<And>";

for (int i = 0; i < countAnd - 2; i++)

{

queryToReturn += string.Format(@"<And><Neq><FieldRef Name='BelongsTo'/><Value Type='User'>{0}</Value></Neq>", listGroupName[i].ToString());

}

queryToReturn += string.Format(@"<Neq><FieldRef Name='BelongsTo'/><Value Type='User'>{0}</Value></Neq>", listGroupName[countAnd - 1].ToString());

for (int i = 0; i < countAnd  - 2; i++)

{

queryToReturn += @"</And>";

}

}

queryToReturn += @"<Neq><FieldRef Name='BelongsTo'/><Value Type='Integer'><UserID/></Value></Neq>";

//Close the And tag if needed

if (countAnd > 0)

{

queryToReturn += "</And>";

}

queryToReturn += "</Where>";

return queryToReturn;

}

Some explanations:

This function will do a check of all the groups in the collection, if the user is present in the group, a counter is incremented and the group name is saved in a list. Then the query is build based on those elements.

Hope this helps !

Christopher

Advertisements

CAML Query – Membership attribute


Hello everyone,

Today I’ll talk about a CAML query attribute, the “membership” attribute. This attribute allows especially to query the groups the current user!

If we look at the msdn documentation, we see that there are 5 different settings possible. However, this documentation is not very detailed. The purpose of this article is to detail the different possibilities.

We will see these possibilities through tests.

Here are the parameters used for the tests:

Site structure :

RootSite based on « TeamSite »

Susbsite based on « TeamSite »

Groups and users :

The tests were performed on the subsite. The site was created by inheriting from the parent security, then security has been broken and a new group “subtestgroup” was created.

  • Groups :
    • Test Owners
    • Test Members
    • Subtestgroup
  • Users :
    •  User 1 :
      • Current user
      • Member of “Test Owners” and “subtestgroup”
  • User 2
    • rights granted directly on the subtestsite.

Query :

<Membership Type=””{0}””>

<FieldRef Name=””AssignedTo””/>

</Membership>

The 0 is replaced by different parameters.

List and tasks targeted:

Standard task list. The following tasks were created.

  • T1 assigned to user1
  • T2 assigned to test owners
  • T3 assigned to subtestgroup
  • T4 assigned to user2
  • T5 assigned to test members

Here the parameters, up to the test!

1) SPWeb.AllUsers :

This value is used to identify the tasks assigned to users (not groups)

Test result: T1 and T4

2 ) SPGroup :

Using this parameter, you must add an ID parameter with the ID of the desired group. This parameter will return the list of tasks assigned to members of this group.

The test will be performed using the ID of groups “members tests “(ID 7, empty group) and “test owners’”(ID 5, containing User 1)

If we change the query used by this one

<Membership Type=””SPGroup”” ID=”7”>

<FieldRef Name=””AssignedTo””/>

</Membership>

Test result : no tasks

Test with ID 5

Test result : T1

3 ) SPWeb.Groups :

The tasks assigned to the group site collection appear but not the tasks assigned to specific groups site

Test result : T2 and T5

4 ) CurrentUserGroups :

The tasks assigned to the current user groups will appear but not tasks assigned directly to the user.

Test result : T2 et T3

5 ) SPWeb.Users :

Tasks assigned to users who have received rights to the site directly (not through a group).

Test result : T4

Voilà !

Hope this helps!

Christopher.

Query only approuved files

Hello everyone,

Today I will talk about a little trick that a friend gave me recently.

Having used to work with SPQuery, I was already familiar with the attribute needed to query all the file within list’s folder:

 SPQuery.ViewAttributes = "Scope='Recursive'"; 

But recently I learned of another attribute to filter only on documents approved! Indeed there is an attribute “ModerationType” !

 SPQuery.ViewAttributes = " ModerationType='HideUnapproved'"; 

And so if we want to make our request only on our approved documents, we will add the following attributes to our SPQuery.

 SPQuery.ViewAttributes = "Scope='Recursive' ModerationType='HideUnapproved'"; 

Hope this helps!

Christopher.

Tool for CAML Query


Hello everyone,

For those who do not know, there is a tool to generate your CAML queries.

This utility is the work of Karine Bosch and Andy Van Steenbergen

For SharePoint 2003 and 2007 we had the CAML Builder tool, you can it find here: http://www.u2u.be/res/tools/camlquerybuilder.aspx

Recently there was a new version of the CAML Builder targeting SharePoint 2010 and SharePoint Online: CAML Designer.

You can find all information here: http://karinebosch.wordpress.com/my-articles/caml-designer/

If you have to create CAML queries, do not hesitate one second!

Christopher.

Filter on current user with CAML Query.


Hi everybody,

Today I will give you a little trick with CAML Query: how to retrieve the current user.

Logic suggests that we write a query like this:

 <Eq> <FieldRef Name='AssignedTo' /> <value Type='User'> [Me]</ Value> </ Eq>  

But then, it does not work …
The proper way to retrieve the current user is to change our query like this.

 <Eq><FieldRef Name='AssignedTo' /><Value Type='Integer'><UserID Type='Integer'/></Value></Eq> 


And that way we get the current user.

Christopher.