Creating Queries

A query is a request for specific information from ClearVantage to which you receive a precise response. Queries allow you to ask ClearVantage specific questions to find the records that meet the specified criteria and to group records in your database based on your defined criteria. Queries are also used in ClearVantage to limit the scope of a task. You can base mailings, reports, exports, record searches and action items on query results.

• Start your search by entering broad criteria if you are uncertain as to how or if something is entered.

• Always try searching by different fields (i.e. zip code, email, etc.) if you cannot find a customer.

• Filter a broad list to see a subset of the results without recreating the query.

• Use Saved Lists for queries that you use often. You can set a default list to appear by starting the name of the saved list with (Default). The saved list will run when you open a list window and the query or quick search windows will not appear by default.

• Export your search criteria if you think you will need to modify them later.

• % can be used as a wildcard (i.e. WER% finds WERE and WERHEIM; STE%ENS finds STEPHENS and STEVENS).

• Entering NULL in a field searches for instances when that field is NULL; NULL is not the same as blank.

Search for Individuals, Organizations, Events, Products, or Documents

Each of these different areas of ClearVantage use query tools that function in the same way. From the Main Menu Dropdowns:

1. Select the appropriate query tool and the Search window will open.

   1. For Individuals, select Membership | Individuals | Power Query > tool.

   

2. For Organizations, select Membership | Organizations | Power Query tool.

3. Click on the arrow next to any category (eg 01 – Membership, 02 – Contact) to expand the category and view all of the fields listed in that category that have been configured for your query tool.

   

4. Find the field that you wish to search in the Field Name column.

5. Select the appropriate type from the Match Type field dropdown. The default option will depend on the configuration of your query tool. You will have some of the following options from which to choose.

   

   • =: Finds records where the value entered in the Search For column exactly matches the value in the Notebook. This is used when only one value is being entered in the Search For Column.

   • >=: Finds records where the value in the Notebook is greater than or equal to the value entered in the Search For column.

   • <=: Finds records where the value in the Notebook is less than or equal to the value entered in the Search For column.

   • >: Finds records where the value in the Notebook is greater than the value entered in the Search For column.

   • <: Finds records where the value in the Notebook is less than the value entered in the Search For column.

   • <>: Finds records where the value in the Notebook does not equal the value entered in the Search For column. This is used for finding records where the value is a value other than what you enter in the Search For column. Records where the field being searched is NULL will be excluded from the results.

   • **Like: ** Finds records where the value in the Notebook partially matches a value with a wildcard (%) entered in the Search For column.

   • Not Like: Excludes records where the value in the Notebook partially matches a value with a wildcard (%) entered in the Search For column.

   • Between: Finds records where the value in the Notebook falls within the range of values entered in the Search For column. This is often used for finding records where a Join Date or Termination Date falls in a particular range (inclusive of the dates entered). Enter the first date of the range in the Search For column and enter the end of the range in the fourth column which has no column title.

   • Any: Finds records where one of the values entered in the Search For column is the only value in the Notebook. This will not include results where multiple values are in the Notebook.

   • Contains: Finds records where the values in the Notebook include at least one of the values entered in the Search For column. This is used when multiple values are being entered in the Search For Column and uses OR logic between the values. Additional values may also be present in the Notebook.

   • All: Finds records where the values entered in the Search For column are included in the values selected in the Notebook. This is used when multiple values are being entered in the Search For Column and uses AND logic between the values. Additional values may also be present in the Notebook.

   • NULL: Finds records where the field is blank or is a SQL NULL. Use this method to search for blank values in the Notebook field such as members with no email. If you clear a criterion that uses the NULL Match Type, you will need to select a different Match Type and then reselect the NULL Match Type to use it for that field again so that the Search Value will repopulate.

   • NOT NULL: Finds records where the fields is not blank or a SQL NULL. In effect, this finds records where the field contains information. If you clear a criterion that uses the NOT NULL Match Type, you will need to select a different Match Type and then reselect the NOT NULL Match Type to use it for that field again so that the Search Value will repopulate.

