CAML query not fetching desired results

Question

I have a CAML query

<Query><Where><Or>"+
             "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
             "<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Text'> " + site.CurrentUser.Name + " </Value></Contains>"+
          "</Or></Where></Query>

This query is not fetching desired results. Especially the <Contains> part of the query. The <Eq> part works fine and it fetches results, but the contains part is not. Contains part is checking a note field in SharePoint list

Are you storing display name of users in OtherUsers column? — Gaurravs
– Gaurravs, Commented Sep 13, 2016 at 5:19
Then change Value Type of 'OtherUsers' to 'Note' and then try. — SP Developer
– SP Developer, Commented Sep 13, 2016 at 5:27

SP Developer · Accepted Answer · 2016-09-13 05:42:35Z

0

You are almost there. If 'OtherUsers' is multiline text field then just change Value Type of OtherUsers to 'Note'.

<Query><Where><Or>"+
"<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
"<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Note'> " + site.CurrentUser.Name + " </Value></Contains>"+
"</Or></Where></Query>

answered Sep 13, 2016 at 5:42

SP Developer

3,0851 gold badge19 silver badges45 bronze badges

Add a comment |

Gaurravs · Accepted Answer · 2016-09-13 05:38:02Z

"<Where><Or>"+
      "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
      "<Contains><FieldRef Name='OtherUsers'/><Value Type='Text'> " + site.CurrentUser.LoginName + " </Value></Contains>"+
"</Or></Where>";

You need to use site.CurrentUser.LoginName instead of site.CurrentUser.Name to get LoginName of user. If you want to use Display name of user, then site.CurrentUser.Name

Check the various options and use based on what you are storing:

site.CurrentUser.LoginName : LoginName of user
site.CurrentUser.Name : Display Name of user
site.CurrentUser.ID : Integer ID of user

Vignesh Subramanian · Accepted Answer · 2016-09-13 05:58:42Z

I found out the issue after some debugging and comments from @Gaurrav

The issue was the additional space that was there in the query!

Old Query

<Query><Where><Or>"+
             "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
             "<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Text'> " + site.CurrentUser.Name + " </Value></Contains>"+
          "</Or></Where></Query>

Corrected Query

<Query><Where><Or>"+
                 "<Eq><FieldRef Name='UserName' /><Value Type='User'>" + User.Name + "</Value></Eq>"+
                 "<Contains><FieldRef Name ='OtherUsers'/><Value Type = 'Text'>" + site.CurrentUser.Name + "</Value></Contains>"+
              "</Or></Where></Query>

Notice that the Value inside the <contains> query had additional spaces

Lessons learnt:

Don't add additional spaces for readability inside your CAML query.
Both Note and Text will work for multiple lines of text field

I would suggest not to keep additional space in <Value Type = 'Text'>" this as well . Refer my answer for same — Gaurravs
– Gaurravs, Commented Sep 13, 2016 at 6:10

Stack Exchange Network

CAML query not fetching desired results

3 Answers 3

Your Answer

Hot Network Questions

CAML query not fetching desired results

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions