Database queries are used to pull information in mail lists, reports, and data exports. While there are some minor differences between these three data format/output types, the criteria-building process is essentially the same. If you are new to building queries, we recommend starting by reading this page in its entirety, including reviewing the sample Mail List queries, before getting started with how to build a basic query in a mail list.

Data Files   Top

Close

Data File Funnel

The most important step in building criteria for a mail list or data export or for creating letters is the selection of the correct data file because it tells Theatre Manager where to look to retrieve the data.

Much like traditional paper files, a database file is a collection or subset of related information (or fields). Theatre Manager contains many data files and connects them via a relationship hierarchy. This relationship hierarchy defines how the different data files relate to each other.

Data files and their structured relationships are how Theatre Manager finds data to display, regardless of the final output/format you want. As an illustration, if you save criteria in a Mail List using the data file Ticket Sales Data, then you can pull this saved criteria from any report or data export attached to that data file.

When you select data file X, you are able to access all data in file X as well as all the data in X's parent files. Parent files are data files both related to X and structured ABOVE it in the database relationship hierarchy.

This is a mouthful, so what does it mean?

The clearest illustration is probably Patron Data. This data file contains information like first name, last name, company, etc. Patron Data is the parent file for most data stored in Theatre Manager. Any data file you select when defining criteria will also include information from Patron Data. However, the converse is NOT true. Selecting Patron Data for your criteria building choice will NOT include any child information (e.g. donation information, ticket information, volunteer activity, etc.).

Close

Data File Selection Window

Below are some examples. (The terms letter, export, and mail list can be used interchangeably in these examples because the process is the same for all three.) Note that in every example the file selected represents the actual data you want to see, but you end up with access to the patron record as well.

• If you want a Letter to contain information about specific donations, then you select Donation Data For A Patron With Each Gift. This data file corresponds to the information you see on an open Donation Detail Window. Choosing this file means you can put the donation date and amount on the letter. You can also include any data related to the donation, including:
  • Campaign/fund setup (e.g. campaign type, target, etc.)
  • Patron record (e.g. name, company, primary address, etc.)
  • Marketing profile (e.g. total donations, do-not-email flags, etc.)
  • Order details (e.g. fees, sales person, etc.)
• If you want to export information about sold gift certificates, then you pick Pass/G.C. Data (your venue may have a slightly different name for this file). This choice gives you access to fields from the Pass Detail Window and also lets you search for and export related information such as:
  • Patron record (e.g. name, company, primary address, etc.)
  • Order details (e.g. fees, sales person, etc.)
  • Marketing profile (e.g. total donations, do-not-email flags, etc.)
  • Pass/GC setup (e.g. pass type, valid events, etc.)
• If you want a Mail List of patrons that came to specific events, you need to check their ticket attended status, just like scanning paper ticket stubs. So, you select the data file Ticket Data for a Patron in order to access the attended status field along with other ticket information like price and promotion. In so doing, you can also add criteria from:
  • Event setup (e.g. venue, season, etc.)
  • Performance setup (e.g. date, time, etc.)
  • Patron record (e.g. name, company, primary address, etc.)
  • Marketing profile (e.g. total donations, do-not-email flags, etc.)
• If you wish to access the schedule for a volunteer/personnel member, you use the file Volunteer Activity History for a Patron. This lets you see when you have scheduled them, along with information from:
  • Volunteer Activity (e.g. position, status, etc.)
  • Marketing profile (e.g. total donations, do-not-email flags, etc.)
  • Patron record (e.g. name, company, primary address, etc.)

Close

Starred Marketing Profile Fields

You may have noticed that the marketing profile was available in all of the above examples along with the patron record. Since this file can be accessed from so many other data files, Theatre Manager includes several fields designed to help you reference key information that lives in a different data file than your main one. The field **Marketing Patron Belongs to Mail List - Is Subscribed To is a very commonly used and powerful choice in criteria because you can build a Mail List of patrons based on information in data file X and then pull information in data file Y related to only those patrons. This functionality comes from the fact that this field lives in marketing profile, a parent file for almost all other data files and thus one available in all queries.

This file selection step is not necessary when running reports because each report has pre-defined access to relevant data files. As an illustration, one way to see which reports are attached to the same data file is by using the run similar button.

Ready to get started? We recommend starting with a criteria-based mail list

Grouping Criteria   Top

