User manual FILEMAKER FILEMAKER PRO 7 - TUTORIAL

FileMaker Pro 7 Tutorial © 2004 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries, and ScriptMaker and the file folder logo are trademarks of FileMaker, Inc. FileMaker documentation is copyrighted. You are not authorized to make additional copies or distribute this documentation without written permission from FileMaker. You may use this documentation solely with a valid licensed copy of FileMaker software. All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. Credits are listed in the Acknowledgements document provided with this software. For more information, visit our web site at www.filemaker.com. Edition: 01 Contents Lesson 1 FileMaker Pro basics How to use this tutorial We begin with a demonstration We explain how it works You build the structures Where to find the lesson files If you need to start over Database concepts What is a database? Why use a database? How is a database organized? How is field data displayed? FileMaker Pro basics About FileMaker Pro modes For more information 7 7 7 7 7 8 8 8 8 8 9 9 9 9 Narrow your search Sort your found set For more information 18 18 19 Lesson 4 Creating a database and entering records Create a simple database and define fields Look at the sample file About field definitions Create a database Enter data into the database Create the first record Create another record Modify data in a record For more information 21 21 22 22 23 23 23 23 23 Lesson 2 Browsing information Open a database and move between records View information in different ways See a different layout View your layouts as forms, lists, and tables For more information 11 11 12 13 14 Lesson 5 Customizing what you see Customize your view in Layout mode Look at the sample layouts About layouts and tables About customizing layouts Customize a layout Resize, move, and add a field Select and resize a field Move a field Add a field Display a number as currency Add color to a layout part Add custom text to a layout Add text 25 25 26 26 26 26 26 26 27 27 28 29 29 Lesson 3 Finding and sorting records Find records based on criteria in a single field Find records based on criteria in separate fields Find records that match multiple criteria in the same field Find records that match a range of criteria in the same field 15 16 16 17 4 Contents Change text size and color Add a graphic to the layout For more information 29 29 30 Lesson 8 Automating tasks with buttons and scripts Perform a task using a button Look at a sample button Create the button Perform a series of tasks using a script Run a script in the sample file About scripts Create a script to preview the Labels Layout Assign a script to a button For more information 43 43 43 44 44 44 44 45 46 Lesson 6 Creating lists, mailing labels, and form letters Create a columnar list Look at the sample list Create a columnar list Create mailing labels Look at the sample labels About label layouts Create a label layout Create a form letter Look at the sample letter About form letter layouts Create a form letter For more information 31 31 31 32 32 32 32 33 33 34 34 36 Lesson 9 Creating and running reports Generate a report with grouped data Look at a sample report About subsummary reports Create a subsummary report Generate a report with grouped data and totals Look at a sample report Create a report with grouped data and totals For more information 47 47 48 48 50 50 51 53 Lesson 7 Simplifying data entry Enter data using value lists Look at the sample value list Define a value list Assign a value list to a field and display it as radio buttons Generate values with a calculation field See how calculations speed data entry Create a calculation field Test your calculation Automatically enter a serial number Look at a serial number example Create a field for serial numbers For more information 37 37 37 38 38 39 39 40 40 40 41 41 Lesson 10 Making databases relational View data from one related record See how relationships work in the sample files About related tables Display a related record in a file View data from a list of related records Work with related records in the sample file About working with lists of related records Display a list of related records For more information 55 55 56 56 57 57 58 58 60 Contents 5 Lesson 11 Keeping your data safe How to back up your database When to back up For more information 61 61 61 6 Contents Lesson 1 FileMaker Pro basics Welcome to the FileMaker® Pro tutorial. This tutorial shows you how you can manage your information in FileMaker Pro. You will learn how to use FileMaker Pro software to: Files included with this tutorial are intended to illustrate the features of FileMaker Pro, and as such, might be unsuitable for use as templates for further development. 1 1 1 1 1 1 locate and sort information create and enter records in a simple database create mailing labels create calculations, buttons, and scripts create and run reports create relationships between FileMaker Pro database tables We begin with a demonstration To show you the goal of each lesson up front, we guide you through a sample file that illustrates a feature or concept. This way, you will always know a lesson's objectives. We explain how it works As we introduce new techniques and concepts, we will explain how to use them in other situations. It takes approximately four hours to complete this tutorial, which can be done in one session or several shorter sessions. You build the structures How to use this tutorial These lessons are based on the membership database of a fictitious travel firm, The Corporate Travelers Club. Corporate travelers who are members of this club save their businesses money on travel costs, and the club's expenses are offset by an annual membership fee. FileMaker Pro is used to track the members' names, addresses, and membership status, as well as to generate labels and form letters to contact members. It's best to complete these lessons sequentially, as the concepts introduced in early lessons are assumed to be understood in later lessons. However, because the lessons are self-contained, it's possible to skip lessons entirely, or to complete them later if desired. This tutorial assumes that you are familiar with your computer's operating system. Opening files, locating folders, and other activities that require some knowledge of your computer's OS are a necessary part of completing this tutorial. Because using FileMaker Pro is the most effective way to learn the application, each lesson features a hands-on activity designed to guide you through menus, screens, and concepts step by step. Where to find the lesson files If the tutorial files were installed, they are in: FileMaker Pro 7 folder\English Extras\Tutorial The tutorial files are also on the FileMaker Pro 7 CD: FileMaker Pro 7 CD\English Extras\Tutorial Important If the files are not installed, you must copy the Tutorial folder from the CD to your hard drive before beginning this tutorial. The sample files illustrate the concepts explained in the lessons, and let you see how features work before you build them. Refer back to the sample file associated with each lesson if you need to. 8 FileMaker Pro Tutorial The working files mimic the sample files, except the features being taught in particular lessons have been removed. You will add these missing items and structures as you complete each lesson. Database concepts What is a database? A database is a method of organizing and analyzing information. You've probably used several databases recently without realizing it. A date book, a parts list, and even your own address book are databases. Overview of the FileMaker Pro Tutorial folder structure or FileMaker Pro 7 CD FileMaker Pro 7 folder Why use a database? Storing information in a database file has many benefits. A database doesn't just hold information--it helps you organize and analyze the information in different ways. For example, databases allow you to group information for reports, sort information for mailing labels, count your inventory, or find a particular invoice. Computerized databases offer many additional advantages over their paper-based counterparts: speed, reliability, precision, and the ability to automate many repetitive tasks. English Extras Tutorial How is a database organized? A database file is organized into one or more tables. Tables store records. Each record is a collection of fields. Sample Files Sample02.fp7 Sample03.fp7 Sample04.fp7 Sample05.fp7 Sample06.fp7 Sample07.fp7 Sample08.fp7 Sample09.fp7 Sample10.fp7 Working Files MyFile05.fp7 MyFile06.fp7 MyFile07.fp7 MyFile08.fp7 MyFile09.fp7 MyFile10.fp7 Imagine your address book is a FileMaker Pro database file. Each listing in your address book is one record in an Address Book table. Each record has information, such as name, address, city, and so on. Each of these pieces of information--name, address, and city--is stored in a separate field. How is field data displayed? FileMaker Pro displays fields on layouts. Each layout is based on a single table, and displays records from that table. If you need to start over If you make a mistake or need to start over, replace your existing Tutorial folder with a new copy from the FileMaker Pro 7 CD. FileMaker Pro basics 9 Imagine that your database has a Tasks table in addition to the Address Book table. A layout based on the Address Book table will display fields and records from the Address Book table. A layout based on the Tasks table will display fields and records from the Tasks table. Current mode (Browse) Mode buttons Name of layout Filename FileMaker Pro basics Unlike most word processing or spreadsheet programs, FileMaker Pro saves your work automatically. Consequently, it's important to carefully consider your actions when making sweeping changes to your files, especially when deleting records. Once records are deleted, they are completely removed from your database. As you will learn in lesson 11, you should back up your files on a regular basis. Backups can save you many hours of unnecessary work and worry in the event that your files become lost or damaged, or records are inadvertently deleted. Number of the current record Fields About FileMaker Pro modes When you work in FileMaker Pro, you work in one of four modes. For more information For more information on FileMaker Pro basic concepts, see chapter 1 of the FileMaker Pro User's Guide. You can also find comprehensive information about procedures and concepts in FileMaker Pro Help. To access FileMaker Pro Help: Choose Help menu > FileMaker Pro Help. Use Layout mode to specify how information will appear on the screen or when you print it. Use Preview mode to see what the pages will look like when you print them. Once you have opened a database, you can switch from one mode to another using either the View menu, mode buttons, or the mode popup menu at the bottom of the application window. 1 1 1 1 Use Browse mode to enter data and view records. Use Find mode to locate a record or a group of records quickly. 10 FileMaker Pro Tutorial Lesson 2 Browsing information You view, enter, or change data in Browse mode. This is the simplest way to interact with your database. In this lesson you will: In the status area, notice that there are 30 records and that the first record is displayed. 1 1 1 1 open a database with data already in it move between records view information in different ways by switching layouts see the difference between form view, list view, and table view Book icon Slider Record currently displayed Number of records Important Copy the Tutorial folder to your hard drive or verify that it is installed before beginning this lesson. See "Where to find the lesson files" on page 7 for more information. Status area Open a database and move between records To open the sample database for this lesson: 1. Open the Tutorial folder on your hard drive. 2. Open the Sample Files folder. 3. Open Sample02.fp7. 4. Notice that the database is in Browse mode. FileMaker Pro 5. Click the right page of the book icon to move forward one record at a time, and click the left page to move back one record at a time. 6. Drag the slider to the right and release to move forward a number of records at one time. Drag the slider to the left and release to move backwards a number of records. You can also type a record number in the area immediately below the book icon to change the current record displayed. defaults to Browse mode when a database is opened. The current mode is identified at the top of the status area View information in different ways Even though you enter information only once in FileMaker Pro, you can use that information in many ways. Layouts let you view some or all of your fields. Because it is not necessary to see all of your fields in each layout, layouts are a powerful tool for working with or printing just the information you need to accomplish a particular task. 12 FileMaker Pro Tutorial Steve Williams 789 Ninth Avenue New York, NY 10001 Dear Sophie, Thank you for reserving a cruise with us. We value your business and appreciate the opportunity to serve you. 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 555-1234 If you have any questions about your reservations, please call us--we want you to be satisfied with your cruise. Sincerely, John Lee Phone List Juanita Alvarez Michelle Cannon Andre Common Marie Durand Jean Durand William Johnson John Lee Patrick Murphy Le Nguyen Kentaro Ogawa Mary Smith John Smith Sophie Tang Steve Williams Betty Wilson The same data can be arranged differently with multiple layouts The Data Entry layout displays all of the fields in the Sample02 table See a different layout To see some layouts in action, use the Sample02.fp7 sample file you just opened. 1. Click the Layout pop-up menu, located above the book icon. A list appears showing the layouts available in this file. Layout pop-up menu 3. Choose the List of Members layout from the Layout pop-up menu. This layout shows each record as one row in a columnar list. Notice that the List of Members layout contains only four of the fields that were present in the Data Entry layout. Because fewer fields are present on this layout, some data is simply not displayed, however, no data has been removed from the database. 2. Notice the check mark next to the Data Entry layout, your current layout. The Data Entry layout shows all of the fields in the Sample02.fp7 table. Browsing information 13 Form view List view The List of Members layout displays only four of the fields present on the Data Entry layout 4. Go to the Layout pop-up menu and return to the Data Entry layout. No data has been lost. Table view The List of Members layout, viewed as a form, then as a list, and finally as a table View your layouts as forms, lists, and tables You can view your layouts in three different ways: as a form, as a list, and as a table. List view shows your current layout as a list, with one record appearing beneath another. Switch between form view, list view, and table view in the same layout to see how they are different. 1. In Sample02.fp7, switch to the List of Members layout. You see many records, displayed as a list. 2. Choose View menu > View as Form. 1 1 1 Form view displays your current layout one record at a time. Table view shows you many records at one time in a grid. You now see a single record, displayed in the same layout. Only the viewing option has changed. 3. Choose View menu > View as Table. Generally, layouts can be toggled between form view, list view, and table view while the database is in Browse mode. The View as Table feature allows you to view many records at once in a grid. 4. Choose View menu > View as List to return to the original view of the List of Members layout. 5. When you are finished with this lesson, close the file by choosing File menu > Close. 14 FileMaker Pro Tutorial For more information You have opened a database, browsed through records, switched layouts, and learned the differences between form view, list view, and table view. For more about browsing information in FileMaker Pro, see chapter 1 of the FileMaker Pro User's Guide. Lesson 3 Finding and sorting records Your database is a collection of records. Sometimes you'll work with all of the records in the database (to mail out an annual catalog for each customer, for example). Often, however, you'll work with a subset of your database--people who come from a particular city, for example, or records that fall within a particular range of dates. In FileMaker Pro, this process of searching for records that match particular criteria is called finding records. Once you've found the records you want to work with, you can change their order by sorting them. Records can be sorted in ascending or descending order. In this lesson, you will: In Find mode, you create a find request using the fields in the layout you see. Type the criteria you want to search for directly into the fields on the request. 5. Type New York in the City field. 6. Click the Find button in the status area. 1 1 1 1 find records matching criteria in a single field and multiple fields find records matching a range of data narrow your searches by omitting records sort records in ascending and descending order Find button Important Copy the Tutorial folder to your hard drive or verify that it is installed before beginning this lesson. See "Where to find the lesson files" on page 7 for more information. Find records based on criteria in a single field In the membership database, find all of the members that are from New York City. 1. Open the Tutorial folder on your hard drive. 2. Open the Sample Files folder. 3. Open Sample03.fp7. 4. Choose View menu > Find Mode. Your find request returns records for four members who live in New York. This is the found set. Notice that FileMaker Pro returns to Browse mode when your search is complete. 16 FileMaker Pro Tutorial Your find request returns a found set of two records for the two new members who live in the United States. 5. To find all of the records in your database, choose Records menu > Show All Records. Note It is not necessary to choose Show All Records before performing a Find, as FileMaker Pro finds records from the entire database by default. Total number of records in the database Number of records in the found set 7. Click the book icon or slider to view each of the four records in the found set. Find records that match multiple criteria in the same field Sometimes you need to find records that match more than one criterion in a single field. For example, you may need to find all members located in either New York or London. This type of search is commonly referred to as an OR search, because your found set will consist of records that match any one of the find criteria. To perform an OR search in FileMaker Pro, you will need to use multiple find requests. Find records based on criteria in separate fields You can find records that match multiple criteria, for example members who are both from the United States and are new members. This type of search is commonly referred to as an AND search. To perform an AND search in FileMaker Pro, type each search criterion directly into the appropriate field in Find mode. To find all members who live in the United States and are new members: 1. Choose View menu > Find Mode. 2. Type USA in the Country field. 3. Locate the Membership Type field, and select the New radio 1 Type the first search criterion into the appropriate field of the first find request. 1 Create a second find request and enter the second search criterion in the same field. When you click the Find button, FileMaker Pro will retrieve all of the records that match any of the criteria you've entered. To find all members living in New York or London: 1. Choose View menu > Find Mode. 2. Type New York in the City field. button. Select the New radio button in the Membership Type field 3. Choose Requests menu > Add New Request. Notice that the status area shows that there are two requests. 4. Click Find in the status area. Finding and sorting records 17 Number of requests 4. Type London in the City field of this second request. 5. Click Find in the status area. Symbols pop-up menu Your find request returns a found set of six records for six members who live in either New York or London. Find records that match a range of criteria in the same field Sometimes, you might want to find records that match a range of criteria within a single field. For example, you might want to find all of the records that fall within a one-month period, or locate all of the sales invoices numbered between 500 and 1000. To perform this type of search in FileMaker Pro, you use a special range symbol in your find request to specify the lower and upper limits of your find criteria. To find the members who paid their membership fees between January 1, 2004 and June 30, 2004: 1. Choose View menu > Find Mode. 2. Type 1/1/2004 in the Date Paid field. 3. In the status area, click the Symbols pop-up menu. Select range Your find request should now have 1/1/2004... in the Date Paid field. 4. Immediately after the ellipsis (...), type 6/30/2004. 5. Click Find. Your search returns six records, for six members who paid their fees within the first six months of the year 2004. from the list, or type an ellipsis (...). 18 FileMaker Pro Tutorial Narrow your search Sometimes, you might need to find records that match certain criteria while at the same time excluding others (for example, finding all of the records that were added in a given year except for those added in February, or finding all customers who reside in New York state except for those in New York City). You can perform these types of finds by using a combination of multiple find requests and omitting certain records. Because FileMaker Pro processes multiple find requests in the order they are created, it is possible to create very specific, layered searches in this manner. Omitting records in a search does not delete them from the database. To find the records of members who paid their fees in the first six months of 2004, except for those who paid their fees in February: 1. Choose View menu > Find Mode. 2. Type 1/1/2004 in the Date Paid field. 3. In the status area, click the Symbols pop-up menu. Select range Omit checkbox 10. Click Find. Your search returns five records for five members who paid their fees within the first six months of 2004, except the month of February. from the list, or type an ellipsis. 4. After the ellipsis, type 6/30/2004. 5. Choose Requests menu > Add New Request. Notice that the status area shows that this is the second request. 6. Type 2/1/2004 in the Date Paid field. 7. In the status area, click the Symbols pop-up menu and select range Sort your found set Once you have a found set of records to work with, you might want to sort those records. For example, you might want to display the records in alphabetical order, or sort them from newest to oldest. To sort records in alphabetical order by the members' last names: 1. Go to the Layout pop-up menu and choose the List of Members layout. This layout will make it easier to see the results of the sorting. 2. Choose Records menu > Sort Records. 3. If any fields appear in the Sort Order column on the right side of from the list, or type an ellipsis. 8. After the ellipsis, type 2/29/2004 in the Date Paid field. 9. Click Omit in the status area to select it. the dialog box, click Clear All. 4. In the list of available fields, select Last Name and click Move. Finding and sorting records 19 For more information You have found records based on a variety of criteria, and narrowed your searches by using Omit. You have sorted the results of your searches. For more information on finding and sorting records in FileMaker Pro, see chapter 1 of the FileMaker Pro User's Guide. Sort direction buttons become active when a field is selected Notice the sort direction symbol to the right of the Last Name field increases in size from left to right. This indicates that when the database is sorted by this field, the data will be in ascending order (from a to z). 5. Click Sort. The names in the List of Members are now in alphabetical order by last name. 6. When you are finished with this lesson, close each open file by choosing File menu > Close. 20 FileMaker Pro Tutorial