Blackbaud CRM™ Query Tips: Understanding AND/OR Operators


When building a query, there are several important features that can help you get the results that you are looking for. A prime example is the query operators that you can find on the Select filter and output fields tab. Query operators combine two separate sets of filtering criteria to narrow your query results.


The three types of query operators are:




  1. AND - use between criteria fields to indicate that records must meet both criteria to be included in the query results. And is the default operator that appears as you add filters to your query.

  2. OR - use between criteria fields to indicate that records can meet either criteria to be included in the query results. To change your operator from “and” to “or”, select the line of your query that you want to change and click the “or” button.



  1. Parenthesis - use to combine multiple criteria so that the query considers them together before considering other criteria. Use the parentheses buttons with a plus sign to add a parentheses, and the buttons with a minus sign to remove parentheses.



Here’s a few examples to illustrate how your use of query operators can change the results of your query.


Example 1: AND Operator



With the criteria listed above, which of the following constituents would appear in your results?

  1. A Board Member that has given $6,000

  2. A Board Member that has given $15,000

  3. A non-Board Member that has given $15,000


In this example, the operator “AND” is being used, so constituents must meet both criteria in order to be included in the results. Therefore, the correct answer is A and B. Constituent C would not appear in your results because they have not met the criteria of being a Board Member.


Example 2: OR Operator




Let’s try this example with the same criteria and the same constituents, but changing the operator to “OR”. Which of the following constituents would appear in your results?

  1. A Board Member that has given $6,000

  2. A Board Member that has given $15,000

  3. A non-Board Member that has given $15,000


This time the answer is A, B, and C. Using the “OR” operator makes the criteria less restrictive, so you can expect to have more records included in your results than the previous example.


Example 3: Parentheses


When you add in parentheses and combine them with both AND and OR operators, things start to get a bit more complex. Let’s use some examples to test this out.




In this example, we have an “or” operator between the first and second criteria, and an “and” operator between the second and third criteria. We also have parenthesis around the first two criteria, which mean they need to be considered together before any other criteria.


Which of these constituents would appear in your results?

  1. A Board Member that lives in Florida and has given $15,000

  2. A Board Member who lives in Ohio and has given $2,000


The answer is A. Constituent A meets all three of the criteria, so they are definitely going to be included in the results. Constituent B meets the criteria within the parentheses because they are a Board Member, but they do not meet the third giving criteria. Because we use the “and” operator in front of the giving criteria, they will not appear in the results.


Let’s move the parentheses around in our example and see how this changes things.




Now which of those same two constituents would appear in your results?

  1. A Board Member that lives in Florida and has given $15,000

  2. A Board Member who lives in Ohio and has given $2,000


The answer in this case is A and B. When we move the parentheses, we now take the second and third criteria as one combined statement. Because both constituents are Board Members, and we have an “or” operator before the criteria within the parentheses, it doesn’t matter if they meet the location or giving criteria. They automatically qualify because of their status as Board Members.


Hopefully these examples have helped to illustrate how just changing an operator or moving some parentheses can drastically change the results of your query. It’s so important to understand how these work together with your filter criteria so that you get the results you need!



Offices:

Headquartered in Boston and Charleston with consultants across the country

843-900-4287

  • BrightVine on LinkedIn
  • BrightVine on Facebook
  • Twitter
Let's Connect

© 2017 BrightVine Solutions | All Rights Reserved

BrightVine Solutions, Inc. (BrightVine) is a member of the Blackbaud partner network. BLACKBAUD®, THE RAISER'S EDGE®, BLACKBAUDINTERNETSOLUTIONS®, BLACKBAUD CRM®, and RAISER'S EDGE® are registered trademarks of Blackbaud.  BRIGHTVINE, the BRIGHTVINE® LOGO, BRIGHTVINE DATALOADER® and DELIVERING EXCELLENCE FOR GOOD® are registered trademarks of BrightVine Solutions.

 

All other third-party trademarks mentioned on this website are the property of their respective owners.  Learn more about BrightVine on our blog