This project has moved and is read-only. For the latest updates, please go here.

Question about multiple filter with AND and OR

May 28, 2013 at 7:45 PM
Edited May 28, 2013 at 7:48 PM
Hello, I'm doing a visual web part that contains HTML filters.

I need to make a query that filters the parameters passed by the user.

has the form:
3 textbox;
5 listBox (multiple selections);

XML code:
_<Where>
   <Or>
     <Or>
       <Eq>
         <FieldRef Name="Regi_x00e3_o_x0020_Impactada" />
         <value Type="Lookup"> Copacabana </ Value>
       </ Eq>
       <Eq>
         <FieldRef Name="Regi_x00e3_o_x0020_Impactada" />
         <value Type="Lookup"> Maracanã </ Value>
       </ Eq>
     </ Or>
     <Eq>
       <FieldRef Name="ID" />
       <value Type="text"> 1 </ Value>
     </ Eq>
   </ Or>
</ Where>_

__But I can not build the correct XML ...
I can not make two AND and OR.
Is there an easier way to solve this problem?__
May 28, 2013 at 8:44 PM
hi jorweb,
from your example it is not quite clear what CAML you need to get in result, but I think this thread can be useful for you: Mix AND and OR with expressions. Shortly, there is ExpressionsHelper class in Camlex, using which you may mix expressions which contain both AND and OR. For example, you said that you have 5 listboxes and 3 textboxes. Suppose that we need to create expression: Title field contains values in all 3 textboxes AND is equal to some value in at least 1 list boxes, i.e.
x["Title"].Contains(txt1) && x["Title"].Contains(txt2) && x["Title"].Contains(txt3) && (x["Title"] == lst1 || x["Title"] == lst2 || x["Title"] == lst3 || x["Title"] == lst4 || x["Title"] == lst5)
This query contains both AND and OR. Here how it can be created using ExpressionsHelper:
string listbox1 = "foo1", listbox2 = "foo2", listbox3 = "foo3", listbox4 = "foo4", listbox5 = "foo5";
string textbox1 = "bar1", textbox2 = "bar2", textbox3 = "bar3";
var or = new List<Expression<Func<SPListItem, bool>>>();
or.Add(x => (string)x["Title"] == listbox1);
or.Add(x => (string)x["Title"] == listbox2);
or.Add(x => (string)x["Title"] == listbox3);
or.Add(x => (string)x["Title"] == listbox4);
or.Add(x => (string)x["Title"] == listbox5);

var and = new List<Expression<Func<SPListItem, bool>>>();
and.Add(x => ((string)x["Title"]).Contains(textbox1));
and.Add(x => ((string)x["Title"]).Contains(textbox2));
and.Add(x => ((string)x["Title"]).Contains(textbox3));
and.Add(ExpressionsHelper.CombineOr(or));

string caml = CamlexNET.Camlex.Query().WhereAll(and).ToString();
It will create the following CAML:
<Where>
  <And>
    <And>
      <And>
        <Contains>
          <FieldRef Name="Title" />
          <Value Type="Text">bar1</Value>
        </Contains>
        <Contains>
          <FieldRef Name="Title" />
          <Value Type="Text">bar2</Value>
        </Contains>
      </And>
      <Contains>
        <FieldRef Name="Title" />
        <Value Type="Text">bar3</Value>
      </Contains>
    </And>
    <Or>
      <Or>
        <Or>
          <Or>
            <Eq>
              <FieldRef Name="Title" />
              <Value Type="Text">foo1</Value>
            </Eq>
            <Eq>
              <FieldRef Name="Title" />
              <Value Type="Text">foo2</Value>
            </Eq>
          </Or>
          <Eq>
            <FieldRef Name="Title" />
            <Value Type="Text">foo3</Value>
          </Eq>
        </Or>
        <Eq>
          <FieldRef Name="Title" />
          <Value Type="Text">foo4</Value>
        </Eq>
      </Or>
      <Eq>
        <FieldRef Name="Title" />
        <Value Type="Text">foo5</Value>
      </Eq>
    </Or>
  </And>
</Where>