On each Query screen, you have the ability to filter the list of records that appear. Whether you are trying to find one particular record or a set of records matching specific criteria, queries are the primary search tool.
Each query is made up of one or more filter statements. A single filter statement contains at least two and sometimes three parts:
For part 1 (Field), you can select the field name from the drop-down list. If you do not see the field you are looking for, check Contact Filter & Option Fields under Settings to be sure the field has been selected as a Filter Field.
Part 2 (Condition) is also controlled by a drop-down list. The options that appear in the drop-down list may vary based on the field you select.
Part 3 (Text or Value) may not always be necessary depending on the condition you select. When text or value is required, how you populate it will depend on the field you select. Sometimes you can select from an authority file; other times you can type in the text or value.
After a filter is entered, click "Apply" to see the query results in the Browse Grid.
Simple queries have just one filter statement (one criterion for selecting the records).
Example 1:
You want to view all of the contacts who live in Texas.
The filter statement would look like this:
State | Is | TX
Example 2:
You want to view all of the contacts who have a primary email address.
The filter statement would look like this:
Primary Email | Is Not Empty
Complex queries involve multiple filter statements defining different criteria. How the filter statements are combined will affect the search results, either narrowing them or widening them. By combining filter statements with AND/OR and by using filter groups, you can create complex queries to retrieve the records you are looking for.
To add additional filter statements, click "Add Filter" again.
When you have two or more filter statements, you must join them with a connector: AND or OR. Which connector you choose will determine which records appear in the search results.
If you use OR, at least one of the filters must be true for each record to appear within the search results. Using OR gives you a broader range of records and therefore a higher number of results.
If you use AND, all filters must be true for a record to appear within the search results. Using AND makes your criteria more specific, which can narrow the search results.
Example 3:
You want to view all of the contacts who have donated over $5000 or paid over $5000 in dues or given in-kind gifts valued over $5000.
The filter statement would look like this:
Total Donations | Greater Than | 5000
Or
Total Dues Paid | Greater Than | 5000
Or
Total In-Kind Gifts Value | Greater Than | 5000
Example 4:
You want to view all the Active volunteers.
The filter statement would look like this:
Volunteer | Yes
And
Volunteer Status | Is | Active
Depending on your criteria, you might need to use both types of connectors. When you start combining ANDs and ORs, you need to use the Groups feature within your filter statements to get the desired results. The filters within each group are handled before the groups are connected; like parentheses in a math equation.
Example 5:
You want to find only the Individual and Family members who expired before 01/01/2018.
Since you are only looking for members who expired before 2018 who have either the Individual or Family membership types, you need to use both the OR and AND connectors.
The filter statement would look like this:
(Membership Type | Is | Individual
OR
Membership Type | Is | Family)
AND
Membership Paid Thru Date | Less Than | 01/01/20218
Example 6:
You want to see which of your volunteers interested in Elementary School Tours or Middle School Tours is available on Mondays or Tuesdays.
The filter statement would look like this:
(Volunteer Skills & Work Preferences | Is | Elementary School Tours
OR
Volunteer Skills & Work Preferences | Is | Middle School Tours)
AND
(Volunteer Available Monday | Yes
OR
Volunteer Available Tuesday | Yes)
For more information about saving the filters as views on the Query screen, see Creating Custom Views.