Get To Know Your Database

Be a (Database) Star :

Be a (Database) Star A Guide to CMT’s FileMaker Database

Agenda:

2 Agenda 1 2 3 4 The Rules of Conduct C are & Feeding Good Hygiene 5 7 8 Advanced Techniques Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Get to Know Your Database

Get to Know Your Database:

3 Get to Know Your Database 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here FileMaker is an organized mechanism for storing, managing and retrieving information. The information we are concerned with tracking revolves primarily around parents and their children, but also includes alumni, volunteers, donors and ticket buyers.

Example of People view:

Example of People view This is your basic People View. From here you can access all the information that we have about a person. 4

Slide 5:

5 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here We also track events. These events include classes, all Mainstage , Rising Stars, and Marquee shows along with special events like Cabaret Night.

Example of Shows view:

Example of Shows view This is your basic Shows View. From here you can access cast rosters and also get at-a-glance information. 6

Slide 7:

Tracking this information in one repository (FileMaker) allows us to share up-to-date information between staff members, parent’s auxiliary members and volunteers. 7 It is your mission to help proliferate accurate database content!

Slide 8:

It can be tempting to keep the knowledge you have right at your fingertips (on your desktop, in a spreadsheet, or even just in your head), but with a little bit of set-up you can make that information available to everyone’s fingertips. 8 Sharing is good karma!

The Rules of Conduct:

9 The Rules of Conduct 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here It is important to identify whose responsibility it is to monitor the data quality and keep the maintenance process ongoing for each department.

Slide 10:

Do own your department: Schedule imports and data entry into your workflow Always share: If you have information, don’t wait for someone else to enter it. Share it right away. Update contact info when there are changes Always correct typ0s and check your own work 10

Slide 11:

Do flag bad addresses and historical records Always check for duplication and redundant data. Thoroughly search for a contact before creating a new record, merge records when necessary (Steps will be gone over in ‘Care & Feeding’) Do create all known relationships (parent-child hierarchies, partner/spouse) when adding new people 11

Care and Feeding:

12 Care and Feeding 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here In order to properly take care of the database searches and data entry must be conducted in a healthy manor. Let’s start with a basic find…

Basic Find::

To search for a person, begin the find by clicking or use the keyboard shortcut Ctrl F. Enter your search criteria (name, address, alumni, age, role) in the appropriate field. Click ”Enter” to perform/complete the find. TIP: Basic people searches should be done by “Last Name” or “Primary Address.” First letters of the words can be used. 13 Basic Find:

Basic Find…cont.:

To search for “Tester McTesterson at 1401 Highlander Lane” you can enter: last name: mct last name: mctes first name: tes last name: Mctesters address: 1401 high address: 1401 Highla 14 Basic Find…cont.

Slide 15:

The database will turn up exact matches only. Unlike Google searches, the database cannot determine what it thinks you might mean. This is why it good to try multiple searches before adding a new person as they may have been entered with a typo in the past or information may have changed. TIP: If you ever get lost and want to return to the database “home” screen, select the layout “ PeopleEntry .” 15

Still didn’t find what you were looking for? :

After exhausting all search possibilities and finding no hits, it’s time to ADD the person. Click to add a new record. A new blank record will come up and be assigned a new ppl id #. Please enter as much information as you have along with all known relationships. Follow these Guidelines when entering or correcting any information. 16 Still didn’t find what you were looking for?

Guidelines:

Guidelines When entering a new person or correcting information please follow these guidelines. 17

Slide 18:

Similarly, each season, as shows are determined…new shows will need to be created in the database. On the Shows view, click to add a new show. Please fill in the dates of the show and age range, along with season and show type (Rising Stars, Mainstage , Marquee, Special Event) 18 What happens in the 44 th Season…and Beyond?

Slide 19:

