CAML query for searching users with identical display names

SharePoint - Development and Programming

Martrs 


Hi!

Imagine situation: there are several employees with name John Smith in Your company. Each of them has his own user name ('mydomain\smith1', 'mydomain\smith2'), but display name in WSS 3.0 for all of them is the same - John Smith.

In custom web part I need to find list items with user 'mydomain\smith1' in column 'Empoyee' using SPQuery object. I create following CAML query:
---------------
Dim MyQuery As New SPQuery
MyQuery.Query = "<Where>" + _
              "<Contains>" + _
                "<FieldRef Name=""Employee"" />" + _
                 "<Value Type=""User"">John Smith</Value>" + _
               "</Contains>" + _
            "</Where>"
---------------

I pass display name of user in query (John Smith), so I also get list items with user 'mydomain\smith2' in column 'Employee' because this user has the same display name. But I need to get items only for user 'mydomain\smith1'.

Unfortunately I haven't found an easy way how to return items only for the user 'mydomain\smith1'. Passing user name or string in format 'UserID;#DisplayName' (like it is written in column) does not work.

Is there any way I can get items only for 'mydomain\smith1' 

Similar situation is also with Lookup columns - different items in lookup list can share the same value in column that is specified as a display column for a lookup column.
Top

Re: CAML query for searching users with identical display names

smc750


Is there something preventing you from using "mydomain\smith1" as the value to query for





Top

Re: CAML query for searching users with identical display names

Martrs

Nothing is returned if using user name ('mydomain\smith1') in query. Nothing is returned also if I pass SharePoint ID of user. Query works only with display names.

It is possible to pass user ID of current user to CAML query using '<UserID />'.

-----------------------

Dim MyQuery As New SPQuery
MyQuery.Query = "<Where>" + _
"<Contains>" + _
"<FieldRef Name=""Employee"" />" + _
"<Value Type=""User"">" + _

"<UserID />" + _

"</Value>" + _
"</Contains>" + _
"</Where>"

-----------------------

This way correct results are returned. But unfortunately this works only for currently logged in user.



Top

Re: CAML query for searching users with identical display names

smc750

Apparently, the "Show Field" setting for a user column in SharePoint will affect your results when using caml queries. If you set the "Show Field" to "Name" instead of "User Name" you will be able to query using "domain\username" format. The "User Name" format will return duplicates because it is just a display name. Setting the "Show Field" to use something that is unique will resolve your caml query problem.





Top

Re: CAML query for searching users with identical display names

Martrs

Your solution is working, thanks! But it does not suite for me because I need to get correct results no matter what is set in 'Show Field' setting.

But yesterday evening I found a solution that works for me. Attribute LookupId="TRUE" must be added to <FieldRef>. Using this attribute I can search by user ID. Query should look like this:
-------------------------
Dim MyQuery As New SPQuery
MyQuery.Query = "<Where>" + _
"<Eq>" + _
"<FieldRef Name=""Employee"" LookupId=""TRUE"" />" + _
"<Value Type=""User"">UserID</Value>" + _
"</Eq>" + _
"</Where>"
-------------------------

This applies also to Lookup fields.


Top
  • The team •