Data Viewer: Building a Query

Any time you are building a query, you will use Data Viewer functionality. All of the different options for query building are discussed below. The directions below assume that you started with the Data Viewer.

• Include all records
• Create a new query
• Custom Filters
• Use a saved query
• Use a criteria template
• Include Privacy Protected Records
• Non-exportable fields
• Large Exports

Include all records

This query will pull data on all records in the database, including members and nonmembers of the community.

1. Select Data Viewer.

2. Select  Include all members from the community. This is the default option.

3.  Click Next.

4. The Additional Actions that can be taken on the query results are shown below.

Additional Actions

Action	Description
Send to Export	Directs you to the export area to finish building an Export.
Send to Email Marketing	This option should NOT be selected since it was for the Legacy Email Marketing tools.
Update Value	Allows you to update a field value for all the members in the query you built. You will then need to select the field for which you want to update the value. See NOTE below for more information.
Create Group	This option allows you to create a Group based on your query.

5. Pick the appropriate radio button for the action you want to take and click Go. Based upon the radio button you selected, you will be moved to the appropriate area.

 

Create a new query

Allows you to select specific fields or apply Community Roles to create a query.

1. Select Data Viewer.

2. Select Create a new query.  If you only select Create a New Query (without selecting Apply Community Roles) you will proceed to create a new query by selecting from a list of the information fields contained in various forms.

You have the option to Apply Community Roles. Apply Community Roles allows you to only pull the data for members who fit the criteria for a certain role (i.e., Class of 1992, Current Students, or Female alumni). Mark the necessary boxes based upon how you need to query.

• Combine Roles with Data Fields and Activity Filters – Selecting this option will allow you to filter the data using a combination of data fields from previously created forms and from Roles that have been created. For example, you could filter for people who attended a Charity Event and who are Engineers (Role).
• Include these Roles - Choose from the previously created list of roles in the list box. Use Ctrl or Shift to select multiple items.
• Exclude these Roles - You may also exclude certain groups by using this checkbox. Choose from the previously created list of roles to select a group to exclude. Use Ctrl or Shift to select multiple items.

3. Click Next.

4. Click Add Field to create your query.

• Quick Fields List: Select from an alphabetical listing of existing fields from previously created forms.  Click on the field(s) you want to use.
• Select from a form: Select from a list of existing fields by searching previously created forms to find the desired field.
• Click on the plus signs (+) to drill down in the hierarchy.  Click on the desired Field to select.
• The field limit for the query is up to 26 fields. Click Add Selected Fields.

5. Define Criteria

Set the value for the field you are using. If you are only using one field, click Apply Filter to view the members who meet the criteria. If you need to add another field, Click Add Field and click on the next field you want to use.

Filters can be created in the following ways (depending on the data field chosen):

• Equals – Allows you to search for entries that match exactly what is typed into the box or what is selected from the dropdown list.
• Null Query option - For checkbox field types (Boolean fields), which only have the single operator option of "equals", there is also an option to "Include blank values". Use this option to include those constituents who have not yet responded to the field (null values).
• Not equal to – Searches for entries that do not match what you have typed into the box or selected from the dropdown list.
• Null Query option - For all field types that provide a "Not Equal To" option, there is also an option to "Include blank values".  This enables you to construct your query so that constituents that have not responded to the particular field (therefore have a null value) can also be included in the query.
• Does not have a value – Allows you to search for entries that do not have a value in that field (it has been left blank).
• Has a value – Searches for entries that have any data in that field, regardless of what it is.
• Search multiple values - Enter multiple values for your search.  Each value must be on a separate line.
• Contains, Does Not Contain, Starts With, Ends With are other filter options that may be available depending on the type of field.
• Does Not Start With - Searches for entries that do not start with what is entered into the value box.
• Ends With - Searches for entries that end with what is entered into the value box.
• Does Not End With -  Searches for entries that do not end with what is entered into the value box.

6.  Write Query Logic
This query should be built with: If you select more than one filter field, then you will need to specify whether the query should be built with “AND Statements” or “OR Statements”. Click Apply Query once all of your fields are selected. You can delete a filter field by clicking the red icon to the right of your field.

• AND Statements – This option will apply the filters to your data so that each set of data must have a match for everything for which you are filtering. For example, if you are filtering for members from California AND for Teachers, only Teachers who live in California will be pulled.

