Running an "Is Not/Did Not" or Complex Query

Overview

Sometimes you will wish to run a query that gets a list of persons that did do something but did not do something else - for example, who did get an invitation but has not responded.  Alternatively, you may want to combine criteria from a few different areas and the criteria would be rather complex.

These kinds of queries is relatively straightforward when there is only one element of data to compare - i.e.: Attribute 1 is set but attribute 2 is not set - however, it can be challenging to accomplish when there can be multiple of the item in the system that you are searching for - such as registrations, contributions, attendances, etc, or when there is a very large amount of criteria to consider.

The Solution

While this kind of query can be achieved with a custom query provided by Gnosis support, you can also perform it yourself by breaking the query into parts and using a temporary attribute to store the results of each part.  The steps would like similar to the following:

1. Create a temporary checkbox attribute in Attribute Management
2. Set the attribute to "true" for all those that qualify for the first part of the query
3. Finally,
   • If the second part of your query needs to only include or exclude the persons in the first query, create the second query and filter the output to only include or exclude the attribute that you set in the previous step.
     -or-
   • If you are getting a list of people that are included in any one of the queries, then use each query to set the same attribute and when you are done, just run a query where that attribute is true.

Step by Step Details

1. Create your new temporary attribute as described in the article on Attribute Management
    
2. To set the attribute for all persons that "did not do the desired thing":
   1. go to the "Process Navigator" by selecting "Process" in the Navigators panel top left of your Gnosis application.
       
   2. Choose the "Attribute Import" option to launch the "Attribute Bulk Update Manager"
      1. Select the temporary attribute you wish to set on the left, then,
      2. check on the "New Value Action" checkbox and
      3. check on the value checkbox to indicate you wish to set that value to checked:
         
          
3. Click on the "Query Selection" tab on the right of the screen to indicate that you wish to set attribute values based on a query:
   
    
4. On the query selection tab:
   1. Select the attribute type you wish to update to Person
   2. Choose the Schema you wish to use for your query
   3. Define your query to select all of the "Did Not/Has Not" persons
      
       
   4. On the "Query Results" (blue) tab, Add the Person ID (PID) to the first line of output, followed by any other fields you may wish to view to help verify that your query is choosing the correct persons.
      
       
   5. Use the "View Query Results" button to view the query output to satisfy yourself that the query is performing as expected, then, when you are ready to set the attribute, press the "Process Query Results" button.
      
       
5. Now, you may fashion your desired query in reporting, recipient groups or ad-hoc queries to produce a list of persons that met all of the individual queries by referencing the set attribute in addition to any other criteria desired.