BTW: A Marquee Audition Show was added to keep track of people who auditioned but were not cast in Hairspray or Chicago. AAMOI: The marquee shows, along with Cabaret Night are not managed through Parent’s Auxiliary. It will be the responsibility of CMT staff to collect and enter information about marquee cast members in the future. 19

Cast Rosters:

Cast Rosters It’s only a few steps to get the most up-to-date cast info. Here they are… 20 On the Shows view, click and enter the show name. Adding the season you are looking for too will aid your search as some shows have been repeated. Click the Show Roster Icon: On the left side of the screen, in the navigation bar click “Continue”

Cast Rosters…cont.:

Cast Rosters…cont. 21 A “print options” window will appear, cancel this screen if you do not wish to print. A window will appear next which says “Save as excel?” Yes!! Name your spreadsheet as you wish and save it on your desktop or other appropriate location. You now have a spreadsheet with all cast roster information, including cast, role and parent info. Woohoo !

Seeing Double?:

22 Seeing Double? 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Keeping the data tidy is an important duty we all share. De-duping and correcting bad info are key steps in keeping the database clean. If you have determined that you have found a duplicate record, please combine it with the good record.

Seeing Double?...cont.:

23 Seeing Double?...cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Here’s how … When you find a duplicate record…while on that record, enter the ppl id # of the GOOD record into this field And click the button. That one. The records will be automagically merged!

Slide 24:

Remember : If you are changing a mailing address….be careful! You might have to change the “ mailing status ” as well! Remember: It is important to check the “ duplicate household ” box on the second parent created if their address is the same as the first parents. This will suppress them in searches for mailings so we do not send duplicate letters and postcards. 24 Good Hygiene….cont.

Slide 25:

Remember: “ Mailing status ” and “ duplicate household ” tags are meant to be selected to suppress a contact. Please keep in mind that anyone with anything marked in any of those fields will be suppressed in a mailing list. 25 Good Hygiene….cont.

Intermission:

26 Intermission 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here

Advanced Techniques:

27 Advanced Techniques 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Performing Advanced Finds (SEARCHES) The key to getting good search results is in formulating your search. Are you looking for a person or a transaction or an event? Are you looking for the ‘children of’ or ‘parents of’? Always start your searches with what you think is your largest segment and drill down from there.

Related Records:

28 Related Records 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Need to find “the parents of Guys and Dolls performers”? Use the magic wand! In the People view, search by show or class participation….or alternatively donors or ticket buyers, if you would like to find their children. Click “Go to Related Records” Do you want to go to the related parent (or child) record? Yes!!

Related Records…cont.:

29 Related Records…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here The database will show you a found set. Check the left-hand side navigation bar to see how many results turned up in your found set. If there are 180 parents that fit this criteria it will say 180/28,589 Found(Unsorted). You can get an export of these people by clicking Did you find the records you wish to include in this mail merge? Yes!!

Related Records…cont.:

30 Related Records…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Export records to file dialogue box will come up. Name your spreadsheet and be sure to select . xls as the file type as it defaults to .tab. After you name and save your file you will have the option to select more fields to include in your spreadsheet, such as Email, Job Title or Volunteer Hours.

Related Records…cont.:

31 Related Records…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Highlight the fields on the left and click “move” or “move all” to select them. Likewise, you can exclude basic fields from your spreadsheet by highlighting it on the right side and clicking “clear.”

Related Records…cont.:

32 Related Records…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Complete your export. If you would like to indicate in the “History” tab of each person that they were part of this pull, enter a name such as “Parents of Guys and Dolls 43” in the field provided in the next dialogue box. If you are just testing out a pull and may try it a few more times, leave the field blank and hit “cancel” to proceed.

Related Records…cont.:

33 Related Records…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Go to your desktop or appropriate folder to retrieve your new spreadsheet. Done!

Extending a found set/ Multiple criteria searches:

