FileMaker Pro ® Tutorial
© 2004–2005 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. All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. FileMaker documentation is copyrighted.
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 8 9 9 9 Lesson 2 Browsing information Open a database and move between records View information in differe
FileMaker Pro Tutorial Add custom text to a layout Add text Change text size and color Add a graphic to the layout For more information 29 29 29 29 30 31 31 31 32 32 32 32 33 33 34 34 36 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 41 41 41 41 Le
Contents 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 59 Lesson 11 Keeping your data safe How to back up your database When to back up For more information 61 61 61 5
FileMaker Pro Tutorial
Lesson 1 FileMaker Pro basics Welcome to the FileMaker® Pro tutorial. This tutorial shows you how you can manage your information in FileMaker Pro.
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. Overview of the FileMaker Pro tutorial folder structure Sample02.fp7 Sample03.fp7 Sample04.fp7 Sample05.fp7 Sample06.fp7 Sample07.fp7 Sample08.fp7 Sample09.fp7 Sample10.fp7 What is a database? A database is a method of organizing and analyzing information.
FileMaker Pro basics 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) 9 Filename Mode buttons Name of layout FileMaker Pro basics Unlike most word processing or spreadsheet programs, FileMaker Pro saves your work automatically.
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 the status area, notice that there are 30 records and that the first record is displayed.
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.
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.
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.
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. Number of records in the found set Total number of records in the database 7.
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.
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.
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.
FileMaker Pro Tutorial
Lesson 4 Creating a database and entering records In the previous lessons, you saw how to use a simple database with existing data. Now you will create your own file and add records. 6. Choose Members from the Table pop-up menu if it is not already In this lesson you will: You see all of the fields in the Members table. 1 1 1 1 1 selected.
FileMaker Pro Tutorial About field definitions You create a field for each category of information you want, such as First Name or City. To find, sort, calculate, and display data correctly, the field type should match the kind of data it contains (text, number, date, and so on). For example, you can’t search for text values in a field of type Number. You will learn about other field types in later lessons. Name a field Create a database 1. Choose File menu > New Database. 2.
Creating a database and entering records 23 Enter data into the database Modify data in a record Now you can begin to enter members’ data. Suppose you realize that Jane Doe paid 75 dollars, not 25. You can easily correct the entry. Create the first record 1. If you don’t see field boxes, press Tab to begin data entry. 1. Go to Jane Doe’s record. 2. Select the amount in Fee Paid. 2. Type Jane in the First Name field. 3. Press Tab to move to the next field. 4. Type Doe in the Last Name field. 5.
FileMaker Pro Tutorial
Lesson 5 Customizing what you see You can enhance the way your data looks by adding graphics and other effects. In this lesson, you will: 1 1 1 1 1 1 learn how layouts determine what you see Notice that the layout is structured like a standard form, with field labels on the left and data on the right. Also, all fields in the Members table are present on the Data Entry layout. 5. Choose Fee Information from the Layout pop-up menu. 6.
FileMaker Pro Tutorial You use Layout mode to customize the appearance of your data. Although the data is still in the file, you do not see it here. Instead, you see the names of the fields. 5. Choose Fee Information from the Layout pop-up menu. You see a plain layout. You will modify this layout in different ways in the following sections. About layouts and tables Each layout is based on a single table, and shows records from that table.
Customizing what you see 27 Display a number as currency You can display a number as currency, even though only the number is typed into the field. 1. Choose Window menu > Sample05. 3. Drag the Last Name field label above the Last Name field. 2. Choose View menu > Browse Mode. 4. Drag the First Name field label above the First Name field. 3. In the Fee Information layout, look at Fee Paid and notice that the number displays as currency. 5. Choose View menu > Browse Mode to see your results. 4.
FileMaker Pro Tutorial Add color to a layout part You will change the color of the top of the layout to match the colors of other layouts in the file. 1. Choose Window menu > Sample05. 8. Choose Other Color from the fill color palette. The header part uses a custom color. and choose RGB Sliders from the Colors dialog box pop-up menu. (Windows users can skip this step.) 9. Mac OS: Select the sliders icon 2. Choose View menu > Layout Mode and look at the header at the top of the layout. 10.
Customizing what you see 29 Add custom text to a layout Change text size and color You will add the name of the organization to the top of the layout, then format the text. 1. Click the Corporate Travelers Club text to select the text block. Add text 1. Choose Window menu > Sample05. 2. Choose Format menu > Text. 2. Choose View menu > Layout Mode and look at the heading at the 3. Choose Times from the Font list. top of the layout. 4. Choose Custom from the Size list, and type 28.
FileMaker Pro Tutorial 12. When you are finished with this lesson, close each open file by choosing File menu > Close. For more information You have learned how to enhance the appearance of your data by using graphics, custom text, field formatting, and creative positioning of objects on layouts. There are many other ways to customize the appearance of fields and layouts.
Lesson 6 Creating lists, mailing labels, and form letters You can easily create layouts to view and print information in many different ways. You see a columnar list showing some of your fields. In this lesson, you will create: 1 1 1 a list of your records mailing labels a form letter Important Download the tutorial folder to your hard drive before beginning this lesson. See “Where to find the lesson files” on page 7 for more information. 5.
FileMaker Pro Tutorial 9. Double-click each of the following fields in the order you want it to appear in the list: 1 1 1 1 Create mailing labels Last Name Look at the sample labels First Name 1. Choose Window menu > Sample06. Company 2. Choose View menu > Browse Mode. Membership Type 3. Select Labels in the Layout pop-up menu. These fields move to the Layout fields list. You see the mailing label for the current record. 4. Choose View menu > Preview Mode.
Creating lists, mailing labels, and form letters Create the label 1. Choose Window menu > MyFile06. 5. Double-click Home Address 1 in the list, then press Enter (Windows) or Return (Mac OS). 2. Choose View menu > Layout Mode. 6. Double-click Home Address 2 in the list, then press Enter 3. Choose Layouts menu > New Layout/Report. 4. For Layout Name, type Labels. 5. Select Labels for the new layout type. 6. Click Next. 7. Choose Avery 5160 from the Use label measurements for list.
FileMaker Pro Tutorial 6. Also notice that the wording in each letter is the same, except that 7. On the layout, click the Header tab, then press Backspace the company name for each record appears in the first paragraph. The text flows around the company name, regardless of its length. (Windows) or Delete (Mac OS). 8. Click the Footer tab, then press Backspace (Windows) or Delete This letter is ready to print, for every record in the database. (Mac OS).
Creating lists, mailing labels, and form letters 35 In your own letters, you can use more fields (for example, a title field) for a more elegant salutation. 4. Type a comma and two line spaces. 5. Type the letter: The Corporate Travelers Club welcomes you and your company to our organization. The text tool is selected We look forward to doing business with you and hope you have many pleasant trips with us! Customize the letter with the company name 1. Click after the word company in the first paragraph.
FileMaker Pro Tutorial For more information In this lesson, you have created list, label, and form-letter layouts. You can create as many layouts as you like, to organize your information in any way you like. To learn how to create other layouts, see lesson 9 in this tutorial and chapter 2 in the FileMaker Pro User’s Guide.
Lesson 7 Simplifying data entry You can simplify the way you enter data in FileMaker Pro and improve accuracy by using value lists, calculation fields, and data auto-entry. 4. Choose Data Entry from the Layout pop-up menu if it is not already In this lesson, you will: You see two radio buttons, one for New and one for Continuing. These correspond to the two choices in the Membership Types value list. 1 1 1 see how value lists can aid data entry write and test a calculation formula selected. 5.
FileMaker Pro Tutorial 8. Click in the large box and enter the values for this list, New and Continuing, by typing each value on its own line. Select Radio Button Set Select Membership Types value list 6. Click OK. Your radio buttons should be visible in Layout mode. 9. Click OK to close the Edit Value List dialog box. 10. Click OK to finish. Assign a value list to a field and display it as radio buttons 1. Choose View menu > Layout Mode. 2. Select the Membership Type field.
Simplifying data entry The calculation formula uses values from the current record or related records. The formula can use values from all field types. You can use calculations to return text values (for example, combining the contents of first name and last name fields into a full name field), dates, times, timestamps, and the contents of container fields. See how calculations speed data entry Members of the Corporate Travelers Club pay an annual fee.
FileMaker Pro Tutorial 7. Scroll through the list of formulas in the upper-right corner of the 11. Make sure the calculation result is set to Number. Specify Calculation dialog box until you locate If (test ; resultOne ; resultTwo). Double-click this formula so that it appears in this dialog box. This formula has three parts: a test, a result if the test evaluates as true (result one), and a result if the test evaluates as false (result two).
Simplifying data entry Automatically enter a serial number FileMaker Pro lets you automatically enter certain types of data—for example, incremental numbers—when you create a new record. 41 8. Select Serial number on the Auto-Enter tab. You don’t need to change the Generate choice, or the amounts for next value or increment by for this exercise. Look at a serial number example 1. Choose Window menu > Sample07. 2. Go to the last record in the database. 3.
FileMaker Pro Tutorial
Lesson 8 Automating tasks with buttons and scripts FileMaker Pro lets you automate many database tasks with buttons and scripts. These powerful features can save you a lot of time by performing a series of actions with a single click. In this lesson you will: 1 1 1 make and use a button to perform a task create and run a simple script to perform a multi-step task use a button to perform a script Important Download the tutorial folder to your hard drive before beginning this lesson.
FileMaker Pro Tutorial Run a script in the sample file 1. Choose Window menu > Sample08. 2. Choose Scripts menu > Preview Labels Layout. Specify the List of Members layout Select Rounded button 11. Click OK. 12. Notice that the insertion point is now in the center of your button. Type Go to List of Members in your button to identify it. 13. Click outside the button. If you need to move the button, select it and drag it to the desired location, as with any other layout element.
Automating tasks with buttons and scripts 45 Assign a script to a button While buttons are generally more convenient for users, scripts are a more powerful feature, allowing you to combine many menu commands into a single executable action. Using a button to run a script gives you the best of both features. To assign the script you just created to the button you created earlier: Specify a target layout for the script step 1. In MyFile08.fp7, choose Data Entry from the Layout pop-up menu. 2.
FileMaker Pro Tutorial Rename the button 1. Click the text tool . 2. Click the button once. The text insertion point appears in the button. 3. Type Preview Labels Layout to rename this button. It may be necessary to resize this button after renaming it. Test the button 1. In Browse mode, test the button by clicking it. Clicking this button performs the script, which displays the Labels layout and switches to Preview mode. 2.
Lesson 9 Creating and running reports The process of retrieving and organizing data from a database is called reporting. Summary reports present just the totals of your data; they do not break information into smaller groups. Subsummary reports, also referred to as reports with grouped data, categorize information by a particular field or fields, allowing you to group information hierarchically. This subsummarized information can then be subtotaled, averaged, or counted.
FileMaker Pro Tutorial About subsummary reports 7. Select Report with grouped data, and click Next. A report with grouped data requires a number of elements to work properly. Subsummary reports include: 1 a subsummary part in the report layout for each level of detail you want to break out. The New Layout/Report assistant creates these for you. 1 a field or fields to group records by. These fields are referred to as break fields.
Creating and running reports 49 5. Move the Membership Type field to the Report categories list to organize the records by membership type. 8. Click Next. Membership Type is referred to as a break field because you are using the contents of this field to break the records into categories for your report. Select a theme and create the header and footer 6. Click Next. 2. Click Next. 7. Move the Last Name field to the Sort order list. 3.
FileMaker Pro Tutorial 1 provides a grand total of these fees 1. To view the finished sample report, choose Window menu > Sample09. 2. Choose Scripts menu > Membership Fee Report to generate the membership fee report. 3. Examine the resulting report. As in the previous example, the members are grouped by membership type. Additionally, the fees collected for each membership type are subtotaled beneath those members’ names, and a grand total of all fees appears at the bottom of the report. 6.
Creating and running reports A report with grouped data and totals requires the same elements as a report with grouped data: a subsummary part in the report layout for each category, a field or fields to group records by, and a sort of the database by these fields. Select the fields used in this report Two additional elements are also required: 3. Select and move the First Name field.
FileMaker Pro Tutorial 3. For Subtotal placement, use the default value of Below record group. 4. Click Add Subtotal to add the Membership Revenue field as the subtotal. This tells FileMaker Pro to create a subtotal of the membership fees that are paid beneath each membership category. 8. Click Next. Select a theme and create the header and footer 1. Select the Standard theme to set the text size, color, and style of the finished report. 5. Click Next. 2. Click Next. 6.
Creating and running reports Create a script to run this report again 1. Click Create a script. 2. Leave Script Name as it appears. Click Next. 3. Click View the report in Preview mode if it is not already selected. 4. Click Finish. Your finished report should look like the report pictured on page 50. 5. When you are finished with this lesson, close each open file by choosing File menu > Close.
FileMaker Pro Tutorial
Lesson 10 Making databases relational Relational databases let you easily view related data and streamline data entry. You can enter data once, then view and use that data in related tables or files. In this lesson, you will: 1 1 1 1 see how relational databases work define a relationship display data from one related record display a list of related records The sample file in this lesson uses two tables: 1 a Members table with records for individual Corporate Travelers Club members 4.
FileMaker Pro Tutorial About related tables The Companies table and Members table are related tables. 6. In the Members table, click the Company field and drag a line to the Company field in the Companies table. When you enter a company name in a record in the Members table, FileMaker Pro identifies the record for that company in the Companies table. FileMaker Pro then displays the address from this matching company record in the Members table.
Making databases relational 3. Using the Field tool , drag a field onto the layout. You see the Specify Field dialog box displaying the list of fields in this table. 4. Click Current Table (“Members”) and choose the Companies table 57 View data from a list of related records Suppose you want to show all club members for each company. You also want to add new members to the Members table without entering the company name for each. from the list.
FileMaker Pro Tutorial You see Joe Williams’ record, with the company information filled in. You automatically created this record when you typed Joe Williams into the list in the Companies table. Display a list of related records Create a portal to display matching records 1. Choose Window menu > MyFile10. Add a new record and view it in the list 2. Choose the Companies layout from the Layout pop-up menu. 1.
Making databases relational 59 6. Click OK, then click OK again to close the Define Database dialog box. 5. Choose View menu > Browse Mode to view your list of members. 7. Choose View menu > Layout Mode. Company, then look to see if it appears in the Data Entry layout. (It will be the last record.) 8. Click the portal tool and draw a box to hold the list of 6. Test your new structure: type the name of a new member at ABC names. 7. When you are finished with this lesson, Exit or Quit 9.
FileMaker Pro Tutorial
Lesson 11 Keeping your data safe Power failures, disk problems, and other unexpected computer problems can happen to anyone at any time. To help prevent data loss, regularly back up all important documents on your computer, including your databases. Important Next time, back up to a different disk or volume. Never back up over the only or most recent copy of your file. You should have several different backups at all times.
FileMaker Pro Tutorial
