cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Using AND OR in a Query List

Jump to solution

I am really confused on the AND OR filters in a query list.  As an example I want to filter a list if:

The student must be a Senior (her grade) AND must be female (her gender)

There sports preference can be soccer OR swimming OR rugby

I tried doing the below in the Query List but it essentially retuned the whole list; it did not filter.

Grade = Senior

AND

Gender = Female

OR

Sports

= Basketball

OR

Sports

=soccer

OR

Sports

=swimming

Labels: (1)
0 Kudos
Reply
6 Replies
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

So right away looking at this, I can see that it's probably reading it as "must be senior AND (female OR basketball OR soccer OR basketball OR swimming)"

I think it's reading everything after your first "AND" as part of the collection of ORs.

Perhaps it needs to be senior AND female AND (basketball OR swimming OR soccer).


I think that would work, but I am not 100%. I will see if I get a chance to try it out tomorrow.

Reply
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

conditions are evaluated from top to bottom.

so first GRADE and GENDER are ANDed and result is ORed with Basketball then ORed with soccer etc.

bit illogical approach and totally not maintainable, but there is not other way with clickable 'Query builder'

I would suggest to switch to 'CAML editor' and write your CAML query completely on your own. this gives you opportunity to group/prioritize single conditions as logically needed and it as well much more easy to maintain.

Reply
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

If you change the order of your query conditions, you can get it to work.  As said, the query reads from top to bottom, and add each criteria to the previous.  So the AND criteria should be last, and the OR criteria first.  

I created the mock table below as a list. The records in red are the results I want to return based on your criteria. 

TitleGenderGradeSports
Joe SmithMaleFreshmanBasketball
Jim JordanMaleSeniorSoccer
Julie SmithFemaleFreshmanSoccer
Tina TombsFemaleSeniorBasketball
Jane DoeFemaleSeniorHockey
Sally SwiftFemaleSeniorSwimming
Susie QueFemaleJuniorBasketball
Sam SmartFemaleSeniorSoccer

So I set up my Query List criteria as follows, with the 3 sports criteria first, and then the Gender and Grade last.  You can use the Run Now option to see the results, as well as the way it structured the CAML query. 

In this case it structured it as follows.  You can see that the outer condition is an AND, and the inner Sports criteria are wrapped in an OR.  So it will find everything that is Senior AND Female AND Sports is Basketball OR Soccer OR Swimming.  So it returns the records in red from the table above. 

With the way you currently have it structured, the CAML looks like this, with the outer condition being an OR condition.  So this query is looking for everything that where Sports is Swimming OR Soccer OR Basketball OR the Grade is Senior AND Gender is Female.   

So if you had a Male Swimmer, this Query would return that record.  

Hope that helps. 

Reply
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

Perfect.  Your explanation and example make total sense.  Thanks Marian and Rhia for the assist.

Reply
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

Great. And please don't forget to mark the question as answered.

0 Kudos
Reply
Workflow Hero

Re: Using AND OR in a Query List

Jump to solution

I did not know this! Thank you for sharing!

Reply