When building complex criteria, grouping is critical to getting accurate results. Whenever you use an Or or Not operator to connect multiple lines of criteria, Theatre Manager will do its best to automatically set appropriate grouping. Depending on how you set up your criteria, however, you may need to manually adjust the grouping. Grouping is visually indicated on the Criteria Window with parenthesis. The parenthesis are the database query equivalent of using parenthesis in a mathematical order of operations. Criteria lines that are collected together within a set of parenthesis are evaluated together. For example:

Event genre is holiday or (performance date is between 12/1/22 and 12/31/22 and ticket total is greater than 0)

will pull different results than

(Event genre is holiday or performance date is between 12/1/22 and 12/31/22) and ticket total is greater than 0

The first example pulls holiday show tickets and paid tickets from December 2022 performances. The second example pulls paid tickets from holiday shows or any performance in December 2022.

Grouping allows you to build complicated searches, especially when nested together. Like with parenthesis in a mathematical order of operations, Theatre Manager will evaluate groupings from the inside out.

Mail Lists   Top

Close

Mail List Lookup Window

Mail lists are a group of patrons who did something in common, such as:

• Bought a ticket to a specific event
• Made a donation to a specific campaign
• Bought a ticket AND made a donation
• Bought "X" out of "Y" events (potential subscribers)

Mail lists can be used for external or internal purposes. Externally, they provide the ability to target the market to a group of patrons. To illustrate, a "musicals" Mail List could be created for all patrons who like musical productions. Use this list to send personalized letters, print labels, create reports or ask for donations. Mail lists can be manipulated in many ways. For example, you could use the "musicals" Mail List to create another Mail List that includes everyone on the musicals Mail List but excludes people who have purchased tickets to the current musical.

Internally, they may be used to create a list of donors over a specified amount, a list of ushers or actors, or donors to a specific campaign. These are listed for internal purposes to be used by the staff and may (or may not) be used for some kind of mailing.

It is important to remember that a Mail List provides "A List of Patrons Who..." For example, if you are creating a list of donors of more than $500, the list won't provide you with the donation amounts. It only provides you with the patrons who fit the criteria. Likewise, a Mail List of patrons who attended 4 out of 6 shows will not tell you what shows they attended, only that these patrons attended that number of shows. This information is obtainable by using reports, but mail lists will only provide the patrons who fit the criteria.

As a rule of thumb, if your question is about WHO, such as I want to know who..., then your answer lies in a mail list. If your question is about WHAT or WHEN, such as I want to know what someone gave..., then your answer lies in a report.

Mail lists present the following information about who meets the criteria:

• Patron Number
• Patron Type (e.g. Individual, Company, School, etc.)
• First Name
• Last Name
• Formal Name
• Company
• Title
• Source (how they were added to the list)
• Primary Phone
• Primary Fax
• Primary Email
• Website
• Address Line 1
• Address Line 2
• City
• State/Province
• Zip/Postal Code
• Country
• Other values drawn from the Marketing Tab of the Patron Record.

There are also some very interesting things you can do with mail lists such as:

• Print customized mailing labels
• Obtain a Google map of the patron addresses
• Use the Mail List as criteria in a Report
• Merge a Mail List with a Letter

There are 7 different ways to add a patron to a mail list. Mail lists also have a couple of unique database query options (removal criteria and having) options that are important to review before

Remove Group   Top

Mail lists have a couple of unique query options that are very useful in building complex queries, specifically the having feature and the ability to create removal criteria blocks. This section is about using removal criteria (or thinking in venn diagrams).

Close

Mail List Venn Diagram Logic

A Venn diagram is a great way to illustrate criteria for more complex mail lists. Most people find building criteria for a simple Mail List fairly straight forward. For example:

• find all patrons who donated money this year (pink circle)
• final all patrons who bought tickets last year (blue circle)
• find all patrons who donated money any year before this year (blue circle)
• find all patrons who used a gift certificate in the past three years (blue circle)
• find all patrons who are current volunteers (pink circle)
• etc

These examples need exactly one Mail List with one Add criteria block because Theatre Manager can check all records to determine if each one meets or doesn't meet the criteria. In a Venn diagram, these examples are the equivalent of finding patrons who belong within one circle/set, regardless if they are also in another circle/set. One Add criteria block is used to find patrons within one circle/set.

Complex Mail List queries require checking each patron's inclusion status in multiple circles/sets. Theatre Manager needs to consider two (or more) disparate sets and find the difference or overlap. There are four query combinations that require the use of multiple mail lists and/or the use of both Add and Remove criteria blocks:

