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

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

In a nutshell, using a nested/sub query allows you to control the order of operations in the query. This is similar to a concept you may remember from basic arithmetic.

The order of operations doesn't matter so much if all queries are being combined with and type operators or if you have just two queries you want to combine, just as it wouldn't matter if we wrote 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 into the mix with more than two queries. This is like doing multiplication and subtraction in math. For instance, something like 3 X 4 - 2 could either mean:

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

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

We will get what we want with a nested/sub query, like this:

Using the sub query allows us to control the order of operations so that it comes out like this:

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

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".

Related Articles