Quick Find & Power Query

ClearVantage Queries

A query is defined as a request for specific information that provides precise responses. Queries allow you to ask CVO these questions in order that find the records that meet specified criteria, and to group records in your database based on your defined criteria.

Creating and managing queries allows for the user to accurately export data from the database.

Queries are also helpful when used in CV to limit the scope of a task. You can base mailings, reports, exports, record searches, and action items on queries.

Note

For more information on how to build queries, please see our Create Queries Guide

Pro Tips for Searching

• Considerations when creating a search: What are the expected results? For what will the data be used?

• Start your search by entering broad criteria.

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

• Save time by using 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.

• 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).

• Enter NULL in a field search for instances where that field is NULL; NULL is not the same as blank.

• Lists can be sorted by any of the fields displaying in list results by selecting the arrow on the right side of the field.

Searching Overview

Within ClearVantage there are many options for searching for data.

1. The search bar, in a list view, is used for quick searches like last name or specific invoice number.

   1. There is a drop down to adjust the search criteria.

   

2. The Quick Find tool is the magnifying glass that is to the right of the search bar.

   

3. The Power Query tool allows users to fine tune their results and create specific searches with multiple criteria.

Search for Individuals and Organizations

1. Select “Membership” and click on either “Individuals” or “Organizations” based on your specific query.

2. Click on the Power Query icon.

   

3. Once you click on the Power Query icon, the search window will appear.

   

4. Click on the drop-down arrow next to any category to expand the category and view all the fields listed in that category that have been configured for your query tool. The columns of the Query window are:

   • Field Name:

   

   • Match Type:

   

   • Search For:

   

5. Find the field that you would like to search by in the Field Name column.

   Note

   The fields that are available in the power query tool are the fields that are available on the notebook of the record you are looking for. It can be helpful to review the notebook to confirm the field name.

6. Select the appropriate type from the Match Type field drop-down menu. Depending on the configuration of your query tool, you will have some of the following options from which to choose.

   1. =: 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.

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

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

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

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

   6. <>: 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 anything except what you enter in the Search For column.

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

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

   9. 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. Records with the dates entered in the query are included in the results.

      

   10. All Automatically selects every value available in the Search For column. Finds records where the values entered in the Search For column match at least one of the values in the Notebook.

   11. Any Finds records where the values entered in the Search For column match at least one of the values in the Notebook. This is used when multiple values are being entered in the Search For Column and uses “OR” logic between the values.

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

   13. NULL Finds records where the field is blank in the Notebook. Note that a field that contains only a space, though the space cannot be seen, is not considered blank.

   14. NOT NULL Finds records where the fields are not blank. 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).

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

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

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

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

   4. Variable Dates: Click the Switch to fixed date icon in any date field in the query tool. Variable dates allow you to pick a date variable (optionally with an offset) instead of a fixed date.

   

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

   2. 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 the options clearly. Select one of the radio button values to search.

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

8. Click on “Show Match Count” to see how many matches you have.

   

9. You can also select “Auto Count” to automatically have the count come up when new criteria are added. The match count will appear in the top right of the pop-up.

   

Search for Subscriptions, Dues, and Donations

You can search for Subscriptions, Dues, and Donations entered for individuals or organizations in your database.

1. Go to the Main Menu drop-down and click on “Membership”. Then click on “Dues/Sub/Don” list.

2. The “Find Dues/Sub/Don Records” window should open automatically. If not, click on the Quick Find icon.

   

3. This will open a “Find Dues/Sub/Don Records” window.

4. Add the appropriate information in the query fields or use the dropdown options to complete your search.

   

5. Click “Search”. This will open a list of dues/subscriptions/donations that meet the specified criteria.

6. Other Options:

   1. Click “Reset” to start a new search.

   2. Click “Cancel” to close the search window.

Search for Invoices

By searching for invoices in ClearVantage, users have the ability to export invoice related data where needed and filter results to provide printed or emailed invoices to members.

1. Go to the Main Menu drop-down and click on “Financials”, then click on “Invoices”.

2. The search window should open automatically. If not, click on the Quick Find icon to open the search window.

   

3. Add the appropriate information in the query fields or use the dropdown options to complete your search.

   

4. Click “Search”. This will open a list of invoices that meet the specified criteria.

5. Other Options:

   1. Click “Reset” to start a new search.

   2. Click “Cancel” to close the search window.

Search for Payments

By searching for payments in ClearVantage, users have the ability to export payment data where needed and filter results to easily review payment information when posting batches.