1. Patrons in Blue but not in Pink
   • In the diagram example, this represents looking for patrons who did something last year but not this year. This is equivalent to LYBUNT or SYBUNT searches for "lost" or "lapsed" patrons (subscribers, donors, volunteers, etc.). See the LYBUNT sample mail list.
2. Patrons in Pink but not in Blue
   • In the diagram example, this represents looking for patrons who did something this year but not in last or prior years. This is equivalent to searching for "new" patrons (subscribers, donors, volunteers, etc.).
3. Patrons in both Blue and Pink
   • In the diagram example, this represents looking for patrons who did something both years. This is equivalent to searching for "repeat" patrons (subscribers, donors, volunteers, etc.).
4. Patrons in neither Blue nor Pink
   • In the diagram example, this represents looking for patrons who did not do something in either year. This is equivalent to searching for "inactive" patrons across both years. It's also useful for finding patron accounts who have not participated in desired engagement activities (e.g. subscribers who aren't donors or volunteers, donors who haven't recently subscribed or recently opened an email, patron records without ticket or pass purchases, etc.).

Below are some illustrations of the four multi-set scenarios. As you read them, remember:

• Each Add or Remove criteria block searches within one circle/set.
• Criteria blocks in the same Mail List do not talk to each other.
• Using multiple criteria blocks in the same Mail List tells Theatre Manager to compare members of circles/sets ALA a Venn diagram.

1. Patrons in Blue but not in Pink
These are patrons in one circle/set, but not the other. There are three common ways to accomplish this:

• Option 1: One Mail List containing both:
  • An Add criteria block
  • A Remove criteria block
• Option 2: Three mail lists:
  • One Mail List that identifies set A of patrons
  • One Mail List that identifies set B of patrons
  • One Mail List that refers to the set A and set B mail lists
• Option 3: Two mail lists:
  • One Mail List that identifies set A of patrons
  • One Mail List that identifies set B of patrons and refers to the set A Mail List (probably with a Not operator)

Close

Blue But Not Pink: Example 1

This LYBUNT Mail List shows an example of option 1 with one Mail List containing multiple criteria blocks:

• An Add block that finds patrons with tickets to season 2019 events
• A Remove block that then eliminates those patrons who have a ticket for the 2020 season

The criteria in each block can be multiple lines long to be as restrictive as you wish. The important thing is that each group represents a different circle/set of patrons that must first be found before they can be compared.

---

Close

Blue But Not Pink: Example 2

This LYBUNT Mail List shows an example of option 2 with three mail lists. It is similar to the above example with one Mail List since it uses both an Add and a Remove block. The criteria within each block just happens to refer to previously built mail lists that identify the circles/sets of patrons.

---

2. Patrons in Pink but not in Blue
This is similar to the above case for "last year but not this". The criteria are simply reversed to "this year but not last". In both cases you are taking one circle/set of patrons and removing the second circle/set of patrons. Again, there are three common ways to accomplish this:

• Option 1: One Mail List containing both:
  • An Add criteria block
  • A Remove criteria block
• Option 2: Three mail lists:
  • One Mail List that identifies set A of patrons
  • One Mail List that identifies set B of patrons
  • One Mail List that refers to the set A and set B mail lists
• Option 3: Two mail lists:
  • One Mail List that identifies set A of patrons
  • One Mail List that identifies set B of patrons and refers to the set A Mail List (probably with a Not operator)

Close

Pink But Not Blue Example

This "new patron" Mail List example shows the use of option 2 with three mail lists to find the difference between two circles/sets of people.

---

3. Patrons in both Blue and Pink

Close

Both Blue and Pink Example

We call something that must be in two circles an intersection set. To find intersection set patrons, you need to build three mail lists:

• One list representing circle/set A
  • the "last year" Mail List in this example
• One Mail List representing circle/set B
  • the "this year" Mail List in this example
• One Mail List that uses the is all of condition to find people in both the set A list and the set B list.

---

4. Patrons in neither Blue nor Pink Finding people who do not belong to either circle/set is a little tricky. You need to find the group of everybody you are considering (e.g. all donors from all time, everyone in the database, anyone who has purchased a ticket in the last five years, etc.), then remove circle/set A patrons, and then remove circle/set B patrons. There are three common approaches to this problem:

• Option 1: One Mail List containing the following:
  • an Add criteria block identify everybody
  • a Remove criteria block
  • a second (different) Remove criteria block
