Online Help

 Help Table of Contents

Working with Query Builder

Query Builder allows you to identify and use generated lists of contacts who meet certain criteria. Use query results for analysis or to create groups, phone lists, and mail merge lists. Groups created by query can be set to rebuild periodically so you always have a fresh set of contacts meeting your criteria.

Note: Some queries might be optimized for performance. This will typically happen after a query is run and its performance is observed by Convio administrators to be excessively slow. If you launch the SQL viewer to examine such a query, you will see a notice, in red letters, that your query is using SQL from a specified file. When you edit such a query, you will see a message in red at the top of the Query Builder advising you of the optimization and asking you to contact Convio support before editing the query. If you edit the query without contacting support, any optimization will be lost.

To access Query, select Query from the Data Management menu.

This section covers the following topics:

Creating a New Query

To create a new query:

  1. On the Data Management menu, select Query.
  2. On the Query List page, click Create a new Query.
  3. On the Query Info page, provide the following:
    • Name: Type a meaningful name for your query.
    • Description: Type a brief description.
    • Security Category: Select Administrators only, Convio administrators only, General, or Registered users only to limit access to your query by other administrators.
  4. Click Save.

Top of Page

Building a Query

Build your query on the Query Builder page by selecting the query criteria. Choose a single field or choose multiple fields and groups. As you build your query, consider the effects of selecting field types, fields, field clauses, field groups, and interaction clauses.

Querying on a Field Type

To add a field type to your query:

  1. Select a Field Type. Field Types include the following:
    • Address: Accepts Postal, City, Country
    • Biographical Information: Gender, Date of Birth, Marital Status, or Origin Source Code
    • CMS Web Site Information: First CMS Site Visit Date, Last CMS Comment date, or YTD CMS Site Visit Count
    • Education: Class, Degree, Major
    • Email: Accept Email, Email, Status, Opt-Out Date, Preferred Format
    • Employment: City, Employer, Occupation
    • Phone: Phone, Preferred Phone
    • Relationships: First Name, Last Name, Type
    • Significant Other: First Name, Suffix
    • System: Active Detail, Active Status, Constituent ID, Contact Range, Creation Time, Donor Status, Exchange ID, Last Login Time, Modification Time, Origin Application Type, Password Type, Security Category, User Name. For details, refer to Querying by the System Field Type.
    • Sustained Giving Information: Actual End Date, Card Expiration Date, Gift Plans Eligible for Processing. For details, refer to Querying by Recurring Gift Field Types.
    • Transaction Information: Engagement Factor, External YTD Gift Amount, Recency PCTL
  2. Select a Field specific to the selected field type.
  3. Click Add Field Clause.
  4. Specify the field clause information you are seeking such as "country equals United States" or "Zip Code equals 78740."
  5. Click Save to insert the field type, field, and field clause into your query.

Top of Page

Querying Different Groups

To add members (or non-members) of a group to your query:

  1. Click the Add Group Clause button.
  2. Choose whether to include contacts that are a member of any of, are a member of all of, are not a member of any of, or are not a member of all of the group or list of groups that you plan to select, using the menu.
  3. Locate one or more Groups that you would like to add to the list of Selected Groups:
    1. Click on the Group Type of interest to you. A list of groups or of group sub-types of that type will appear to the right.
    2. Click on the Group name or Group subtype that appears. This will add the Group to the Selected list, or open a list of Groups of that subtype to the right that you may select from.
    3. Repeat as necessary to add more Groups.

      Note: If necessary, you can also use the Search feature to further refine the list. The Search feature will honor any Group Type that is currently selected.
  4. To remove a Group from the list of Selected Groups, click on that Group's name in the Selected list.
  5. Click Save to place the group condition in your query.

Top of Page

Querying by Interaction

To add constituents to your query by the interactions they logged:

  1. Click Add an Interaction Clause.
  2. Choose whether to include constituent who has interaction of type or has no interaction of type for the interaction type you plan to select, using the menu.
  3. Locate the Interaction Type you would like to add from the list of all Interaction Types, as follows:

    Note: For details on Sustaining Gift interaction clauses, see Querying by Recurring Gift Interaction Clauses.
    1. Click Referenced Types, Default Types, or Custom Types in the Interaction Group pane. The list of possible Interaction Types associated with that Interaction Group appears on the right.
    2. Select an Interaction Type.
  4. If you selected a Referenced Type* of interaction, you will be presented with a list of Referenced Names (campaigns and other objects) associated with that type of interaction:
    1. (Optional) Filter the list of Referenced Names by Name and/or Security Category to reduce the size of the list. You can enter a partial name in the filter. (Note that a maximum of 500 named objects will display.)
    2. Select a Referenced Name (such as the name of a donation campaign) to select it for your query phrase. You can select multiple referenced objects of the same interaction type (for example, multiple donation campaigns or multiple alerts). Multiple objects will be added as separate lines in your query and must be edited separately.
  5. If you selected a Default Type or a Custom Type of Interaction, you can (optionally) specify an interaction subject in the With subject field. If specified, this tests for an exact, case-sensitive match to text in the interaction Subject field. (You can view the subjects of interactions on the Interactions tab in the Constituent Profile.)
  6. Click Save to add the interaction condition clause to your query.

Important: The Referenced Types of interactions are system-generated and considered part of the Default Types when viewed on the Interactions tab in the Constituent Profile. They are separated here so that you can easily select the referenced objects that you want to include in your query phrase. Read more about Referenced Interaction types in Understanding Referenced Interaction Types in Query.

Top of Page

Using the Query Tools

