Creating a Database Query

You can download quick reference information for Creating a Query by clicking on the icon

Cheatsheet

Creating the query requires some planning and foresight. To start, you must decide what criteria you want the patrons to meet. If you are looking for very specific patrons, you must know what makes them unique from the rest. You may also narrow down your search by removing certain data from a returned query. Click here for information on complex criteria.

To create a Query, you would perform the following steps:

1. Open the Mail List - List window.

   

   For more information on accessing the Mail List window, click here.

2. Click the New button.

   The Mail List Criteria (inserting) window opens.

   

3. In the Name field, enter a short name for the Mail List.
4. Make a selection for the names to be included on the list.
5. In the Description tab, select the appropriate flags and in the description field, enter a detailed description for the Mail List.

   This is extremely important for other users who may use the mail list. If the description of the list is good it will not need to be deciphered to understand the reason for the list.

   

6. Click the Save button.

You can now enter the Criteria.

7. Click the Criteria Groups Tab.

   

8. Click the Create Group button.

   

9. Choose To Add Patrons To The Mail List.

   The Mail List Data Selection window opens. To search for data, you must select a file for the primary source of information for the mail list. Theatre Manager will include information in related parent files.

   Patron Data is the PARENT for most data stored in Theatre Manager. When defining criteria, if you select any file as the main file, you can also set criteria for the patron record. However, the converse is NOT true. Selecting the patron file as the Merge File, it does not include the child information - such as donor inormation.

   

   Select a file that contains the data you are searching for. As the example list is searching for patrons who have made donations; the file to use would be "Patron Address Link". Click here for more information on selecting the file.

10. Select the "Patron Address Link" file..
11. Click the Next button.

    The Parameter window opens.

    Click here for more information on this window. This tab displays the added criteria for the query on the database.

    

12. Click the New button.

    The Add/Edit Parameter window opens.

    

    The list contains all the possible searchable fields available in your selected file. The selections on the top left side of the window (and, or, not) are used for searching multiple fields when more then one line of criteria is added within the same file. Click Here for an overview of conditions and search criteria.

    

13. Select the field to search.

    In this example "Patron Addresses>>Address Line 1", will be the selected search field criteria.

14. Select a condition, in this example "is not empty".

    The condition displayed above will find any patron has aan entry in the street address section of the Patron Record.

15. Click either:
    1. Add / Continue if adding another search criteria to the file.

       The Criteria window remains open until save or cancel is clicked.

    2. Save if the search criteria for this file is complete.

       The Parameter window will return and the entered criteria will appear in the list.

    3. Under Exclude Records, enable the "Don't Mail-Theatre Requested" and the "Don't Mail-Patron Requested" flags, and click the Done button.

       

       Now the group and its criteria have been added to the main query. For this example, another group needs to be added to include the patrons who have email addresses.

       

    4. Click the Create Group button.
    5. Choose To Add Patrons To The Mail List.

       Choose a file containing the required data. This example requires the "Patron Contact Link" file.

    6. Click the Next button.
    7. Click the New button in the Parameters window.

       In the Edit/Add Parameters window, leave the And button selected. The field for this example would be "Patron Contact Data>>Contact Data (eg Phone/email/website)" while the condition would be "contains" and parameter would be "@" (to search for an email address).

       

    8. Click the Save button.

       

    You are returned to the Parameters window.

    9. Click the Done button.

       

       Both of the Search Criteria are added to the query. The group criteria says add any patron who has volunteered their time as well as any patron who has made a donation to the list.

       Now, all the criteria have been added to satisfy the description, the database can be searched.

    If you have criteria that you'd like to print and save, while on the tab, click the PRINT button at the top of the window. You will get a report that you can then print or save to PDF. This can be handy when you: Want to keep a printed format of your Mail List criteria Want to share your criteria with others in the office who may not use Theatre Manager Want to email the precise criteria you have been working on to support@artsman.com to ask for help. Emailing a PDF is preferred as it shows all of the criteria, the Description (so we know what you are trying to accomplish), the conjunctions (AND, OR, NOT) and operators (is between, is greater than, is equal to, etc.) used in the criteria. This is the preferred method for Support to receive your criteria help request.

    10. Click the Execute button to begin the Query.

        The patrons satisfying the first criteria will be found and added to the list. Once complete, the second criteria will be performed and any patrons matching the second criteria will be added. Any patron matching both criteria will only be displayed once. The Mail List will default to the Who's In tab displaying all patrons who match the criteria and are in the list.