Using SPQuery to filter lists by Domain User Account and SPUser ID

If you want to filter a list based on the value in the look up field that is of type ‘Person or Group’, We have to consider the following options:

  • Filter by the User Name.
  • Filter by SPUser ID Property
  • Filter by Domain User account

Filter by the User Name:

By default the settings for the ‘Person or Group’ column will have the following settings.

assignedto11

The Show Field will have the “Name with Presence” selected.

When we run a SPQuery search on the list using the following code we will be able to filter the list based on the fullname of the user.

   1: using (SPSite oSite = new SPSite("http://vslearnwss:801"))
   2:            {
   3:                using (SPWeb oWeb = oSite.OpenWeb())
   4:                {
   5:                    oList = oWeb.Lists["Project Tasks"];
   6:                    SPQuery query = new SPQuery();
   7:                    query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>Karthikeyan K</Value></Eq></Where>";
   8:                    SPListItemCollection items = oList.GetItems(query);
   9: 
  10:                }
  11:            }

Filtering by SPUser ID :

If you want to filter the list based on the SPUser ID then follow the steps below.

  • Add an additional attribute ‘LookupId’ for the queried field in your CAML query
<FieldRef Name='AssignedTo' LookupId='TRUE'/>
 

The updated code is as follows.

   1: using (SPSite oSite = new SPSite("http://vslearnwss:801"))
   2:            {
   3:                using (SPWeb oWeb = oSite.OpenWeb())
   4:                {
   5:                    oList = oWeb.Lists["Project Tasks"];
   6:                    SPQuery query = new SPQuery();
   7:                    query.Query = "<Where><Eq><FieldRef Name='AssignedTo' LookupId='TRUE'/><Value Type='User'>7</Value></Eq></Where>";
   8:                    SPListItemCollection items = oList.GetItems(query);
   9:                }
  10:            }

Filtering by Domain User Account :

If you want to filter the list based on the Domain User Account then follow the steps below.

  • Change the ‘Show Field’ Settings of the Person or Group lookup column to ‘Account’

assignedto21

 

 

 

 

 

 

 

  • Modify your code to include the domain account in the filter value.
using (SPSite oSite = new SPSite("http://vslearnwss:801"))
            {
                using (SPWeb oWeb = oSite.OpenWeb())
                {
                    oList = oWeb.Lists["Project Tasks"];
                    SPQuery query = new SPQuery();
                    query.Query = "<Where><Eq><FieldRef Name='AssignedTo' /><Value Type='User'>learnmoss\vinod</Value></Eq></Where>";
                    SPListItemCollection items = oList.GetItems(query);
                }
            }
Advertisements

4 Responses

  1. Excellent, nice one. I was baffled about how to get the Domain Account out of the user field – didn’t think of changing the column’s show field. You’ve saved me some head scratching!

  2. […] I was beginning to feel a bit frantic about not being able to do this sort of query when I found this very useful post by Karthikeyan Kasiviswanathan. The short of it – you have to set the column up to display the […]

  3. my situation is that I have document libray list (floder) , suppose i have field in the list with the user id or user email id, now I want to filter the list based on the the user logged in.
    how to filter the list based on user logged in? plz help

  4. It is truly a great and helpful piece of info.

    I am glad that you shared this helpful information with us.

    Please keep us informed like this. Thanks for sharing.

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: