Complex constituent searches

In this article:

Why use a complex constituent search?

While you can do most everyday queries by adding more criteria to a search, sometimes you need a bit more power. The Advanced Search function at the top of the Constituents page allows you to run complex searches by combining queries in different ways to get the search results you want.

These are the three different ways you can combine search queries together:

  • all of the following are true/and all: Results must match all of the criteria defined in the first query AND the second query
  • or all: Results must match all of the criteria defined in the first query OR the second query
  • and not: Results must match all of the criteria defined in the first query AND NOT match all of the criteria defined by the second query

Example: Combine multiple queries in a complex constituent search

Say you are searching for multiple criteria that are mutually exclusive, such as anyone who has given $500 over all time but hasn't yet donated this year. Using a simple search that includes the total giving amount and the gift dates from this year would not work because you are querying across multiple dimensions of a constituent's giving history at the same time (when did they last give any amount, and what is their total giving all time).

Here are the steps you can take to create this search:

  • Step 1: Navigate to your Constituents tab and, in the Advanced Search area at the top of that page, add an additional query (click Advanced options > Add additional query) after you've defined the first search ("Total Giving Amt." >= $500):

  • Step 2: Set the criteria for the second query ("Gift dates" in the current fiscal year):

  • Step 3: Pick the logic, or operator, for combining these two queries. In this case, we want everyone who's given more than $500 total but who has not given this year, so we will pick the and not option:

Now click the Search button to see a set of results containing constituents who meet the criteria in the first query and do not meet the criteria in the second query.

When to use a nested/sub query versus an additional query

With the same advanced options from the example above, you can add a nested/sub query or an additional query. What's the difference between the two?

Using a nested/sub query or an additional query allows you to control the order of operations within the search in different ways. A nested/sub query lets you add an additional query to an individual search statement (when there are multiple queries or statements in a search), whereas an additional query applies to the entirety of your search, including all the queries and subqueries.

NOTE: The rule of thumb for "or all" queries is that you should almost always nest them, unless you want all queries at the top level to be "or all". Doing this ensures that any additional criteria, such as exclusions ("and not" queries), apply to your entire result set.

As an example, say we run a query for constituents who gave $500 total or a single gift of more than $100 to our annual appeal but who have not given this year. We won't get the results we want if we build our query like this:

But we will get the results we want with a nested/sub query set up like this:

Using the nested sub query allows us to control the order of operations so that our search statement comes out like this:

  • ($500 total givers or $100 single last year) and not anyone who gave this year

Thinking in math terms

From another perspective, you can think of the nested/sub query and additional query like plus and minus signs and parentheses in math equations.

The order of operations doesn't matter if all queries are being combined with "and all" operators. It doesn't matter if you have just two queries you want to combine. In the same way, it doesn't matter if we write 3 + 4 instead of (3 + 4) (both equal 7), or 3 + 4 - 2 instead of (3 + 4) - 2 or 3 + (4 - 2) (all three equal 5).

However, it does matter if you want to add "or all" into the mix using more than two queries. This is like doing multiplication and subtraction. For instance, something like 3 X 4 - 2 could either mean:

  • (3 X 4) - 2: 10
  • 3 X (4-2): 6

This is where paying attention to the order of operations becomes important.

Related Articles