6. Enter the value you wish to search for in the Search For column for the row of the field you wish to search. Depending on the configuration of your query tool, you will be able to enter the value to search for in one of the following ways:

   • Single Select Dropdowns: Click on the dropdown arrow in the Search For column and select one option from the dropdown menu. You should generally use the = or <> Match Types with this style of Search For value entry.

   • Multi Select Dropdowns: Click on the dropdown arrow in the Search For column and a dropdown list will appear. Click on the boxes next to the values you wish to search for to enter a checkmark. Use the All, Any or Contains Match Type to indicate how you want to search for the multiple values. Using either the = or Any Match Types will result in the same results.

   • Calendars: Click on the dropdown arrow in the Search For column and click on the appropriate date from the Calendar. You can also type the date into the field using the MM/DD/YYYY format.

   • Checkboxes: Click in the box in the Search For column to check the checkbox. A check will indicate Yes or that the box is checked on the Notebook. An unchecked box will indicate No or that the box is unchecked on the notebook. You can search for either checked or unchecked values.

   • Radio Buttons: Click in the Search For column and the radio button values will appear. You may need to click in the Search For column a second time to expand the box to see all of the options clearly. Select one of the radio button values to search.

   • Open Text: Type the value you wish to search for in the Search For column. You must use the Match Type of Like to search using a wildcard.

   

7. Confirm that you have entered the search criteria correctly. All of your search criteria will be listed in the Selected Criteria section.

8. Choose whether you wish to conduct the search using the criteria AND between each search criterion or an OR between each field being searched. To use an AND between each criterion in the same category, check the Match All checkbox at the top of the screen. To use an OR, uncheck the Match All checkbox. For more information, see the sections on AND/OR searches later in the guide.

9. Press the search button to run the search and return a list of records that match your criteria.

Saving Search Criteria

If you want to save a set of search criteria for later use, simply:

1. Populate the Selected Criteria section with the search you wish to save using the steps above.

2. Select “Save Search” in the bottom left corner.

3. You will be prompted to type a name for the saved search.

4. Click save.

This saved search will now be available when you select “Load Search” in the Power Query tool.

Query Examples for =, Any, All and Contains

The Match Types of =, All, Any and Contains are probably the most confusing of the match types because of how the results vary based on the type of field. These are mostly used when querying fields that hold comma separated lists of values such as interest fields. For example, searching in the Interests field and checking Interest 1 and Interest 2 will provide different results based on the selected Match Type.

• = – Results listing an exact match to the values entered in the query

• Any – Results listing either Interest 1 or Interest 2 but not both and no other interests

• Contains – Results listing either Interest 1 or Interest 2 as well as any other interests

• All – Results listing both Interest 1 and Interest 2 as well as any other interests

Example 1: Querying a field with a Single Value

If you wanted to search for individuals with a primary address in a Midwestern state, you could use the individual query tool to find people where the StateCD field lists one of several states. By way of example, we have a member whose primary address is in Indiana. This field only holds one value “IN”. The Match Type tells ClearVantage how to compare the value to the value entered in the query tool.

Member's Data	Match Type	Search For	Result
IN	=	IN,OH,IL,IA	Match. The EQUAL operator checks to see if the member's data is exactly included in the list of options selected. The match is with “IN” or “OH” or “IL” or “IA”.
IN	Any	IN,OH,IL,IA	Match. The ANY operator checks to see if the member's data equals any of the values searched for. The match is with “IN” or “OH” or “IL” or “IA”.
IN	All	IN,OH,IL,IA	No match. The ALL operator checks to see if the member's data contains all of the values searched for. The “IN” is found in the member's data but the other values are not.
IN	Contains	IN,OH,IL,IA	Match. The CONTAINS operator checks to see if the member's data contains any of the values searched for. This member would have been included in search results that included "IN".

