SharePoint's CAML query the "Created By" field with username

Question

Hey, I have a form for administrators where they insert a user name ("domain\name") and the code gets and sets some information out of it.

It's a huge project and some of the lists contain the username as a string ("domain\name"), but some lists only count on the "Created By" column, which is auto-created.

I want to know what's the fastest way to query these lists using the username string. I tried to use the same query as the one I use for the first kind of lists and it obviously didn't work -

<Where><Eq><FieldRef Name='UserName'/><Value Type='Text'>domain\\username</Value></Eq></Where>

Thank you.

Lighterman

Answer 1

There are two different "Created By" fields, one for all items and one specifically for document libraries ("Document Created By"). The internal name for the "Created By" field is "Author", so <FieldRef Name='Author'/> would be the best start. The second field, which is the author of the actual file in a document library, has an internal name of "Created_x0020_By". Judging from your scenario I have a feeling you'll only need the former, but the latter is good to know anyway, because the data stored in them is different.

"Created By" is a user field, so the string version of its data is ID;#Full Name. Meanwhile, "Document Created By" is a text field for a single line of text, and its data is stored as the actual username (with domain, if applicable). So your above query would work in the case of a document library and searching on <FieldRef Name='Created_x0020_By'/>. If you wanted to search on the "Created By" field, however, you'll have to be a bit trickier, but you should be able to accomplish it by referencing the user's SharePoint ID. The following is an example of a different User field query that I am using, for comparison.

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

This specifically filtered out the current user. To use it for your purposes, replace 'AssignedTo' with 'Author' and <UserID Type='Integer' /> with the ID of the user in question.

Chatoyant

Thanks. I'm having the first scenario, that is trying to query a simple list by the Author column, having only a user name. I managed to create an SPUser object and tried your query with "Author" and user.ID, but I get an exception saying "Conversion failed when converting to nvarchar value 'SomeFirstname SomeLastname' to data type int.", where "SomeFirstname SomeLastname" is a name from the list that isn't the username. Would appreciate your help :) – Lighterman

nvm, fixed it. When querying the "Author" column by ID, you need to add a "LookupId='True'" attribute to the "FieldRef" tag. Working, thanks a lot! – Lighterman

Answer 2

I would recommend the following:

1. Get the SharePoint-User by invoking the EnsureUser method, i.e.:

    SPUser myUser = SPContext.Current.Web.EnsureUser(@"DOMAIN\USERNAME");
   

2. Query the list by using the ID of the SharePoint-User:

    SPQuery spQuery = new SPQuery();
    StringBuilder queryString = new StringBuilder(String.Empty);
    queryString.Append(@"<Where>");
    queryString.Append(@"   <Eq>");
    queryString.Append(@"       <FieldRef ID=""{1df5e554-ec7e-46a6-901d-d85a3881cb18}"" LookupId=""True"" />"); //Author Field
    queryString.Append(@"       <Value Type=""Lookup"">" + myUser.ID + @"</Value>");
    queryString.Append(@"   </Eq>");
    queryString.Append(@"</Where>");
    spQuery.Query = queryString.ToString();
   

3. Fire the query against the list

   myList.GetItems(spQuery);
   

Gazpacho