• Option 2: Four mail lists:
  • One Mail List containing everybody
  • One Mail List that identifies patrons in set A
  • One Mail List that identifies patrons in set B
  • One Mail List with both:
    • an Add block that refers to the first list containing all patron records
    • a Remove block that refers to the second and third lists containing sets A and B
• Option 3: This approach is useful if the set of everybody truly means everybody in the database. It requires three mail lists:
  • One Mail List that identifies patrons in set A
  • One Mail List that identifies patrons in set B
  • One Mail List with an Add block that includes the Not operator when referencing the first lists of patrons in sets A and B (this negates the search)

Close

Neither Blue nor Pink: Example 1

This example shows a combination of option 1 and 3 with and Add block that pulls all patron records in the database and a Removal block that eliminates patrons who belong in set A or set B list. The *is all of * condition means Theatre Manager removes patrons who are on both prebuilt lists (or all people who are not in last year or this year).

---

Close

Neither Blue nor Pink: Example 2

This example uses the option 1 approach. Everybody is defined as subscribers who are part of the control house for dinner theatre. The Mail List then removes all donors. Finally, in a second Removal block, it removes all volunteers. The result is subscribers who have never donated or volunteered.

---

Close

Neither Blue nor Pink: Example 3

This example uses option 3 to look for patrons who do not belong to two prebuilt lists. In this case, everybody is truly everyone in the database. This definition of everybody is implied because we selected the data file Patron Data and Theatre Manager scans for all data in the file unless explicitly told not to. The criteria will tell Theatre Manager to only include people in none of the specified mail lists.

---

Having   Top

Mail lists have a couple of unique query options that are very useful in building complex queries, specifically the having feature and the ability to create removal criteria blocks. This section is about the having feature, an advanced criteria building option only available in mail lists.

The Having button allows you to add the having criteria to the search. Having criteria is criteria based on aggregate 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 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 a having query, you need two things:

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

Here's two examples to help clarify.

Close

Having Example 1: Donors Having Given in More Than XX Years

The following walks through how to find people who have given between XX times in the past number of years or more than XX times in the past number of years. The trick is that you need a combination of normal criteria to limit the donations you are looking for AND a having clause to count what you have found and eliminate those that don't meet the count.

The normal criteria is optional. If you don't specify anything, you will retrieve all donations. Some things you might want to include in criteria are one or more of:

• criteria to eliminate soft credits, prospects, and hard pledges which are not real money (unless you want to count those amounts towards the patrons giving)
• donation date range if you only want to look at a range of years, or only every second year, or only leap years, or anything you want
• donation campaigns if you want to limit search to certain campaigns
• donation actual amount if you want to limit search to individual donations between a certain range, over a certain range, or anything you want
• belongs to mail list if you want to find subscribers in your list, or volunteers, or people who bought giver certificates, or people who returned tickets for donations, etc

In short, you can limit the donations selected to ANY criteria you could enter.

The HAVING clause re-examines the set of data is found using the normal criteria. It then imposes a further restriction on the aggregate set of data. Examples could be:

• the data must have a UNIQUE number of fiscal years donated >=2 per patron (as in the example above)
• the total count of donations must be >6
• the average donation across the number of years selected must be between x and y
• the distinct number of campaigns given to must between x and y
• and much more

---

Close

Having Example 2: Patrons Having an XX Count of Tickets

This sample shows the basis for finding patrons who have purchased more than a specified number of tickets. The query is based on the data file ticket data for a patron. 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 (e.g. total cost > 0)

This example shows the use of ticket quantity from the data file ticket data per performance in the having clause to obtain people who bought more than 20 tickets (across all time). The first line of criteria was added to make sure that they were paid tickets and not comps.

---

Double Opt-In   Top