Example 2: Querying a field with Multiple Values

If you were to track "States Practicing In" (a multi select list of states) you may want to find individuals practicing in a Midwestern state. In the query tool, a user can check off up to 50 states to include in the search. By way of example, we have a member who is practicing in Indiana and Ohio. Useful to understand that this data is stored as a comma-separated list ("IN,OH") and when a user check off values to Search For in the query tool, they also go in as a comma-separated list. The Match Type tells ClearVantage how to compare those two lists.

Member's Data	Match Type	Search For	Result
IN,OH	=	IN,OH,IL,IA	No match. The EQUAL operator checks to see if the member's data is exactly equal to the searched for text. "IN,OH" is not the same as “IN” or “OH” or “IL” or “IA”.
IN,OH	Any	IN,OH,IL,IA	No match. The ANY operator checks to see if the member's data equals any of the values searched for. In this example, "IN,OH" is not equal to "IN" or any of the other values checked off.
IN,OH	All	IN,OH,IL,IA	No match. The ALL operator checks to see if the member's data contains all of the values searched for. The first two values are found in the member's data ("IN" and "OH") but the last two are not ("IL" and "IA").
IN,OH	Contains	IN,OH,IL,IA	Match. The CONTAINS operator checks to see if the member's data contains any of the values searched for. This member would have been included in search results that included "IN" or "OH".

Advanced Query Examples

One of the advantages of the query tools for individuals, organizations, projects, documents and events is the ability to customize the query settings for each client. Depending on your configuration, you may be able to use either of the search methods below or both. Your system administrator can configure these options for your database or you can contact Euclid Technology for assistance.

Search Using Multiple Rows of the Same Field

Your system can be configured so that you can search the same field in multiple rows but this is generally not part of a standard ClearVantage configuration. This allows you to place an AND or OR conjunction between values in the same field or enter multiple values to be searched.

From the query screen:

1. Press the arrow to expand the category that contains the field you want to search.

2. In the first Search Field for the notebook field, enter the value you want to find in the Search For column.

3. In the second Search Field for the notebook field, enter the next value you want to find in the Search For column.

4. Continue adding values to the subsequent Search Fields until you have entered your entire search. If you have additional values to enter, you may consider breaking your search into pieces and grouping the results in the activity bucket or using the memo field search option shown below.

5. Choose whether you wish to conduct the search using the criteria AND between each search criterion or an OR between each field being searched. To use an AND between each criterion, check the Match All checkbox at the top of the screen. To use an OR, uncheck the Match All checkbox. For notebook fields that can only contain one value, you will need to uncheck Match All or you will not return any results.

   

6. Press the search button to run the search and return a list of records that match your criteria.

Sample Searches

Searching for Addresses in any one of Several Zip Codes

1. Press the arrow to expand the category that contains the field you want to search. Zip codes are usually in the 02 – Contact category.

2. Enter the first zip code in the Zip field.

3. Enter the second zip code in the Zip 2 field.

4. Enter any additional zip codes into the additional Zip fields.

5. You can also use the Between Match Type to enter a range of zip codes.

6. Uncheck Match All.

7. Press the search button to run the search.

Searching for Individuals with specific Individual ID numbers

1. Enter the first Individual ID in the Individual ID field. Individual ID is usually in the 01 – Membership Category.

2. Enter the second Individual ID in the Individual ID 2 field.

3. Enter any additional Individual IDs into the additional Individual ID fields.

4. Remember that you can also use the Between Match Type to enter a range of Individual IDs.

5. Uncheck Match All.

6. Press the search button to run the search.

Searching Using a Memo Field

A Memo search field can be configured in ClearVantage to allow you to enter a simple SQL statement for your search.

From the query screen:

1. Press the arrow to expand the Others category.

2. In the Other Parameters field, select =Memo as the Match Type.

