Is this kind of logic possible using CAML Queries?

May 7, 2015 at 8:54 PM
Edited May 7, 2015 at 10:11 PM
I am fairly experienced with CAML Queries, but this one has me stuck. I need some help in structuring the query's logic. What I need is to return every record that that contains two words across two columns.

Example (return these):
Column1: word1, Column2: word2. //Return this record
Column1: word2, Column2: word1. //Return this record
Column1: word2 word1, Column2: (empty). //Return this record
Column1: (empty), Column2: word2 word1. //Return this record

Example (do not return these):
Column1: (empty), Column2: word1. // Do not return this record
Column1: word1, Column2: (empty). // Do not return this record
Column1: (empty), Column2: word2. // Do not return this record
Column1: word2, Column2: (empty). // Do not return this record
Column1: (empty), Column2: (empty). // Do not return this record

To put the logic in sudo-code:
if( ("word1" appears in 'Column1' OR "word1" appears in 'Column2') AND ("word2" appears in 'Column1' OR "word2" appears in 'Column2') )

I have tried different variations of queries, but they do not return desirable results. For example, the one below will always return a record if word1 appears in Column1 even if word2 does not appear anywhere.
<Or>
    <Contains>
        <FieldRef Name="Column1"/><Value Type="Text">word1</Value>
    </Contains>
    <And>
        <Contains>
            <FieldRef Name="Column2"/><Value Type="Text">word1</Value>
        </Contains>
        <Or>
            <Contains>
                <FieldRef Name="Column1"/><Value Type="Text">word2</Value>
            </Contains>
            <Contains>
                <FieldRef Name="Column2"/><Value Type="Text">word2</Value>
            </Contains>
        </Or>
    </And>
</Or>
P.S. I am in SharePoint 2007
May 8, 2015 at 12:13 PM
This should be doable! Have you tried this?

<And>
<Or>
    <Contains>
        <FieldRef Name="Column1"/><Value Type="Text">word1</Value>
    </Contains>
    <Contains>
        <FieldRef Name="Column2"/><Value Type="Text">word1</Value>
    </Contains>
</Or>
<Or>
    <Contains>
        <FieldRef Name="Column1"/><Value Type="Text">word2</Value>
    </Contains>
    <Contains>
        <FieldRef Name="Column2"/><Value Type="Text">word2</Value>
    </Contains>
</Or>
</And>

This should be what you need. I had a huge query for polling a calendar list so these can be confusing!

Hope it works for you!!

Dan
(spevilgenius)
May 8, 2015 at 2:29 PM
Thank you so much, I knew there was a way I was overlooking! It works perfectly :)