1

I am trying to create a CAML query for querying list, here is my problem. There are a list Employees and it has a LookupMulti column for Hobbies. I want to get all Employees who have selected Reading as their secondary Hobby, means the Employee should have more than one Hobbies selected and one of them should be Reading.

Please check following code for what I have tried so far

        SPList list = web.Lists[Constants.List_Employees];

            SPQuery query1 = new SPQuery();

            //Create caml query
            var queryBuilder = new StringBuilder("<View><ViewFields>");

            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ID);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_EmployeeStatus);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_InsuranceCompanyName);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_Title);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABSEmployeeRegion);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABSGender);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABS1ParentCaregiverEmail);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_ABS2ParentCaregiverEmail);
            queryBuilder.AppendFormat("<FieldRef Name='{0}' />", Constants.Employee_Field_MedicaidID);
            queryBuilder.AppendLine("</ViewFields>");

            queryBuilder.AppendLine("<Where>");
            queryBuilder.AppendLine("<And>");

            queryBuilder.AppendLine("<Eq>");
            queryBuilder.AppendFormat("<FieldRef Name='{0}'></FieldRef>", Constants.Employee_Field_EmployeeStatus);
            queryBuilder.AppendFormat("<Value Type='Choice'>{0}</Value>", Constants.Employee_Status_Active);
            queryBuilder.AppendLine("<Eq>");

            //This is where I am struggling to write CAML query
            queryBuilder.AppendLine("<Contains>");
            queryBuilder.AppendFormat("<FieldRef Name='{0}'></FieldRef>", Constants.Employee_Field_Hobbies);
            queryBuilder.AppendFormat("<Value Type='Choice'>{0}</Value>", Constants.Employee_Hobby_Reading);
            queryBuilder.AppendLine("<Contains>");

            queryBuilder.AppendLine("</And>");
            queryBuilder.AppendLine("</Where>");
            queryBuilder.Append("</Query></View>");

            query1.ViewXml = queryBuilder.ToString();

            return list.GetItems(query1);
Improve this question

1 Answer 1

Reset to default
0

Try this:

string query = "<Where><Eq><FieldRef Name=\"MultiLookupFieldName\" /><Value Type=\"LookupMulti\">" + DisplayValue + "</Value></Eq></Where>";
SPQuery spQuery = new SPQuery();
spQuery.Query = query;
SPListItemCollection items = list.GetItems(sp

Query);

Improve this answer
3
  • Please check updated question with code. This code will get me all Employees with Hobby as Reading even if there is only single Hobby selected.
    – Nikhil Chavan
    Commented Feb 8, 2016 at 6:31
  • You give a try this CAML query, it should work fine. Contains is not preferred.
    – Ovais Khatri
    Commented Feb 8, 2016 at 6:36
  • Yes, I understand, I will change Contains to Eq. But I would like to check number of Hobbies too, in CAML query, if number of Hobbies are greater than one i.e. there must be atleast two Hobbies selected, I don't want to get single Hobby selected Employees. Please do let me know if its even possible to check this condition with CAML query.
    – Nikhil Chavan
    Commented Feb 8, 2016 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.