3. Enter your SQL statement in the search for column. Below are some sample statements which you can enter.

   • CustomerCD in (***,***,***,***)

     • In the Search Individuals screen, enter any Individual ID numbers in place of the asterisks and you will return a list of all the individual records with those Individual ID numbers.

     • As many Individual ID numbers as you wish can be entered by entering the numbers separated by commas, without spaces between the parentheses.

   • Orgcd in (***,***,***)

     • In the Search Individuals screen, enter any Organization ID numbers in place of the asterisks and you will return a list of all the individual records that are linked to the organizations with those Organization ID numbers.

     • In the Search Organizations screen, enter any Organization ID numbers in place of the asterisks and you will return a list of all the organization records with those Organization ID numbers.

     • As many Organization ID numbers as you wish can be entered by entering the numbers separated by commas, without spaces between the parentheses.

4. Click in the blank Selected Criteria area to load your search criteria once you have entered all of them. All of your search criteria will be listed in the Selected Criteria section.

5. Press the search button to run the search and return a list of records that match your criteria.

Search for Products

There are two primary ways to search for products in your database.

Using Quick Find

From the Services Dropdown:

1. Select the Products tab.

2. Click on the Quick Find icon.

   

3. Enter any combination of values you want to find.

   

4. Press the search button to run the search and return a list of products that match your criteria.

Using Filters

Filters can also be used to quickly find products. To perform a filter search, navigate to the Services dropdown and select Products.

1. Open the filter type dropdown and select which type of search you will be performing. For products you can filter by:

   1. Product Name

   2. Product Code

2. Begin filling in your desired filter. The results list will update as you type, or you can select “Go” to display the filtered results.

   

Search for Product Orders

There are two primary ways to search for product orders in your database.

Using Quick Find

From the Processes Dropdown:

1. Select the Order Fulfilment tab.

2. Click on the Quick Find icon.

   

3. Enter any combination of values you want to find.

   

4. Press the search button to run the search and return a list of products that match your criteria.

Using Filters

Filters can also be used to quickly find product orders. To perform a filter search, navigate to the Processes dropdown and select Order Fulfilment.

1. Open the filter type dropdown and select which type of search you will be performing. For orders you can filter by:

   1. Payee Name

   2. Payee ID

   3. Invoice Number

   

2. Begin filling in your desired filter. The results list will update as you type, or you can select “Go” to display the filtered results.

Search for Subscriptions, Dues or Donations

To search for dues, subscriptions, or donations entered for individuals or organizations in your database, first:

1. Navigate to Membership and select Dues/Sub/Don list.

2. This will open the search window, which allows you to search by any combination of the following criteria:

   

3. Select “Search” to return a list of subscriptions, dues, or donations that match your specified criteria.

Search for Subscriptions

There are two primary ways to search for subscriptions in your database.

Using Quick Find

From the Services Dropdown:

1. Select the Subscriptions tab.

2. Click on the Quick Find icon.

   

3. Enter any combination of values you want to find.

   

4. Press the search button to run the search and return a list of products that match your criteria.

Using Filters

Filters can also be used to quickly find subscriptions. To perform a filter search, navigate to the Services dropdown and select Subscriptions.

1. Open the filter type dropdown and select which type of search you will be performing. For subscriptions you can filter by:

   1. Description

   2. Code

   

2. Begin filling in your desired filter. The results list will update as you type, or you can select “Go” to display the filtered results.

Search for Invoices

There are two primary ways to search for invoices in your database.

Using Quick Find

From the Financials Dropdown:

1. Select the Invoices tab.

2. Click on the Quick Find icon.

   

3. Enter any combination of values you want to find.

   

4. Press the search button to run the search and return a list of products that match your criteria.

Using Filters

Filters can also be used to quickly find invoices. To perform a filter search, navigate to the Financials dropdown and select Invoices

