Need help structuring a CAML query

[HumblePie]

Trying to structure a CAML query with nested AND OR statements to retrieve data sets from a sharepoint list.

I'll give you an example of what I'm trying to retrieve back.

I have a list with several fields. This list contains incidents that have occurred at various locations. I would rather use a CAML query to retrieve the exact data set I need back than to use code to refine what I'm looking for. For example:

I want to retrieve data points back over a given date range for two different types of incidents at 2 or possibly 3 different locations.

Here is a sample list with filler data to use.

My List: (field names below)
DateOfIncident
TypeOfIncident
Description
Location


There are dozens of different types and dozens of locations. Say for example I want to return all incidents that are of type "theft" and of type "vandalism" for locations "A, C, and G."

I tried doing it this way.

<Query>
	<Where>
		<Or>
			<Or>
				<And>
					<Or>
						<Eq>
							<FieldRef Name='Type'/>
							<Value Type='Text'>Theft</Value>
						</Eq>
						<Eq>
							<FieldRef Name='Type'/>
							<Value Type='Text'>Vandalism</Value>
						</Eq>
					</Or>
					<Eq>
						<FieldRef Name='Location'/>
						<Value Type='Text'>A</Value>
					</Eq>
				</And>
				<Eq>
					<FieldRef Name='Location'/>
					<Value Type='Text'>C</Value>
				</Eq>
			</Or>
			<Eq>
				<FieldRef Name='Location'/>
				<Value Type='Text'>G</Value>
			</Eq>
		</Or>
	</Where>
</Query>

I've tried changing out the AND and the OR statements around but can't quite figure it out. In the example above, the AND and OR statements are just there as I've been playing with them in different nested layouts to produce the correct data set I'm trying to retrieve but haven't figured it out yet. Any help out there?

 

[Gunslinger08]

You need to restructure your query. I'll give you some suggestions tomorrow when I'm not replying from my phone. When working with dynamic CAML queries like this, I find it easiest to build a query generator. I'll see if I can find you a free one, or at least explain how to build one.

 

[Ken g6]

Looks like in this case it should be where (type="theft" OR type="vandalism") AND (location='A' OR location='C' OR location='G') I'd say get the Boolean logic right first, and then convert it to CAML.

Having never used CAML, I'd guess:

<Query>
  <Where>
    <And>
      <Or>
        <Eq>
          <FieldRef Name='Type'/>
          <Value Type='Text'>Theft</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Type'/>
          <Value Type='Text'>Vandalism</Value>
        </Eq>
      </Or>
      <Or>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>A</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>C</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>G</Value>
        </Eq>
      </Or>
    </And>
  </Where>
</Query>

 

[Gunslinger08]

Looks like in this case it should be where (type="theft" OR type="vandalism") AND (location='A' OR location='C' OR location='G') I'd say get the Boolean logic right first, and then convert it to CAML.

Having never used CAML, I'd guess:

<Query>
  <Where>
    <And>
      <Or>
        <Eq>
          <FieldRef Name='Type'/>
          <Value Type='Text'>Theft</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Type'/>
          <Value Type='Text'>Vandalism</Value>
        </Eq>
      </Or>
      <Or>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>A</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>C</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>G</Value>
        </Eq>
      </Or>
    </And>
  </Where>
</Query>

CAML is actually a little goofy - you can only have 2 subelements inside of any boolean operator, so you have to nest them like this:

<Where>
  <And>
    <Or>
      <Eq>
        <FieldRef Name='Type'/>
        <Value Type='Text'>Theft</Value>
      </Eq>
      <Eq>
        <FieldRef Name='Type'/>
        <Value Type='Text'>Vandalism</Value>
      </Eq>
    </Or>
    <Or>
      <Eq>
        <FieldRef Name='Location'/>
        <Value Type='Text'>A</Value>
      </Eq>
      <Or>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>C</Value>
        </Eq>
        <Eq>
          <FieldRef Name='Location'/>
          <Value Type='Text'>G</Value>
        </Eq>
      </Or>
    </Or>
  </And>
</Where>

Notice that each OR/AND only contains 2 subelements (which include either a condition or another OR/AND). This can be pretty tricky when you're building dynamic queries (ex. a user clicks checkboxes for each "type" and "location" that they're interested in). We've got a custom CAML query builder in house for this. There are a couple of free ones, but I haven't used any of them very much. You might check out CAML.NET: http://camldotnet.codeplex.com/