• OR Statements – This option will apply the filters to your data so that each set of data only needs to have a match for one of the fields for which you are filtering. For example, if you are filtering for members from California OR for Teachers, all members from California (regardless of occupation) and all Teachers (regardless of where they live) will be pulled.

• Custom Where Logic - This option allows you to create your own complex query logic by writing out the query string using the letters A through Z which have been assigned to the criteria/field.  Parentheses are used for grouping and the following operators can be used in the query:
  
  • AND = For a row to be selected all the specified conditions must be true. (This would be the default.)
  • OR = For the row to be selected at least one of the conditions must be true.
  • For example, if you are looking for constituents with Class Years of 1980-1985 (A) that live in California (B) or that have a Chemical Engineering degree (C).  The query would be A AND (B OR C).
  NOTE:  Member Activity Filters are not affected by AND/OR logic.

7.  A preview of your query results will be shown in a table at the bottom of the page. You can check the box in the Exclude column to exclude a member’s data from the report/export.

8. Click Next to proceed.

9. If you want to save the query, select the Save Query option. Based upon your administrative rights, you may see a Create New Email Segment checkbox. There is also a Save as Criteria option to save criteria values so that it can be used for future reports/exports. A criteria template saves the fields but not the specific filters.  For example, if you queried on members with the Home State of California, the criteria template would be saved as a query for Home State. When you use it again you would specify the state you needed.

10.  The Additional Actions that can be taken on the query results can be viewed here.

11. Pick the appropriate radio button for the action you want to take and click Go.  Based upon the radio button you selected, you will be moved to the appropriate area.

 

Custom Filters

Custom Filters provide functionality for getting just the information you need.

NOTE:  As changes are made to Encompass some filters may display as options but will not return results.

Form Progress Filters
Find information on how users are progressing through a Form.  Have some users completed everything except the last step?  Are certain steps not being completed by users?
Select the Form Name and choose whether you want information on Completed Steps or Incomplete Steps.  You can drill down further by selecting step(s) from the dropdown.

Event Filters
Select the Event, and then you can drill down further to find information on that event's Activities, Registration details and Tickets and Merchandise.  The options available are customized based on the Event.
Registration Filter - Select from the following:
    All Registrations
    Registrants that are attending    
    Registrants that are NOT attending
    Registrants that have guests attending
    Members who have NOT registered Donation Filters
Select one of the following options to find information on Donations in your community.
Filter by All Donors - Find all members who have made a Donation.
Filter By Donation Date Range - Find all members who have made a Donation within a specified time frame.
Filter by Donation Amount Range - Find all members who made a Donation within the specified amount parameters.
Apply selected Donation filters
Select a Donation Campaign from the dropdown to find more information on that specific campaign.  Based on the individual campaign you will also be able get information on Donation Levels and Designations.
Or you can Select those who did not donate to the campaign to get information on members who have not donated to the selected campaign. Dues Filters
Select one of the following options to find information on Dues in your community.

Filter by All Dues purchasers - Find all members who have paid Dues.
Filter By Dues Date Range - Find all members who have paid Dues within a specified time frame.
Filter by Dues Amount Range - Find all members who paid Dues within the specified amount parameters.
Apply selected Dues filters
Select a Dues Campaign from the dropdown to find more information on that specific campaign.  Based on the individual campaign you will also be able get information on Dues Levels and Types.
Or. you can Select those that did NOT purchase from this campaignto get information on members who have not paid Dues to the selected campaign.

Sub-Community Filters
Select one of the following options to find information on the Sub-communities in your community.

Filter by members who belong to any Sub-community - Find all members who belong to any of the sub-communities in your community.
Filter by members who DO NOT belong to any Sub-communities - Find all members who are not members of any of your community's sub-communities.
Filter by all members who belong to a specific Subgroup
Select a Sub-Community from the dropdown to find more information on a specific Sub-community.

Tracking Actions Filters
Tracking actions track what admins and constituents have been doing when they visit the site. 

Select the action(s) you want to track, using the CTL or Shift key to select multiple actions, taken by Members, or select from the Actions Not Taken by Member. Enter a Start Date and an End Date to select a date range for your data. Leave the dates blank to see all data regardless of date. The system returns users based on the selected criteria.

Group Filters
Select one of the following options to find information on the Sub-communities in your community.
Filter by members who belong to any Group including auto-groups - Find all members who are members of any of the Groups in your community.
Filter by members who DO NOT belong to any Groups including auto-groups - Find all members who do not belong to any Groups.
Filter by all members who belong to a specific Group excluding auto-groups - Select a Group from the dropdown to find more information on a specific Group.  Use CTRL or Shift key to select multiple Groups.