1. Open the filter type dropdown and select which type of search you will be performing. For invoices you can filter by:

   1. Invoice Number

   2. Payee Name

   3. Payee ID

   

2. Begin filling in your desired filter. The results list will update as you type, or you can select “Go” to display the filtered results.

Search for Payments

There are two primary ways to search for payments in your database.

Using Quick Find

From the Financials Dropdown:

1. Select the Payments tab.

2. Click on the Quick Find icon.

   

3. Enter any combination of values you want to find.

   

4. Press the search button to run the search and return a list of products that match your criteria.

Using Filters

Filters can also be used to quickly find invoices. To perform a filter search, navigate to the Financials dropdown and select Payments.

Open the filter type dropdown and select which type of search you will be performing. For payments you can filter by:

1. Payment Number

2. Invoice Number

3. Payee Name

4. Payee ID

   

5. Begin filling in your desired filter. The results list will update as you type, or you can select “Go” to display the filtered results.

Search for Documents

You can create a list of individuals having certain documents by using Search Individuals. Alternatively, the Documents List allows you to create a list of just documents. This screen must be added to User Roles. Ask your System Administrator if you wish to use the Documents List screen.

Searching for Individuals with Documents

From the Main Menu Dropdowns:

1. Select Membership | Individuals and open the Power Query tool.

2. In the 28 – Documents category, enter the value you want to find in the Search For column.

3. Continue adding values to the subsequent Search Fields until you have entered your entire search.

4. Press the search button to run the search and return a list of individuals that match your criteria.

Basic AND, OR & NOT Searches

Subscription, Invoice, and Payment Queries

AND Searches

By default, searches in ClearVantage use an AND between criteria areas. Using the AND in a query pulls results that fit into both of the criteria joined by the AND.

The circles below represent two different fields being searched. Records that match the first field are in the left circle and records that match the second field are in the right circle. The circles overlap where both fields are matched. The shaded area represents results that match the left circle AND the right circle.

For example, this search is used when searching for individuals who are both members and have a member type of Senior.

OR Searches

On the right of the search screen is a checkbox labeled Use OR Logic. When this option is checked, the system uses OR logic between criteria in the search.

Both circles are included in the results when the search is for records that match the left circle OR the right circle.

This search is used for finding individuals who are either members or have a member type of Senior. This is inherently a larger group thatn the results of the AND search.

When the default settings are used, an OR is also used in situations where special select features allow a user to select multiple items within the same field (e.g. Bill Period on the Invoice Search). The search below is searching where the invoice is in Bill Period 2014 -12-Dec OR 2014-11-Nov since an invoice can’t list both periods at the same time.

Individual, Organization, Event & Product Queries

Match All

The Match All checkbox on the top of the query screen determines whether an AND or an OR is used between search criteria that are within the same group. When Match All is checked, an AND is used between the criteria and records are found that match all of the listed criteria. When Match All is unchecked, an OR is used between the criteria and records are found that match any one of the selected criteria. In most cases you will want to leave Match All checked. See the section Complex And & Or section for examples.

Use "Not" Query

When Querying Individuals or Organizations there is a tab in the search screen labeled “Not.”

The Not tab allows you to search for individuals who do not have specific search criteria in their record. Perhaps you want to search for individuals who have not attended an event or have not bought a certain product.

The Not search is in addition to all the other search criteria selected on the other search tabs. Whereas the Set Filter Options tab searches allow you to search where a value does not equal something, the Not tab is searching for things that do not exist. For example, you could easily search for all individuals with an interest in the environment, who have not yet bought the new environment related product. In this case the purchase of the product does not exist.

Searching for Individuals without Action Items

The default configuration is to allow you to search for individuals who do not have action items in a particular action list or action lists or do not have any action items.

1. Click on Query Individuals from the Membership Main Menu Dropdown to open the Search Individuals window.

2. Enter the search criteria you want to include on the Set Filter Options tab.

