CODEWITHSUNDEEP
sharepointcaml
Luis Valencia
Luis Valencia

Reputation: 33988

How to write CAML query with nested ANDs or ORs

Before you start suggesting U2U CAML Editor or CAML Designer 2013, I already tried both, none of them allow me in an intuitevely way to group ANDS/ORS statements to do the proper query.

I have a list if Bill cycles with some fields, In SQL This would be extremely easy:

WHERE (Content Type='Bill Cycle')
  AND (Status != 'Completed' ANDStatus!='Terminated' and Status!=NULL)
  AND (JobAdvisor:xyz OR JobPartner:xyz or JobManager:xyz or Reviewer:xyz or AnotherUserField:xyz)

My code is more or less like this: but I am missing to group the 2 big statements here

<And>
    <And>
        <And>
            <Neq>
                <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
                <Value Type='Text'>Completed</Value>
            </Neq>
            <Neq>
                <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
                <Value Type='Text'>Terminated</Value>
            </Neq>
        </And>
        <IsNull>
            <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
        </IsNull>
    </And>  
    <Eq>
        <FieldRef Name='ContentType' />
        <Value Type='Computed'>Bill Cycle</Value>
    </Eq>
</And>

<Or>
    <Or>
        <Or>
            <Or>
                <Contains>
                    <FieldRef Name='Billing_x0020_Advisor_x0020_Reviewers' />
                    <Value Type='User'>a</Value>
                </Contains>
                <Contains>
                    <FieldRef Name='Final_x0020_P_x002F_D_x0020_Approver' />
                    <Value Type='User'>a</Value>
                </Contains>
            </Or>
            <Contains>
                <FieldRef Name='BillManager' />
                <Value Type='User'>a</Value>
            </Contains>
        </Or>
    </Or>
    <Contains>
            <FieldRef Name='Bill_x0020_Preparer' />
            <Value Type='User'>a</Value>
    </Contains>
</Or>

Upvotes: 0

Views: 612

Answers (1)

Mike Smith - MCT
Mike Smith - MCT

Reputation: 1221

Just following your SQL example... the outer and's three things: the Bill Cycle test, the and'ed status test, and the OR block. 

<And>

  <Eq>
    <FieldRef Name='ContentType' />
    <Value Type='Computed'>Bill Cycle</Value>
  </Eq>

  <And>
    <Neq>
      <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
      <Value Type='Text'>Completed</Value>
    </Neq>
    <Neq>
      <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
      <Value Type='Text'>Terminated</Value>
    </Neq>
    <IsNull>
      <FieldRef Name='Bill_x0020_Preparation_x0020_Status' />
    </IsNull>
  </And>

  <Or>
    <Or>
      <Or>
        <Or>
          <Contains>
            <FieldRef Name='Billing_x0020_Advisor_x0020_Reviewers' />
            <Value Type='User'>a</Value>
          </Contains>
          <Contains>
            <FieldRef Name='Final_x0020_P_x002F_D_x0020_Approver' />
            <Value Type='User'>a</Value>
          </Contains>
        </Or>
        <Contains>
          <FieldRef Name='BillManager' />
          <Value Type='User'>a</Value>
        </Contains>
      </Or>
    </Or>
    <Contains>
      <FieldRef Name='Bill_x0020_Preparer' />
      <Value Type='User'>a</Value>
    </Contains>
  </Or>

</And>

Upvotes: 1

Related Questions