CASL (Canada's Anti-Spam Law) has 3 basic mechanisms to provide permission to email your patrons:

• Explicit Consent, which is implemented using a Mail List that is set up as either single opt-in or double opt-in. Double opt-in is the preferred choice if you wish to have the patron explicitly confirm their email address and/or permission to be emailed.
• Implied Consent, which occurs if a client initiated activity with you (any sales or phone contact) where you have up to two years to contact them via email without asking their permission.

Close

Email Signups Online

What does an Explicit Consent Mail List look like on the Web for the end user? When one or more email notification lists are set up to allow people to sign in, they will look like this on the patrons account page. (They also show on the mail lists window).

Randomized Mail List   Top

There are situations where we don't want everybody in the mail list, only a random selection of them. This could be used for things like:

• A lottery based on-sale
  • Multiple randomized mail lists can be used to fairly distribute on-sale time slots to patrons via different coupon/pre-access codes.
• Marketing research or promotional events
  • Generating a random subset of qualifying people in a larger Mail List can help provide a representative sample for focus groups, surveys, or event invitations to limited-capacity events like a press release or promotional giveaway.
• Snail mail promotional material
  • Use a randomized subset of a larger Mail List when a flier/brochure/postcard mailing budget requires a tighter distribution list.
• Generating A/B testing environments
  • Easily test a few different eblast or letter messages/formats to see what strategy is most effective by identifying a group of similar patrons and breaking them into randomized subsets.

Regardless of your use case scenario, a randomized Mail List refers to how a Mail List is initially created. Once it's built, you can do all the same things you would do in a non-randomized mail list, such as:

• Add/remove more people manually or via criteria
• Merge data from the list with an eblast or printed letter
• Run reports to see activity for patrons on the list
• Attach the list to a pre-sale or discount coupon code

Since patrons initially added to a randomized Mail List are not represented by a criteria block, it is especially helpful to include the word "random" in the Mail List Name and Mail List Description for future reference. Once you are done using your randomized lists, we also recommend deleting these lists to keep your records clean.

Reports   Top

Reports allow you to examine the information stored in Theatre Manager's database in a format relevant to your requirements. Reports provide critical information to support day-to-day activities and strategic information for planning purposes. Theatre Manager has nearly 250 different standard reports you can generate. Each of the reports employ multiple format, search, sort, and output options to ensure that the reports generated are as effective as you need them to be. Reports always print to the screen first for viewing. From there you can print, PDF or export the reports for use in outside software programs.

It is important to remember that if your question is about WHO, such as I want to know who..., then your answer lies in a mail list. If your question is about WHAT or WHEN, such as I want to know what someone gave..., then your answer lies in a report. Review some sample reports and then learn how to build reports yourself.

Types of Reports   Top

[Report Categories include:

• Attendance for House and Calendar
• Box Office Statistics
• Calendar and Facility Management
• Course Reports
• Donation Reports & Receipts
• Exporting, Labels & Record Counts
• G/L Report
• Mail List Reports
• Pass, Membership and Gift Certificate Reports
• Miscellaneous Reports
• Order & Payments
• Patron Reports
• Sales Summary Analysis
• Season Subscription and Subscription Packages
• Ticket Printing, invoicing & Inventory Analysis
• Transaction History Reports
• Utility Functions and Database Analysis
• Volunteer and Volunteer History

In addition, customized reports can be created using the Export Data function. The possibilities are limitless.

PDF Generator in Reports   Top

The Portable Document Format (PDF) Generator is maintained by Brainy Data. The PDF Generator allows for any report or letter in Theatre Manager to be created as a PDF document without the assistance of additional software. PDF documents are a better format for emailing documents to another person. In simplest terms, a PDF document looks exactly as a document that has been printed when opened. This allows for transferring documents in their printed format to other users via email or over a network. These documents cannot be edited with a PDF Editor.

Queued Reports   Top

Background reporting is when you ask Theatre Manager to run a report and instead of running it on your workstation, you send it to the queue. So there are two ways to run a report:

1. Right away on your machine
   • causes you to wait till the report is done
   • runs on your local workstation
2. In the background
   • lets you continue to work until the report is finished
   • lets you schedule a report to be run automatically in the future
   • runs on the first available web listener setup for background reporting and is stored in the queue when ready

Here are some good use case scenarios doe running reports in the background:

• Year-end financial reports used during the year-end rollover process
• Month-end financial reports
• Daily sales/donation reports
• Large reports that will take a long time to pull

Queued Report Setup Requirements   Top

There are three steps required to enable the reporting queue:

1. Enable the background reporting option for the entire system.
   • This must be set before enabling background reporters in listeners (see next bullet point).
   • If you have reporters enabled, and you uncheck this feature, any background reporters will stop accepting requests.
2. Decide which TM web servers will host one or more background reporters and enable the feature.
   • You may want to dedicate a single machine to running reports, since this activity consumes some CPU that could be used for serving web pages. In other words, if you enable reporters on your main web server, it will result in slower response for online purchasing if many reports are being run.
3. Enable the background reporting option for each employee who wants to use the feature.

REST API   Top

Close

REST API Example

The REST API feature allows access to the database fields using a URL. This can be typed directly into a web browser or use in a program. Typically, this feature might be used by:

• web developers to augment your main marketing website and pull data from theatre manager. This data could be used to show upcoming events (e.g. icons, text, dates, times, on sale status, etc.)
• selected employees to pull specific data or aggregated date for analysis
• producers or other external companies that are enabled to see some data for their events.

The data can be delivered in a number of formats to provide more controlled access to data. However, it is possible to provide direct read-only SQL access, for example to be used on a marketing website to push the user over to the bootstrap based commerce pages provided by Arts Management.

API Login Requirements   Top

Close

Employee API Setting

In order to gain access to the rest API, the following requirements must be met:

1. At least one web sales listener enabled in the Director.

• The web listener is the process that responds to the API request.
• Web sales are not required to be enabled to the outside world - but if you do online tickets sales, you need to be aware that extensive use may require additional web listener processes.

2. The person needing REST API access must be set up as an employee in the database.

• and have the checkbox REST API: enable access via url on the employee access tab
• the employee does not necessarily have to be able to log into Theatre Manager desktop, but they:
• must have an employee record
• must not be resigned

Close

Employee Permissions & API Endpoints

3. The employee record must have permissions in the Employee Data Tab to indicate which data that record can access.

• They can have any user level from No Access to Normal Access, or you can set the user to API Only, which means they cannot log into TM but can use the REST API.
• Just having access to the rest api does not mean you can see the database. The permissions set for the employee in the Data Tab in employee preferences limit the data which the employee can see in the API endpoints.

4. The employee record must have logged into the online web sales site and created an account, which requires:

• at least one email address associated with their patron account (multiple emails for an employee are ok)
• and a password that allows them log into the web store

5. A workstation, browser, or toolset that is capable of connecting using TLS 1.2 or later for security. If you can't connect, try latest Firefox, Chrome and/or on Windows 10 or some smartphone.

6. (optional) Consider requiring complex passwords via a setting in system preferences, which will also affect your regular patrons as well.

   • The Ticketing Site password is different from the employee's Theatre Manager login account since they can set it themselves online. Since most people will not have the API enabled for their employee account, the complex password requirements would affect only a few employees.

Top Level API Endpoints   Top

The initial REST API login window shows a list of the endpoints -- which is a URL that:

• will give you specific information that relates to the link. eg. there is a patron end point that provides information about the patron and a ticket end point that focuses on ticket information
• can be modified to only select specific columns
• can have a sort order provided and data will return in that order
• can have a query provided to only select data that you want
• can also show some associated data (e.g. ticket date can show the patron, event and performance it is for

The endpoints are shown in this manner so that they are discoverable. This means you can use the links to follow any one of them and see what kind of information it provides and can discover where it will lead. Review the key columns of Artsman's REST API.

API Output Formats   Top

Close

REST API Extensions

The default output format for the REST API is in a human-readable and navigable html format. That is not always the easiest for a web developer to deal with in a program, so the API offers a number of output formats that can be viewed using https://tickets.yourvenue.org/api/v1/extensions which produces a list like this.

Some example output formats are below and may be supplemented as needed:

• csv: comma delimited values
• html: output from the database viewable in html format (the default)
• json: data is delivered in json format, probably the best for programming
• xls/xlsx: data is sent in Excel spreadsheet format, probably best for subsequent analysis by end users
• xml: also a programmer based format

Reveal   Top

Close

Reveal Dashboards

Reveal is the Theatre Manager web application that provides insights into your sales data and operations using graphical dashboard widgets.

Graphs in the Reveal dashboards are LIVE. This means that when you are watching a dashboard, if ticket or donation is taken, it will change the appropriate graph in real time.

Reveal Setup Requirements   Top

Close

Employee Access

Reveal requires the following in order to be used:

1. Web Sales set up and running for your venue with an external domain pointing to it like tickets.yourvenue.org. It will require:
   • That you are using TM Server Nginx Setup for the main web server. Apache will not work and those computers will need migrated to TM Server.
   • Some configuration of the Director, depending on your current TM Server setup - to get live charts.
2. An employee account set up in Theatre Manager with:
   • employee permission to use the REST api
   • employee data permission to:
     • manage and create dashboards.
     • manage widgets -AND-
     • permission to look at the data that the graph displays. EG: if the graph shows donation data, the employee must be able to see donation data, otherwise you might get an error like not authorized to view this chart

3. A primary email address:
   • set up for the employee in the employee's contact card
   • demonstrated the ability to log in to the online web store using it since the same email address and password are used to access Reveal dashboards
4. A recent version of a browser like Safari, Firefox, Chrome, Opera or Edge that supports PCI defined 'high encryption' (essentially TLS 1.2 or later).

Import Data   Top

Importing data refers to using the bulk import function to insert large quantities of information into the database from an external source. Most often this external source is exported from another system. The most common file types are tab-delimited (.txt), comma-delimited (.csv), and Excel 2008 and later (.xlsx), though Theatre Manager also accepts some specialty formats.

Most data imports happen when an organization migrates to Theatre Manager from a different CRM, ticket sale, donation tracking, or accounting software. These tend to be full-system data imports that require data cleanup and knowledgeable preparation to accurately translate one database structure into Theatre Manager's structure. These kinds of data migrations are processed by Artsman staff and can happen with all kinds of information, such as sale and donation history, volunteer and employee records, project and task history, gift certificate sales and redemptions, etc. In short, anything that involves transactional data, must be imported by Artsman.

Theatre Manager does have a built-in import function available to you as a client. This function is limited to patron information, mail lists, and seat map names. These are database areas that pose relatively minimal challenges and risks (as opposed to transactional data that affects multiple data files and financial accounts).

Of the built-in client import options, mail lists is the most common. This feature can be used to recover an old Mail List that was deleted or to create a subset of patrons based on analysis done in Excel (e.g. donors with a 10% amount increase every year for 6 years). Importing patron data can be used to add new contacts gathered from a networking event or non-ticketed fundraiser or to process address updates provided from the post office based on a recent mass mailing.

Though the import options available to you are relatively less risky, all data imports should be undertaken with care. Imports cannot be undone. (In serious cases Artsman may be able to help you revert to a previously backed up version of your database, but this will involve the loss of any information entered or edited since the last backup.) It may be good idea to implement business practices that limit who can process imports and/or require a checking or buddy system.

Here are the general steps to follow when importing data, be it patron demographic information, a mail list, or seat names for a venue map:

1. Obtain a data file
   • Purchasing (e.g. new patron lists)
   • Exporting (e.g. from another system)
   • Making (e.g. from paper records)
2. Prepare the data file
   1. Review
   2. Correction/Cleanup
   3. Reformatting (preferably to a .txt or .csv file)
3. Make a backup of the database (strongly recommended)
4. Decide which data to include What to Import
5. Decide which fields should be used to match with existing data (see About Matching Fields)

What to Import   Top

Your initial data file may have a lot of information and many types of fields. We suggest that you make an evaluative judgment on the quality of the data. For example, first name, last name and address fields are usually of good quality. Often work and home phone numbers are reasonable. Other phone numbers and marketing information may be poor quality. Sometimes it is better not to import poor quality data. If it is missing, nobody can make a decision based on poor data.

Apart from poor quality data, it is also important to consider the purpose of the import when deciding which fields to keep in the import file. For example, when importing an address correction from the post office, you may not want to update the patrons first name or last name, just their street address, city, and postal/zip code fields. If so, only include those columns to be updated in the import file.

For detailed formatting guidelines by field, review the how-to guide for preparing the import file.

Matching Fields   Top

Matching fields are special fields in Theatre Manager that have been designated to allow existing data to be replaced during an import process. They are unique identifiers in a relational database, meaning that each value refers to only one specific record. With patron data and Mail List imports, this is usually Patron # (designated with the special column name MKT_C_SEQ). Each patron number refers to one and only one patron record. It is never repeated. This unique identification behavior means that Theatre Manager can match a single patron record to a single row in the import file. Once matched, the system proceeds to overwrite any existing data with the values provided in that row's corresponding columns. Rows in the import file that don't have a matching record in the system will be imported as new records.

Some words of caution when using matching fields to overwrite data:

Export Data   Top

Arts Management Systems believes deeply that free and easy access to your data is critical for your organization's success. Towards that end, Theatre Manager includes not only hundreds of pre-designed reports and easy-to-customize mail lists, but comprehensive direct export access to your CRM information. Exporting data is the process of extracting information from your database and putting it into an editable format to use outside Theatre Manager. For example, you can export the aggregated information from a report into an Excel file for customized formatting, export individual data into a comma-delimited file to use with a data analysis software, or export patron data from a Mail List to an Excel file to import into a third party emailing software. The end-use options are unlimited.

You can export data from a variety of places in Theatre Manager. Each export location has its own set of output formats. In general, the available output formats are:

• Tab-delimited (.txt)
• Comma-delimited (.csv)
• Excel 97-2004 (.xls)
• Excel 2008 and later (.xlsx)
• Specialty data interchange format for things like maps
• Specialty output formats from the REST API which may be more suitable for programmers and automating cross-software integrations

When working with exported data, it is important for future use to understand and record the limits of the information, such as the date it was pulled, any criteria limitations included in the original query, etc. One easy way to keep a record of the export details is to run a print criteria report from the Criteria Search Window, save it as a PDF, and store it with the data export file.

General Ledger Export   Top

Exporting General Ledger Entries allows you to take the entries and import them into your accounting software package. Given their significance, exporting these records requires unique export features. There are several special G/L export formats available to suit a variety of common accounting software. This export format needs to be set in advance of running exports inside Company Preferences >> Accounting Tab.

Once the export format is set up, G/L entries can be exported from one of two places with the same result:

• From the G/L Entry List Window OR
• The final tab of the End of Day Wizard (recommended option in order to automatically pick up all the latest G/L Entries that have been posted)

Regardless of which option above you use to start the export process, G/L Entries can only be exported if they meet all following criteria:

• Are posted to the G/L
  • Unposted G/L entries cannot be exported as they are still subject to manual correction.
• Are not previously exported
  • The Exported Column will have a no in it.
  • This flag is set to yes when an export is complete in order to keep people from accidentally exporting it twice.
  • There's a Clear Export G/L button option to manually override the flag should the original export file be lost.
• Are selected in the list to be exported
• Have matching debit and credit totals in the database

Event Export   Top

This feature (we suggest using the REST API going forward) can be used to export event details using a https request exportevents if you are using Theatre Manager's online sales. Online sales are a central location of data displaying details for upcoming events. This data can be used to feed web pages specific information, such as titles, dates, times and ticket availability (e.g. good, limited, sold out, etc.) of events. The data is presented in an XML style layout and can be customized so the tags are compatible with how you want them exported.

You can use the extracted information to publish data from TM into a custom calendar object and automate creation of buy now links.

The address used to view the export is:

https://tickets.yourvenue.org/TheatreManager/1/exportevents (deprecated)

Close

Sample Code of Exporting Events

NOTE: Refer to the REST API equivalent showing event id, name and sales notes.

Where:

• tickets.yourvenue.org is the name of your ticketing website is required
• 1 is the outlet number at the site you normally use
• exportevents is the function you want the TM server to do

You can preview this information in a browser prior to programming a custom interface. To review the data, right-click on the displayed list and select View Source. This will display the beginning and ending XML tags for each field and a <br> tag after each set of record rows.

Formatting Event Export   Top

You can reformat data to appear in a different order. Fields can be removed or additional event and performance related fields added. Calculations can be set based on existing data to provide required information for customized fields.

Some of the field references are:

• TM_SEQ - Map Number
• TM_MAP_NAME - Map Name
• P_SEQ - Event Number
• P_PLAY_TITLE - Event Title
• PB_SEQ - Performance Number
• PB_SHOW_CODE - Event Code
• PB_SERIES_CODE - Performance Code
• PB_PERF_NOTE - Performance Notes
• F_SOLD_PERCENT - Percent of Seats Sold for this Performance
• PB_PERFORMANCE_DATE - Performance Date
• PB_PERFORMANCE_TIME - Performance Time

To alter the information exported, you need to edit rowTicketEventExportCustom.html

You can have several variations of this page to be used for different exports. When a desired format is required, the page names can be altered so the required export is using the name rowTicketEventExportCustom.html.

The rowTicketEventExportCustom.html page is referenced by the TMticketsExportCustom.html page.

Customizing an Export   Top

The export address can be altered to include or exclude certain information.

For example:

https://tickets.yourvenue.org/TheatreManager/1/exportevents?event=246&venue=18&year=2012&abletosell=1

This export address will export only information for Event number 246 if it takes place in venue 18, is in the year 2012 and is available for sale online.

Additional options that can be added to the address are listed below:

• &year=YYYY - displays the performances for the specific year where YYYY is the desired year
• &event=X - displays the performances for the specific event where X is the event sequence number from Theatre Manager
• &venue=Z - displays the performances for the indicated venue only, where Z is the venue sequence number from Theatre Manager
• &abletosell=1 - displays ONLY the events available for sale online (this is the default if not supplied)
• &abletosell=0 - displays events that are available for sale and/or those events that are not yet available for sale online