3. Click on the Not tab.

4. From the Category dropdown, select Action List as the type of record that individuals should not have to be included in your results. A selection of field names will appear below.

5. You can now enter your NOT search criteria.

   1. To search for individuals who have no action items, check the box to the right of No Action Items in the Search Value column.

   2. To search for individuals who have no Action Items with a particular Action List:

      1. Click on the dropdown in the Search Value column for the row with the Field Name No Actions On This List and the Match Type of Any.

      2. Check the single Action List you wish to include in your Not query.

      3. Click out of the dropdown list of Action Lists.

   3. To search for individuals who have no Action Items in any of a set of Action Lists:

      1. Click on the dropdown in the Search Value column for the row with the Field Name No Actions on the List and the Match Type of Any.

      2. Check each of the Action Lists you wish to include in your Not query.

      3. Click out of the dropdown list of events.

6. If you are using the Auto Count, refresh your count by either clicking on the calculator button or clicking back onto the Set Filter Options tab.

7. To remove the Not criterion from your search, press the Clear Criteria button on the Not tab. The Not criterion will not be removed from future searches unless you either clear this tab or log out of ClearVantage.

Searching for Individuals without Registrations

The default configuration is to allow you to search for individuals who have not registered for a particular event(s) or have not registered for any events.

1. From the "Membership" main menu dropdown, click on "Individuals" to open the individual search window.

2. First, enter the search criteria you want to include in the "selected criteria window" before adding NOT criteria.

3. Click on the Not tab.

4. From the Category dropdown, select "Event" as the type of record that individuals should not have to be included in your results. A selection of field names will appear below.

5. You can now enter your NOT search criteria.

   1. To search for individuals who have never registered for an event, check the box to the right of No Event Registrations in the Search Value column.

   2. To search for individuals who have not registered for a particular event:

      1. Click on the dropdown in the Search Value column for the row with the Field Name Have Not Registered For and the Match Type of Any.

      2. Check the single event you wish to include in your Not query.

      3. Click out of the dropdown list of events.

   3. To search for individuals who have not registered for any of a set of events:

      1. Click on the dropdown in the Search Value column for the row with the Field Name Have Not Registered For and the Match Type of Any.

      2. Check each of the events you wish to include in your Not query.

      3. Click out of the dropdown list of events.

6. If you are using the Auto Count, refresh your count by either clicking on the calculator button or clicking back onto the Set Filter Options tab.

7. To remove the Not criterion from your search, press the Clear Criteria button on the Not tab. The Not criterion will not be removed from future searches unless you either clear this tab or log out of ClearVantage.

Complex AND & OR Searches

There are 3 checkboxes on the Individual, Organization, Event and Product query screens which control the conjunctions used between fields and tables in the search. Using these checkboxes allows you to place an AND or an OR between fields or between tables in your search criteria.

Each Individual and Organization Notebook combines data from a series of tables. The information on the profile tab of the Individual Notebook is stored in the CUSTOMER table and the information on the profile tab of the Organization Notebook is stored in the ORGANIZATION table. Additionally, the information on each history tab is pulled from a separate table. For example the Events history tab pulls from the INDSESSION table and the Payments history tab pulls from the PAYMENTS table.

Understanding Groups

When the Individual, Organization, Event, and Product query tools are configured, the fields that are searchable are separated into Groups. These groups appear as categories in the query tool (01 – Membership, 02 – Contact, etc.) but also serve to point to the particular SQL table or view where the data being searched is stored.

When creating a complex query using AND and OR, it is important to recognize when data is stored in the same group or in different groups as this will impact how you structure the query and your results. Though multiple Groups may search the same table, generally each group searches a unique SQL table or view. Groups that are actually searching the same table can generally be considered the same group. An example of this is that the Membership and Demographic groups in the standard configuration of the individual query are both searching the CUSTOMER table and can be considered the same group. Note that it is possible to set these up as separate groups using views if desired.