1. Go to the Main Menu drop-down and click on “Financials”, then click on “Payments”.

2. The search window should open automatically. If not, click on the Quick Find icon to open the search window.

   

3. Add the appropriate information in the query fields or use the dropdown options to complete your search.

   

4. Click “Search”. This will open a list of invoices that meet the specified criteria.

5. Other Options:

   1. Click Reset to start a new search.

   2. Click Cancel to close the search window.

Search for Event Registrations

We recommend using the individual search window to find all individuals with related event registrations for events and functions. See the Search for Individuals and Organizations sections for additional information.

Search for Action Items

1. Go to the Main Menu drop-down and click on “Administration”, then click “Search Action Items”.

2. Click on the Quick Find icon to open the search window.

   

   

3. Other Options:

   1. Click “Reset” to start a new search.

   2. Click “Cancel” to close the search window.

Saving a Power Search

When you save a list, you save a set query. You can pull up the results of the list at any time, but you cannot edit the parameters of the query itself. Unlike saved lists, Power Searches can be edited and re-saved at any time. This functionality is especially useful to users who have complex searches and would like to run different variations of that search.

The “Save Search” toolbar button allows users to save current search criteria and NOT criteria to the database. The “Load Search” toolbar button allows users to select a saved search to run.

1. Navigate to “Membership”, and then click on either “Individuals” or “Organizations” (depending on your specific search).

2. Click on the Power Query icon.

   

3. Enter your search criteria, and then click on the “Save Search” button in the left-hand corner of the page.

   

4. Enter a name for your search, and then click on “Save”.

   

Loading a Power Search

1. To run your search again, click on the “Load Search” button in the Power Search window.

   

2. Highlight the search that you would like to run and click on “Load”.

   

   • My Searches: Shows the power searches saved by you.

   • All Searches: Shows the power searches saved by other CV Online users with the same role as you.

3. If you would like to edit the search criteria for your saved Power Search, first load your Power Search, enter your new and/or expanded criteria, and then click on “Save Search”. Enter a new name for this search, and then click on “Save”.

Deleting a Power Search

Note

Only the owner of a saved Power Search can delete it.

1. Click on the "Load Search" button in the Power Search window

   

2. Click on the icon found in the "Action" column of the saved Power Search you wish to delete.

   

3. Confirm you would like to delete the saved Power Search by clicking "Yes".

   

4. The saved Power Search is now deleted.

Using AND, OR & NOT Searches

There are three search command options in ClearVantage that can be used to broaden, narrow, or ignore specific terms in search results.

• AND searches find all of the search terms. This narrows the search.

• OR searches find one term or the other. This expands the search.

• NOT eliminates items that contain the specified term. This narrows the search.

The Default Search (AND Searches)

All searches in CVO use an “AND” between criteria areas. An “OR” is only used in situations where special select features allow a user to select multiple items (e.g. on Interests, Member Types, States).

Note

This is based on the option labeled “Match All” at the top of the query screen being checked.

For example, if a user queried on the following criteria:

• Event Code = 180620-CONFERENCE in the Event area of the Query Individual screen

• Product Code = BNPJOURNAL in the Products area of the Query Individual screen

The system would search for all individuals who took both activities: registered for 180620-CONFERENCE AND bought the BNPJOURNAL.

Use the OR Search

At the top of the query screen is a checkbox labeled “Match All.” When this option is un-checked, the system uses “OR” logic between criteria in the search.

For example, if a user has the “Match All” box un-checked and queried on the following criteria:

• Event Code= 180620-CONFERENCE in the Event area of the Query Individual screen

• Product Code= BNPJOURNAL The Best Nonprofit Journal in the Products area of Query Individual screen

The system would return any individual who registered for 180620-CONFERENCE OR bought the BNPJOURNAL The Best Nonprofit Journal Product.

Select “NOT” Criteria

There is a tab in the top of the Power Query window called “Select NOT Criteria”.

The “NOT” tab allows you to search for individuals who do not have specific search criteria in their record. Perhaps you would like to search for individuals who have not attended an event or have not bought a certain product. The “NOT” search works in addition with all other search criteria selected on the other search tabs. For example, you could easily search for all individuals with an interest in the environment, who have not yet bought the new environmentally related product.

Working with Results

The Activities panel is located on the right side of the list results and provides a list of activities that can be performed on each record or the list as a whole. These activities vary depending on the category in which you are working.

Examples include:

• Send Emails

• Merge Duplicates

• Add to a Bucket

• Run Reports

• Export Data

Please review the Basics user guide for more information.