2

I need some assistance with a complex CAML query. I can get parts of it working but cannot get all of it working together. What I need in laymans terms is this:

Where Country = US 

AND Status BEGINSWITH A OR Status BEGINSWITH B OR Status BEGINSWITH C 
OR Status BEGINSWITH D OR Status BEGINSWITH E OR Status BEGINSWITH F

AND Relationship NOT EQUALS to N/A OR IsComplete EQUALS Yes OR IsApproved EQUALS Yes

I have the statement laid out like this:


<Where>
<And>
        <Eq><FieldRef Name='ContractingCountry' /><Value Type='Text'>Canada</Value></Eq>
        <Or>
            <Or>
              <Or>
              <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>A</Value></BeginsWith>
              <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>B</Value></BeginsWith>
             </Or>
              <Or>
                  <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>C</Value></BeginsWith>
                  <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>D</Value></BeginsWith>
              </Or>
            </Or>
            <Or>
                <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>E</Value></BeginsWith>
                <BeginsWith><FieldRef Name='Status' /><Value Type='Text'>F</Value></BeginsWith>
            </Or>
        </Or>

</And>
<And>
        <Or>
              <Neq><FieldRef Name='Relationship' /><Value Type='Text'>N/A</Value></Neq>
            <Or>
                <Eq><FieldRef Name='IsComplete' /><Value Type='Text'>Yes</Value></Eq>
                <Eq><FieldRef Name='Approved' /><Value Type='Text'>Yes</Value></Eq>
            </Or>
        </Or>
</And>
</Where>

The Query runs but it seems to ignore the last part of the statement as it is pulling back things where the Relationship = N/A.

  • I think your last And should goes into the first And after the last Or – Marco Aug 11 '15 at 15:06
  • Using a tool to formulate queries like these would eliminate common errors. – Akhoy Aug 11 '15 at 15:14
  • no tools that I've tried can generate such a complicated query @uberz91. Suggestions? – Michael Colbs Aug 11 '15 at 17:59
  • @Colbs i find U2U query builder pretty useful for these cases. I'm sure it will be able to handle this. – Akhoy Aug 11 '15 at 18:37
  • 1
    You could also use spcamlqueryhelper.codeplex.com to test your queries or use U2U Query Builder. – ReTech Apr 11 '16 at 11:07
1

According to your condition following should be the CAML Query.
Condition

(Country == US) && (Status BeginsWith A || Status BeginsWith B || Status BeginsWith C || Status BeginsWith D || Status BeginsWith E || Status BeginsWith F) && (Relationship != NA || IsComplete == Yes || IsApproved == Yes)


CAML Query

<Where>
    <And>
        <And>
            <Eq><FieldRef Name='Country'/><Value Type='Text'>US</Value></Eq>
            <Or>
                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>A</Value></BeginsWith>
                <OR>
                    <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>B</Value></BeginsWith>
                    <OR>
                        <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>C</Value></BeginsWith>
                        <OR>
                            <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>D</Value></BeginsWith>
                            <Or>
                                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>E</Value></BeginsWith>
                                <BeginsWith><FieldRef Name='Status'/><Value Type='Text'>F</Value></BeginsWith>
                            </Or>
                        </Or>
                    </Or>
                </Or>
            </Or>
        </And>
        <Or>
            <Neq><FieldRef Name='Relationship'/><Value Type='Text'>N/A</Value></Neq>
            <Or>
                <Eq><FieldRef Name='IsComplete'/><Value Type='Text'>Yes</Value></Eq>
                <Eq><FieldRef Name='IsApproved'/><Value Type='Text'>Yes</Value></Eq>
            </Or>
        </Or>
    </And>
</Where>


Please check if it works for you.

0

You must first understand how it works and how to write.Then you can do it on your own.

How To Write Dynamic CAML :

While writing CAML query, you must understand the architecture how it works.

It is pretty simple. You just have to do logical AND/OR operators.

Any operator must have 2 conditions to combine and test.

SCENARIO 1 : This is 1 Operator use with 2 conditions

 <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
 </Or>

SCENARIO 2 : This is 2 Operators use with 2 conditions From scenario 1 we got operator now along with that we will add another operator.

<And>
    <Or>
      <Eq><FieldRef Name='Status' /><Value Type='Text'>(3) TestStatus3</Value></Eq>
       <Eq><FieldRef Name='Status' /><Value Type='Text'>(2) TestStatus2</Value></Eq>              
    </Or>
    <Eq><FieldRef Name='Status' /><Value Type='Text'>(1) TestStatus1</Value></Eq>                  
</And>

Here if you notice, <OR> is having two conditions inside it. and that makes 1 condition for outer <And>, so we added another condition block parallel to <OR> operator. Hence 2 conditions are available for <AND>.

Likewise you can build n number of nested conditions.

PS: If you have only 1 condition, it will not require any operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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