Querying the “Person or Group” field using SPQuery (Update)

One of the weak points of Sharepoint 2007 is documentation. If you want to query a "Person or Group" field, the documentation on MSDN is a bit sparse.
Here is how to query a person:

    <fieldref name="PersonFieldName"/>
    <value type="User">User Display Name</value>

Replace PersonFieldName with the internal Name of the Field.
The two important things here: Value Type is "User", and the String to search for is the Display Name. So if you have a user called "John Doe" whose login is "mydomain\jdoe", you have to search for "John Doe", not for "mydomain\jdoe".
There is an obvious drawback to this: What if you have two users called John Doe?

Sharepoint People or Group SettingsI was quite disappointed that the Person field does not store the LoginName by default and can therefore not be queried for it.
There are two workarounds. The first one: Go to the properties of the field in the list, and at the bottom you can change "Show Field" to "Account".
This has the advantage that you can use the "domain\username" Syntax now. The drawback: The field looks bad in the list as it does now show the Login, not the Display Name with the neat presence status.

The second workaround: Use the undocumented LookupId property and search for the numeric ID:

    <fieldref name="PersonFieldName" LookupId="TRUE"/>
    <value type="int">UserID</value>

The UserID is not the "domain\username", but the numeric internal ID (SPUser.ID). As I use this in a workflow, I can easily access this through workflowProperties.OriginatorUser.ID, which is good enough for me.

Update: The second code example was incorrect, I fixed it now.

Comments (11)

MattiasFebruary 11th, 2008 at 17:18

This is exactly what i'm looking for but are you shure you've posted the correct code for the second workaround?

mstumFebruary 11th, 2008 at 19:53

Oops, thank you, the second example was indeed incorrect. I fixed it now.

Nicklas JohanssonJune 2nd, 2008 at 09:47

Excellent posts. I do have problem with a Query I've created containing a SPUser. I was just wondering if you ever has this error.

Query looks like this: "ADDPRO\\nijo_addpro"

Error message: Value does not fall within the expected range.

SPListItemCollection foundItems = list.GetItems(query);

if (foundItems.Count > 0) << This line gives the error message.
foreach (SPListItem item in foundItems)

mstumJune 2nd, 2008 at 19:49

"Value does not fall within the expected range" is one of those very generic error messages from Sharepoint that are not really helpful 🙁 In any case, it will most likely mean that your Query is not correct, most likely a mistake on the FieldRef.
Keeping in mind that you are only able to use domain\username syntax when the field has been set up properly and that you have to replace "PersonFieldName" with the actual internal name of the field, the query should look like this:

<fieldref name="PersonFieldName"/>
<value type="User">ADDPRO\nijo_addpro</value>

or when escaped:
SPQuery query = new SPQuery();
query.Query = "<where><eq><fieldref name=\"PersonFieldName\"/><value type=\"User\">ADDPRO\\nijo_addpro</value></eq></where>";

Oskar AustegardJuly 25th, 2008 at 19:48

Not that I disagree with the rant against poor documentation (I do so myself here: http://mo.notono.us/2008/01/moss-rant-pathetic-documentation.html) but how about helping out your fellow CAML sufferer with some Community Content on MSDN? Add your finding to (spquery) http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx and (fieldref) http://msdn.microsoft.com/en-us/library/ms442728.aspx, and (where) http://msdn.microsoft.com/en-us/library/ms414805.aspx

mstumJuly 25th, 2008 at 20:35

That's actually a really good point, especially because the community content helped me in some situations as well. I did not think about that yet because usually when I run into one of those problems, I'm busy resolving them and moving on with my work. I think I'll add some stuff to it later.

MatAugust 20th, 2008 at 23:08

Can you tell me how to query for a SharePoint group instead of a user? I tried the LookupId and what was said under MSDN, but nothing worked.

MatAugust 22nd, 2008 at 23:27

Take it back.. it worked

jagkonMarch 6th, 2010 at 23:29

Will this query work for Group also ?
Can you please show some pointers

Adam LarnerOctober 11th, 2012 at 09:32

You could use a workflow to set another column in the list to the user's ID. It's not ideal, but this is one way to get around the missing UserId in the user object.

IvanJune 11th, 2013 at 08:41

Is it possible to query a Person or Group field that has multiple values? I want my query to return all lists that contain the value John Doe in the participants column.

SPQuery query = new SPQuery();
query.Query = "John Doe";
SPList list = CurrentWeb.Lists.TryGetList("GroupChallenge");
SPListItemCollection listItems = list.GetItems(query);