Match All Checkbox

As discussed earlier in this guide, the Match All checkbox determines whether an AND or an OR is entered between individual fields in your search within each group that is in the search. For example, if you are querying only fields listed in the Membership category of the individual query, checking the Match All checkbox will mean that you are putting an AND between each field. The records returned will match every one of the criteria listed. For those same search fields, unchecking the Match All checkbox puts an OR between each field. The records returned will match at least one of the search criteria.

When you are searching multiple categories, the Match All checkbox still applies within each group rather than between groups. The conjunction used between groups is the purpose of the boxes discussed in the next section.

Or for Groups/And with Or for Groups

The Use Or for Groups and Use And with Or for Groups checkboxes control whether an AND or OR is placed between the groups in the search. If neither checkbox is checked, ClearVantage will place an AND between the groups by default. Searching fields in both the Contact group (Profile tab) and the Events group (Events tab) will return results where the record matches information in each of these areas.

The Use Or for Groups checkbox places an OR between each of the groups in the search. Searching fields in the Contact group (Profile tab) and the Events group (Events tab) with this checkbox marked will return records that match the information on at least one of the tabs.

The Use And with Or for Groups checkbox places an AND between the groups that search the CUSTOMER or ORGANIZATION tables (ie Contact, Demographic, Membership) with an OR between the other groups. Checking this box and searching fields in the Contact group (Profile tab) and the Events group (Events tab) and the Certification group (Certifications tab) will return records that match the Profile tab and at least one of the other tabs.

Complex AND & OR query examples

Below we are searching with four criteria:

• From the “01 – Membership” group (CUSTOMER table) – “Member?” is checked.
• From the “01 – Membership” group (CUSTOMER table) – Paid Thru Date is 07/01/2022.
• From the “07 – Event group” (SESSION table) – registered for the event 2022EMS.
• From the “07 – Event group” (SESSION table) – registration is not cancelled.

Using this query criteria, we can explain the differences between “Match All”, “Use OR for Groups”, and “Use AND with OR for Groups”, and using different combinations of these options.

The default search is “Match All”. If only this is checked, then you will search for individuals where all of the criteria is met:

• They have the member flag checked.
• The person has a paid thru date = 07/01/2022.
• They have registered for 2022EMS.
• Their registration for that event is not cancelled.

Before we move on to the other match options, we need to describe what a group is. A group is the collapsible sections of the power query like “01 – Membership”, “04 – Committees”, “07 – Event”, etc. These sections often pull from different tables. However, some groups pull from the same table, like “01 – Membership” and “02 – Contact” both pull from the CUSTOMER table.

If you check off only “Use AND with OR for Groups” then you are searching for individuals where both a) AND b) are true:

a) Individuals who have the member flag checked OR the member’s paid thru date is 07/01/2022 .

b) Individuals who have registered for 2022EMS OR they have a registration for some event that is not cancelled.

This option only makes sense to use if you have multiple query criteria within a group (e.g. searching by member flag and paid thru date within “01 – Membership”). Perhaps a more realistic query using the same groups might be searching for individuals who a) have the member flag checked OR member type = Speaker and b) have attended 2022EMS or any event with the program type of Conference.

If you check off only “Use OR for Groups” then you are searching for all the individuals who meet any of the criteria that are selected.

If you check off “Match All” and “Use OR for Groups” then you are searching for individuals where either a) or b) are true:

a) Individuals who have the member flag checked AND the member’s paid thru date is 07/01/2022 .

b) Individuals who have registered for 2022EMS AND their registration for that event is not cancelled.

You might use this query in real life if you wanted to email existing members as well as the non-members who attended an event:

The following options don’t make sense to run:

• All three options checked – same as just having “Match All” selected.
• Match All and Use AND with OR for Groups – same as just having “Match All” selected.
• No options selected – same as “Use AND with OR for Groups”.