This project has moved. For the latest updates, please go here.

Query with username

Jul 12, 2011 at 1:21 PM

What is the best way, if any, to query a list item by user account with Camlex?

I have a Sharepoint list that has a Column named Trainee, which is Name (with presence).

When you look at the list in IE, it shows LastName, FirstName, and it is underlined. I know the user account (domain\username) is associated with that field.

My query is set up like this Camlex.Query().Where(x => x["Trainee"] == (DataTypes.User)domain\username).ToString();

But this doesn't return anything.

How can I query based on the user account and leaving the column name with presence?

If I change the field to account, it works, but then I lose the LastName, FirstName.

 

Thanks.

Coordinator
Jul 12, 2011 at 3:29 PM

hi chorpeac,

as I understand "Trainee" column has "User" type (there is no such type as Name with presence so I didn't fully get the phrase "Column named Trainee, which is Name (with presence)"). If you want to query using login name (domain\username) you need to set "Show field" setting for "Trainee" column to "Name" in column's settings in your list. If it has Show field = User name, you need to use Display name format, i.e. "FirstName LastName" for your CAML. Check the following forum thread: CAML query for searching users with identical display names.

Jul 12, 2011 at 5:12 PM

Hello,

In my testing here is what I did and my results.

Using this CAML as a default:

Camlex.Query().Where(x => x["Trainee"] == (DataTypes.User)domain\username).ToString();

In my SharePoint list, the column Trainee is configured as a single person/group type.

When I set the "Show field" as Name I see LastName, FirstName in the list display, but my Caml returns 0 results

When I set the "Show field" as Account I see domain\Username in the list display, and my Caml returns 1 results, but I don't have the LastName, FirstName display that is more user friendly.

When I set the "Show field" as Name (with presence) I see LastName, FirstName in the list display, but my Caml returns 0 results

I am sure this is probably not a problem with Camlex.Net, but I am just trying to figure out how to query with login name and display with the Last, First. I don't want to have to create a duplicate column, as that would be a hack solution.

Jul 12, 2011 at 5:31 PM

I've done some more testing, and I have come up with something that works for all the selections of "Show field" on the Trainee column.

When I want to get "My" items, I do this...

string caml = Camlex.Query().Where(x => (int)x["Trainee"] == -1).ToString();

caml = caml.replace ("-1", "<UserID/>");

 

I figured, let me do the same thing, but with the login information, so I did this...

string caml = Camlex.Query().Where(x => (int)x["Trainee"] == -1).ToString();

caml = caml.replace ("-1", "<UserID>" + loginInfo + "</UserID>");

And it works.

Maybe there is a better way to do this within CamlEx?

Coordinator
Jul 12, 2011 at 6:33 PM

yes - the point was that "Show field" option affects CAML query result. In your case it was not Name as I wrote, but Account (don't have Sharepoint now to check the exact names). You can also try to query with Show field = Name, and use

<Value Type="Text">LastName, FirstName</Value>

or

<Value Type="Text">FirstName LastName</Value>

(try both). Note that instead of User simple Text type is used.

Currently UserID syntax is not supported in Camlex. I think it can be added in next release. I will add it on feature request page.

Jul 12, 2011 at 6:44 PM

Thanks for your input, and that feature would be appropriate in Camlex. Hope it makes it! :)

Aug 5, 2011 at 10:16 AM
Edited Aug 5, 2011 at 10:19 AM

Hi!

Recently I faced with the same issue, and what I found:

Instead of query by "domain\username" you can query by UserId, that is more preferably. Camlex code look like follows:

var query = Camlex.Query().Where(el => el[FieldNames.GroupTrainer] == ((DataTypes.LookupId)trainerId.ToString()));

And generated query:

<Where>
    <Eq>
      <FieldRef Name="Group_x0020_Trainer" LookupId="True" />
      <Value Type="Lookup">2959</Value>
    </Eq>
</Where>

As you may know, SPFieldUser is inherits from SPFieldLookup, and this query will work, you can test it using caml query builder. But, as mentioned in discussion above, may be more correct query is:

<Where>
    <Eq>
      <FieldRef Name="Group_x0020_Trainer" LookupId="True" />
      <Value Type="User">2959</Value>
    </Eq>
</Where>

and code for this query must look like this one:

 var query = Camlex.Query().Where(el => el[FieldNames.GroupTrainer] == ((DataTypes.UserId)trainerId.ToString()));

may be Alex include this feature in next release, but now my above example works perfectly.

Alex, thanks for great tool.

Aug 5, 2011 at 5:17 PM

sKai,

Thanks for the contribution. It looks like a good way to work it. However, I don't know if your claim of most perferable is 100%. In fact, I think both methods would be preferable. SharepointUserId and the domain/userid are both actually equally accessible. I develop apps that use SP web services, not native SP applications. So for me, I know the domain/userId. I think it just depends on where you are using the CAML, but both ideas would be beneficial in the library.

Coordinator
Aug 5, 2011 at 7:06 PM

hello guys,

chorpeac, is it true that loginInfo in your last example contains login name in the form "domain\username"?

sKai, did you test the following CAML on the real data?

<Where>
    <Eq>
      <FieldRef Name="Group_x0020_Trainer" LookupId="True" />
      <Value Type="User">2959</Value>
    </Eq>
</Where>

logically it should work, but we try to test queries on real data before to add it into Camlex.

BTW, I'm not the only person who work over it, so I share your thanks to Vladimir as well :)

Aug 5, 2011 at 8:55 PM

sadomovalex, I am about 99% positive that the query is domain\username. I would be able to certify 100% on Monday when i get back to work.

Coordinator
Aug 5, 2011 at 9:57 PM

the only thing which worries me is that such usage is not documented: http://msdn.microsoft.com/en-us/library/ff625789.aspx (i.e. that you can use value inside UserID tag). Do you have any links which describe such behavior? I'm adding these features currently into Camlex and will definitely add UserID without value. However I could not get it work for UserID with value - it always returns empty result on my environment.

Coordinator
Aug 5, 2011 at 11:08 PM

ok, I uploaded new version 2.4.0.0. Will write about changes in the blog. Here is the list of new features:

1. DataTypes.UserID support is added:

string caml = Camlex.Query().Where(x => x["Author"] == (DataTypes.UserId)"123").ToString();

will produce the following CAML:

<Where>
  <Eq>
    <FieldRef Name="Author" LookupId="True" />
    <Value Type="User">123</Value>
  </Eq>
</Where>

2. Querying by current user's id is added:

string caml = Camlex.Query().Where(x => x["Author"] == (DataTypes.Integer)Camlex.UserID).ToString();

will produce the following CAML:

<Where>
  <Eq>
    <FieldRef Name="Author" />
    <Value Type="Integer">
      <UserID />
    </Value>
  </Eq>
</Where>

Aug 6, 2011 at 1:29 AM

Thanks for including the <UserId /> feature. I will definitely be using that. I'll also research this other one a little with the domain\username. I'll send a question off to Microsoft as well. Maybe one of their techs can shed some more light on this. I think the resolution I had was related to the selection of the "Show Field". Those options made my testing vary. In some cases the query worked, where other cases it didn't return anything.

Aug 8, 2011 at 7:35 AM
Edited Aug 8, 2011 at 7:37 AM

Thanks for quick update.

<q>sKai, did you test the following CAML on the real data?</q>

Yes, I've tested both

<Where>
    <Eq>
      <FieldRef Name="Group_x0020_Trainer" LookupId="True" />
      <Value Type="Lookup">2959</Value>
    </Eq>
</Where>

and

<Where>
    <Eq>
      <FieldRef Name="Group_x0020_Trainer" LookupId="True" />
      <Value Type="User">2959</Value>
    </Eq>
</Where>

 

queries using Caml query builder, on my local environment all works fine. I will use DataTypes.UserID and tomorrow we can test it on test server, so I will report results.