Parameters for Mail Lists Window

Parts of the Criteria for Group from Mailing List Window

Default search or sort order for the selected Parameters.
Inserts the last used criteria for the merge field selected.
Displays saved criteria options within the selected merge field.
Saves selected criteria.
Saves the entered criteria to be used again.
Opens the criteria window to insert new criteria.
Opens the criteria window and allow for modifications.
Removes the selected line of criteria.
Removes all lines of criteria in the parameters window.
Inserts brackets around lines of criteria.
Removes brackets around lines of criteria.
Searches the database for patrons who "have" a specified line of criteria. Having criteria is criteria that is based on aggrigate values within the search. Click here for more information on using Having.
Cancels the entry of criteria.
Completes the entry of criteria.

Having Criteria

The Having button allows you to add the having criteria to the search. Having criteria is criteria based on aggrigate values within the search. Examples are:
  • find donors HAVING a total of all donations over xxx
  • find ticket buyers having purchased to multiple distinct events for the current season
  • find subscribers that have a subscriber package set up for all of the last 10 years
  • find gift certificate purchasers with an average gift certificate purchase over $25
  • find auction item buyers with a maximum purchase over $1000 for all items bought
Having is an SQL action that re-examines records for an aggregate (average, minimum, maximum, sum, count, distinct count) based on the record found and then keeps or discards the data.

For example: if you used criteria to find all subscriber records in the past 5 years, you would get 1, 2 ,3 4 or 5 records - depending on how many years the person has been a subscriber. Since we know each subscription package has a year associated with it, if you make a having criteria that is having a unique count of subscription year >= 4, you would find people who were 4 or 5 year subscribers.

To create the query, you need two things:

  • A normal query that finds all the records you want to aggregate in some way. eg: tickets with date sold since 2004 and price paid > 0
  • An additional having clause that uses one field for the aggregation. eg
    • sum price paid >500 -or-
    • count ticket #> 50 -or-
    • distinct count event # > 4

  1. Open the Mail List you want to add the criteria to.

    The Mail List Criteria window opens.

  2. Click the Criteria Groups tab.

  3. Click the create Group button.

  4. Make your selection to Add or Remove Patrons from the listing.

    The Mail List dialog opens.

  5. Select the file for the primary source of information for your list. Then click the Next button.

    The Parameters window opens.

  6. Click the Having button.

    The Edit Report Parameters window opens.

    Note the criteria for selection on the left side of the window gives yous different options to choose from.

  7. Make your selections for the Criteria, and when complete, click the Save button.

    You are returned to the Parameters window and your criteria selection appears in the search.

  8. Click the Done button.

    You are returned to the Mail List window.

  9. Click the Execute to execute your search.

Having - Patrons with count of tickets

The sample below shows the basis for finding patrons who have purchased more than a specified number of tickets.

The query is based on ticket data for a patron and you can add other criteria such as:

  • Only specific shows in the past
  • A certain fiscal year, season or range of seasons
  • People who have donated money in the past and are large purchasers
  • patrons who paid for their tickets (eg total cost > 0)

The example below shows the user of Ticket Quantity from the ticket data per performance in the having clause to obtain people who bought more than 20 tickets (in all time). The first line of criteria was added to make sure that they were Paid tickets and not comps.