34 Extending a found set/ Multiple criteria searches 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here To first search for people who are Ticket Buyers and then broaden the search to also include people who are donors… Enter into find mode: Perform a find for ticket buyers by clicking the Tickets Tab and entering the season you are querying on, let’s say “43”.

Extending a found set/ Multiple criteria searches…cont.:

35 Extending a found set/ Multiple criteria searches…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Create a second find request by clicking “Request” in the top navigation bar and selecting “Add New Request” (the keyboard shortcut is Ctrl N). Tab over to Donations and enter “<$50” in the ‘received donation Amount’ field. This will bring up donors for all years, or you can put a date range in if you would like to narrow the found set.

Extending a found set/ Multiple criteria searches…cont.:

36 Extending a found set/ Multiple criteria searches…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here New requests can be used within the same field as well, such as City. The search find “San Jose,” add new request “Los Angeles” would return people whose address is either in San Jose or Los Angeles.

Extending a found set/ Multiple criteria searches…cont.:

37 Extending a found set/ Multiple criteria searches…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here New requests can be used within the same field as well, such as City. The search find “San Jose,” add new request “Los Angeles” would return people whose address is either in San Jose or Los Angeles. Any New Request is essentially an “ OR ” request.

Handy symbols::

38 Handy symbols: 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here “ … ” Use this for any range like date: “09/01/2010…02/02/2011” zip code: “94000…95999” or age: “8…21” “ * ” Placed in a search field will result in finding any records with any info in that field

Handy symbols::

39 Handy symbols: 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here “ = ” can also be used to customize precise finds. For example searching “=95126” in the zipcode will result in all records that contain exclusively that number. If you would like to find all records with the zipcode blank your search would be just the equal sign “=”. Omit is found in this same left-hand side navigation bar. When doing multiple requests, make omit your last request.

An “OMIT” Find::

40 An “OMIT” Find: 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Let’s try an omit. Let’s say you would like to find all 43 rd season ticket buyers except season subscribers. Begin Find Enter your search criteria of 43 rd season Ticket Buyers.

An “OMIT” Find…cont.:

41 An “OMIT” Find…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Add a New Request ( Ctrl+N ) Enter your omit criteria “43 rd season subscribers” In the left-hand side navigation bar toggle off “include” and onto “omit”. Complete the find hitting Enter.

An “OMIT” Find…cont.:

42 An “OMIT” Find…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here TIPS: You can add as many layers of criteria as you need – just keep adding a New Request ( Ctrl+N ) to add criteria. You must always put your omit criteria as the LAST steps in your find.

OR V. AND:

43 OR V. AND 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here So you don’t want to search for an OR ? If you would like to find people who are ticket buyers AND who have donated…a constrained find will be needed. This is essentially your “ AND ” search and is more restrictive. It only retrieves data that meets all the conditions you specify.

Constraining a found set/Narrowing your selection::

44 Constraining a found set/Narrowing your selection: 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here To search for all ticket buyers and then constrain the found set to people who have also donated: Perform and complete a find for Ticket Buyers in the 43 rd Season. Perform a second find for donors within the last year –but DON’T hit enter yet!

Constraining a found set/Narrowing your selection…cont.:

45 Constraining a found set/Narrowing your selection…cont. 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here Choose from Requests the option Constrain Found Set. The found set would then consist of people who are 43 rd season ticket buyers AND Donors within the past year.

Creating a mailing list :

46 Creating a mailing list 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here After determining your search criteria and method, it is crucial to remember to: Omit “Mailing Status” category so bad addresses and the like are not included. Omit “Do not solict ,” “Do not mail” when appropriate. Omit “Duplicate Household” checkbox Remember, each time you forget to do these steps, somewhere in the universe, a star dies.

Congratulations: :

47 Congratulations: 7 8 Discussion Item Seven Add a second line of text here Discussion Item Eight Add a second line of text here You have made it through to the end of your CMT FileMaker training session! You are the proud owner of “the database”. You’re a Rock Star!