Membership Filters
Select from the following options to find information on memberships in your community.
Expiration Date:  Select one of these options to find members whose memberships have recently expired or are about to expire.  Choose an exact day or a range from the dropdown. Enter the number of days for your selection.

• Membership did expire [exactly/between now and] __ days ago
• Membership will expire [exactly/between now and] __ days from now

Membership Levels: Use the Level filter to only include transactions for specific Membership Campaign Levels.  Use the Ctrl key to select multiple Levels for filtering.
Membership Status:Use the Membership status filter to only include data for a specific membership status.  Use the Ctrl key to select multiple values for filtering:

• Active - Membership is current.
• Lapsed (Expired) - Membership is in a lapsed state if the expiration date passes without the membership being auto renewed or the constituent repurchasing a membership.
• Canceled - Membership has been canceled.
• Past - Previous membership level. A membership level/transaction is designated as 'Past' after a member purchases another membership.

Auto-renewal Status: Filter the report by a specific auto-renewal status.

• Auto-renew membership - Members whose membership auto-renews.
• Do not auto-renew membership - Members whose membership is not set up to auto-renew.

 

Use a saved query

Select from the list of saved queries to use a previously created query. You can also modify a saved query before running it, or delete the query.

1.  Select Data Viewer.

2.  Select  Use a saved query.

3. The saved queries you can choose from will be displayed in a grid. Click  to run the query.

4. The Additional Actions that can be taken on the query results can be viewed here.

5. Pick the appropriate radio button for the action you want to take and click Go. Based upon the radio button you selected, you will be moved to the appropriate area.

NOTE:  Using a saved query to create a group
If a saved query is modified, then the membership of the Query-based Group will be adjusted in the nightly process to change the membership of the Group. This will be the same as if the member changed their data to no longer meet the query – the content they had posted previously to the Group would be remain ( except for Class Notes pulled in from their Profile) but they would no longer appear in the membership directory, receive emails, etc.

If the saved query is deleted, it does not affect / remove the Group – it just removes the Query from the Saved Query list. Everything set to use that Saved Query, including EM, Exports and Groups continue to function.  

 

Use a criteria template

Select from the list of previously created templates to load a criteria template. In the next step you will be asked to specify the criteria for your export. You can also delete the query.

For example, if you are searching for members from certain states, a criteria template for "Home State" allows you to specify different state(s) for your search each time you use it. (Criteria templates save the fields, but not the field values.)

1. Select Data Viewer.

2. Select Use a criteria template.

3. The criteria templates you can choose from will be displayed in a grid. Click  to select a criteria template.

4. Select the value(s) for the field(s) being used by the criteria template.  You also have the option to select additional fields.

5. Click Apply Filter.

6. A preview of your query results will be shown in a table at the bottom of the page. You can check the box in the Exclude column to exclude a member’s data from the report/export.

7. Click Next  when you are ready to proceed.

8. If you want to save the query, use the Save Query option.

9. The Save as Criteria Template option is selected by default. If you made changes to the Criteria Template (added fields, took fields out, etc.) and you want it to be a unique Criteria Template, you must uncheck the Update Query/Email Segment and give it a new name.

10. The Additional Actions that can be taken on the query results can be viewed here.

11. Pick the appropriate radio button for the action you want to take and click Go.  Based upon the radio button you selected, you will be moved to the appropriate area.

 

Include Privacy Protected Records

Only Super Admins and Privacy Protected Records Admins will see this checkbox that allows them to include Privacy Protected Records in their query.
If a query or criteria template containing Privacy Protected Records is saved, it will only appear in the saved query or criteria template grid for Super Admins and Privacy Protected Records Admins.

Non-exportable fields

Only Super Admins and Privacy Protected Records Admins will be able to utilize non-exportable fields.
If you are not a Super Admin or a Privacy Protected Records Admin you cannot build queries or create groups with a non-exportable field and the field will also be excluded from the “Select Field” area when building an Export or Report.

 

Large Exports

If your export is very large, you might receive this message after the Preview page:  Your file is being processed.  Please wait...
The system will time out after 60 seconds if your file is too large to be processed in that time and will give you this message and options:

Select Save and Schedule.
Enter the Export Name, select today’s date for the Start Date, and select One Time for the Frequency. (End Date is not applicable.) Your export will be run in overnight processing, and you will receive an email notifying you when it is ready.