Use the tools on the left to clarify or revise your query:

  • And/Or: All criteria statements are added as OR criteria.  Select a second statement and click AND to make both criteria required.
  • Parentheses: Complex queries might require that you use parentheses. Things in parentheses are evaluated first by the database; for example:
    • Class equals 1964
    • AND Major equals biology
    • AND (Home State equals WY
    • OR Home State equals IL)

      • In this example, the Home State condition will be evaluated before Class and Major are evaluated.

      To use the parentheses, click the line where you want to start the parentheses and click the left parenthesis tool. Repeat for the right parenthesis.

  • Change: Select a line and click Change to reopen the dialog box where you chose the specific condition (such as the specific state).
  • Remove: Select a line (a condition) you want to remove and click Remove.

Top of Page

Getting the Results You Want from a Query

The following examples might help you construct a query.

  • Simple statements
    • Query: Biographical First Name equals "Chris"

      This query states: “If someone’s first name is Chris, they belong in the result.”  “Christopher,” “Christine,” or “Christian” do not apply.
    • Query: Biographical First Name begins with "Chris"

      This query returns anybody with a first name that starts with Chris, including the ones excluded earlier.
    • Query: Not a member of the <whatever> group

      This query returns everyone not in a particular group.
  • How "AND" works:"AND" says that both statements on either side must be true to consider the record for inclusion in the result.
    • Query: Home State/Prov equals WV
      AND Home State/Prov equals UT

      This query says: "If someone lives in West Virginia AND Utah, they belong in the result."  Since it is not possible to mark two states for the Home Address in the database, this always returns nothing!
    • Query: Home State/Prov equals New York
      AND Home City equals Albany

      This query says: "If someone lives in the state of New York and in the city of Albany, they belong in the result."  This might seem redundant until you realize there is an Albany, GA and an Albany, CA, (and perhaps many more).
  • How "OR" works: "OR" says that only one statement on either side must be true to include the record in the result.
    • Query: Biographical First Name begins with B
      OR Home State equals MA

      This query returns Barb in Nebraska, Bill in Texas, and everyone (including Ben, Dave, and Jill) in Massachusetts.
  • How parentheses ( ) work:  Parentheses make clear to the database in what order to evaluate things.  Conditions in parentheses are evaluated first.
    • Query: Home City equals Albany
      OR Home Zip equals 23561  
      OR Home City equals Omaha
      AND Home State/Prov equals NY

      The results of this query may surprise you. The query returns the following: "Everyone in the 23561 zip code, everyone in any city named Albany, and everyone in Omaha, New York." This is true because the database interprets AND before OR, so it does the last connection first. This can be avoided by using parentheses in the next example.
    • Query: (Home City equals Albany
      OR Home Zip equals 23561
      OR Home City equals Omaha)
      AND Home State/Prov equals NY

      Now the query returns the following results: "Everyone who lives in New York AND also lives in Albany, Omaha, or the 23561 zip code.

Top of Page

Saving and Running a Query

To save and run a query:

  1. Click Save Query.

    Note: If you opened an existing query and made changes, you can choose Save Query As to save the modifications under a new name.
  2. Click Run Query.  The query results appear on a new Results page.

Top of Page

Using Query Results

You can use query results to create a group or a mail-merge list. If you are using the Clubs application, you can also add query results to the Club Owners or Club Members groups.

Using Query Results to Create a Group

To use the query results to create a group:

  1. Choose the Use Query link on the query Results screen.
  2. In the dialog box that opens, click the radio button for Create a Group and then click Go.
  3. On the New Group Information screen, observe and/or make changes to the following:
    • Group Name - by default the group name is the query name
    • Description - by default the description is also the same as the query description
    • Security Mode - determines the access that members of the group will have to the site
    • Group Type - choose from the drop-down menu or click the second radio button and enter a new type; this represents the group folder where the results will be stored
    • Scheduling Information - if you would like to have this group periodically rebuilt using the query, then check “Periodically rebuild group membership” and click the radio button for the desired schedule.

      Important: Manual changes to a rebuilding group (or changes made by Data Sync) will be eliminated the next time the group is built by the query if those contacts do not meet the query criteria.
  4. Click Save.  The Group Information screen should now reflect the new Group ID and the number of users in this group.

Note: The new group will be identified as a query generated group on your Group List. See "Group Management" for more on working with groups.

Top of Page

Using Query Results to Create a Mail-Merge List

A query can also be used to create a downloadable CSV file which can be used by Microsoft Word® for Mail-Merge.  The file created using this procedure is also a useful means of exporting data for other purposes including analysis, since you can choose to include only certain fields in the CSV file.

To use query results to create a Mail-Merge List (external CSV file):

  1. Choose the Use Query link on the query Results screen.
  2. In the dialog box that opens, click the radio button for Create a Mail-Merge list and then click Go.
  3. On the New Mailmerge screen, observe and/or make changes to the following:
    • Mailmerge Name - provide a name for your file
    • Author - by default will be the name of the Administrator who is creating the mail-merge list
    • Description - provide a description of the file
    • Security Category - choose from the drop-down menu; your choice may limit access to the mail-merge list by other administrators
  4. Select Log mailmerge as interaction for constituents if you would like to enable this feature.
  5. Select the fields to include in the mailmerge by highlighting the fields on the left of your screen and clicking Add Selected. (Use Ctrl + Click to select multiple fields.)  Use the Remove Selected button to move any unwanted choices back to the left of your screen.
  6. Click Next Step.
  7. Click Confirm. The new list should now appear on the Mailmerge List page.
  8. Click the Refresh button to update the Status of the list. When the Status indicates that the report has been Generated, you can download the mailmerge list (csv file) using the download icon icon.

Top of Page