5. Click Find.

Your search returns six records, for six members who paid their fees within the first six months of the year 2004.

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 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

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.

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

The completed record should look like this:

Create another record

1. Choose Records menu > New Record. 2. As you did for Jane Does record, enter data for John Smith,
who paid 75 dollars on 2/2/2004. Your database now contains two records. Click the book icon to see each record.
You have created a simple database and defined fields, and added and modified records. For more information, see chapters 1 and 2 in the FileMaker Pro Users Guide. You can also use or build on the database template files included with FileMaker Pro. For more information on entering data in different field types and by different methods, including import, see lesson 7 in this tutorial and chapters 1 and 4 in the FileMaker Pro Users Guide.
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: 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. Notice that although this is the same record in the same table, the
learn how layouts determine what you see add, resize, and move fields display values in a number field as currency add text to a layout, and change its size and color add graphics see where to find other ways you can customize a layout
data is displayed differently:
First Name and Last Name are side by side You see only five fields:
First Name, Last Name, City, Country, and Fee Paid
The amount in Fee Paid is prefaced with a currency symbol.
Customize your view in Layout mode
Look at the sample layouts
1. Open the Tutorial folder on your hard drive. 2. Open the Sample Files folder. 3. Open Sample05.fp7. 4. Choose Data Entry from the Layout pop-up menu if it is not already

selected.

You will make these changes to MyFile05 later in this lesson.
7. Choose View menu > Layout Mode.
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. If your file contains only one table, all layouts in your file will be based on that table. If your file contains more than one table, you must choose which table your layout will use at the time you create the layout, although you can change this later if necessary. The table you choose as the basis for your layout is important because it determines which fields and records are available for use on that layout. Fields and records that are not stored in the current table can still be used, but must be accessed through relationships. For more information on relationships, see lesson 10, Making databases relational.
Resize, move, and add a field

Select and resize a eld

1. In Layout mode, click the First Name field to select it.

Click to select

Click and drag the handle to resize
2. Drag the handle at the lower-right corner of the field to the left
About customizing layouts
Design layouts in Layout mode. You can include any combination of fields, text, and graphics on a layout. You can change the font and color of fields and text. You can also change the formatting of Number or Date fields. Changes you make in Layout mode affect only the way data appears. The data itself remains as it was entered. Tip Leave the sample file open to refer to as you build these features in the working file.
until the field is smaller, yet large enough to display the longest first name you expect in your file.
3. Choose View menu > Browse Mode. 4. Click Save to save changes to this layout, if prompted.
FileMaker Pro can save layout changes automatically, or prompt you to save changes before leaving Layout mode. To save layout changes automatically, select the Save layout changes automatically checkbox in the Save changes to this layout dialog box.

1. Choose Window menu > Sample05. 2. Choose View menu > Layout Mode and look at the header at the top
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 10. Enter these values in the Color dialog box:
Color Red Green Blue Value 170
of the layout. This is your model.
3. Open MyFile05.fp7 if it is not already open. 4. Choose View menu > Layout Mode. 5. Choose Fee Information from the Layout pop-up menu. 6. Select the Header tab to make that part of the layout active.
11. Do one of the following:
Windows: The Header label will become lighter in color when selected.
Windows: Click Add to Custom Colors, then OK. Mac OS: Click OK.
The header is now colored. Mac OS: The Header label will become darker in color when selected.
7. Choose the fill color palette.

Fill color palette

Add custom text to a layout
You will add the name of the organization to the top of the layout, then format the text.
Change text size and color
1. Click the Corporate Travelers Club text to select the text

block.

Add text
1. Choose Window menu > Sample05. 2. Choose View menu > Layout Mode and look at the heading at the 2. Choose Format menu > Text. 3. Choose Times from the Font list. 4. Choose Custom from the Size list, and type 28. 5. For Color, choose white or another color. 6. Click OK.
top of the layout. This is your model.
3. Open MyFile05.fp7 if it is not already open. 4. Choose View menu > Layout Mode. 5. Select the text tool.
Add a graphic to the layout
Add a logo beside the club name.
1. Choose Window menu > Sample05. 2. In the Fee Information layout, choose View menu > Layout Mode and notice where the logo appears on the layout. 3. Open MyFile05.fp7 if it is not already open.

8. Select the Columnar list/report layout again, then click Next.
9. Double-click each of the following fields in the order you want it

to appear in the list:

Create mailing labels
Look at the sample labels
1. Choose Window menu > Sample06. 2. Choose View menu > Browse Mode. 3. Select Labels in the Layout pop-up menu.
Last Name First Name Company Membership Type
These fields move to the Layout fields list.
You see the mailing label for the current record.
4. Choose View menu > Preview Mode.
You see a page of labels as it will print.
5. Notice that not all records have the same number of lines of text.
Records with four-line addresses display all lines. In records needing only three lines, the extra line is automatically omitted so there is no blank line in the labels.
6. Choose View menu > Layout Mode.
You see the names of fields that will appear on the labels.

Merge fields

10. Click Next. 11. Click Next again to accept the default sort order. 12. Select Standard from the list of layout themes, and click Next. 13. Click Next on each of the two remaining panels to accept the defaults. 14. Click Finish.

About label layouts

Label layouts use merge fields. Merge fields expand or contract to fit the data in the field, and take no space if the field is empty. Merge fields are for display and printing only. You cant enter data into merge fields.
The data in all records for the fields Last Name, First Name, Company, and Membership Type appears in the List of Members layout. The completed layout is displayed in Preview mode, which shows items as they will appear when printed.

Create a label layout

In this section, you will use the New Layout/Report assistant to:
create a label layout select fields to include on the labels
Creating lists, mailing labels, and form letters

Create the label

1. Choose Window menu > MyFile06. 2. Choose View menu > Layout Mode. 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. 8. Click Next.
5. Double-click Home Address 1 in the list, then press Enter
(Windows) or Return (Mac OS).
6. Double-click Home Address 2 in the list, then press Enter
7. Double-click City in the list. 8. Type a comma, then a space. 9. Double-click Country in the list.
Now you have set up the merge fields. Your label contents should look like this:
Now youre ready to specify the label contents.
Choose the fields that will appear on the labels
1. Double-click First Name in the list.

inches to the right. You see a heavy dotted line along the right side. This is the right-side page boundary. Your letter will be within these boundaries.
12. Scroll back all the way to the left. 13. Scroll up to the top of the layout.

Create the text block

You will create a text block with a 2 inch margin on all sides.

1. Click the text tool

Create a one-page blank layout
1. Choose Window menu > MyFile06. 2. Choose View menu > Layout Mode. 3. Choose Layouts menu > New Layout/Report. 4. For Layout Name, type Welcome Letter. 5. Select Blank layout for the new layout type. 6. Click Finish.
2. Drag a rectangle starting about 2 inches from the top left side of the layout, and ending about 2 inches from the bottom right side, as indicated by the page boundary lines.
When you release the mouse, the insertion point flashes in the top left of the text box. You will type the letter inside this box.
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 Add the address
1. Choose Insert menu > Merge Field. 2. Select First Name in the list and click OK. 3. Type a space. 4. Choose Insert menu > Merge Field. 5. Select Last Name in the list and click OK. 6. Move to the next line (enter a line space): 1. Click after the word company in the first paragraph. 2. Type a comma and a space. 3. Insert a merge field for the company name. 4. Type another comma.

Add the closing

1. Click at the end of the text and enter two line spaces. 2. Press tab several times to go to the center of the text block. 3. Type Sincerely, then enter four line spaces. 4. Press Tab several times to align the insertion point with Sincerely, and type John Jones. 5. Enter a line space, then press Tab again to align the cursor with the

Windows: Press the Enter key above the Shift key on the keyboard. Mac OS: Press Return.
7. Add merge fields for the street address, city, and country, with
appropriate spaces, new lines, and punctuation.
previous lines of text, then type President. Your layout should look like the letter in the sample file.
6. Choose View menu > Browse Mode to see your letter.

Type the letter

1. Press Enter (Windows) or Return (Mac OS) twice to create two
line spaces at the end of the address.
2. Start the salutation line: type Dear and a space. 3. Insert merge fields for the first and last name, separated by a space.
It should look like the letter in the sample file without the letterhead. See lesson 5 to learn how to add a logo.
7. When you are finished with this lesson, close each open file by
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 Users 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. In this lesson, you will:
4. Choose Data Entry from the Layout pop-up menu if it is not already
5. Locate the Membership Type field on the current record.
see how value lists can aid data entry write and test a calculation formula use a fields auto-entry options to automatically enter data
You see two radio buttons, one for New and one for Continuing. These correspond to the two choices in the Membership Types value list.
A value list with predefined values, displayed as radio buttons
6. Click one to enter the corresponding value in the field.
Enter data using value lists
Value lists let you choose the value you want from a scrolling list, a pop-up menu, checkboxes, or radio buttons. These lists can contain predefined values, or can be constructed dynamically based on the values in a particular field. They are an efficient way to enter frequently used values into your database.

Dene a value list

Automatically enter a serial number
FileMaker Pro lets you automatically enter certain types of datafor example, incremental numberswhen you create a new record.
Look at a serial number example
1. Choose Window menu > Sample07. 2. Go to the last record in the database.
The completed calculation
3. Locate the Member Number field, and note the current number of

this record.

4. Create a new record by choosing Records menu > New Record.
The value in the Member Number field of the new record is increased by one.

12. Click OK.

Note If you did the first activity in this chapter, you will notice that the value in Member Number is 32, even though there are now 31 records in the Sample07.fp7 database. FileMaker Pro continues to increment serial numbers even if intervening records have been deleted, such as record 31 in this case.
To learn how to change the position of the new field, see lesson 5.
11. When you are finished with this lesson, close each open file by
Create a eld for serial numbers
1. Choose Window menu > MyFile07. 2. Choose File menu > Define > Database. 3. In the Define Database dialog box, click the Fields tab if it is not already selected. 4. For Field Name, type Member Number. 5. Select Number as the field type. 6. Click Create. 7. Click Options. 8. Select Serial number on the Auto-Enter tab. You dont need to
You have learned how to simplify data entry by defining and formatting value lists, calculation fields, and auto-entered serial numbers. For more information on calculation fields and field auto-enter options, as well as defining and formatting value lists, see FileMaker Pro Help.
change the Generate choice, or the amounts for next value or increment by for this exercise.

Select Serial number

9. Click OK, then click OK again to close the dialog box.
The new field appears at the bottom of the layout. (You might need to scroll down to see it.)
10. Test your new field entry option by choosing Records menu >
New Record. Each time you create a new record, the value in the Member Number field increments by one.
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:

Create the button

1. Open the Tutorial folder. 2. Open the Working Files folder. 3. Open MyFile08.fp7. 4. Choose Data Entry from the Layout pop-up menu if it is not already
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
5. Select View menu > Layout Mode to switch to Layout mode. 6. Click the button tool

Specify a target layout for the script step
1. In MyFile08.fp7, choose Data Entry from the Layout pop-up menu. 2. Choose View menu > Layout Mode. 3. Double-click the button you created earlier in this lesson. 4. In the Specify Button dialog box, select the Perform Script script
7. From the list on the left, scroll down and select the Enter Preview
Mode script step, and move it over to your script.
step from the top of the list on the left.

The finished script

In the Options area, for Current Script click Specify. Select Preview Labels Layout from the list of available scripts, and click OK. Leave the rest of the default button settings as they are and click OK. If you have not already created this script, follow the steps described in Create a script to preview the Labels Layout on page 44.
8. Click OK, then click OK again to close the dialog box. 9. To run the script you have just created, choose Scripts menu >
Preview Labels Layout. FileMaker Pro displays the Labels layout and switches to Preview mode.
5. Click OK to save this button.
Rename the button 1. Click the text tool
2. Click the button once. The text insertion point appears in the 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. When you are finished with this lesson, close each open file by
In this lesson, you have created buttons and scripts, and used these two features together. For more information on creating buttons and scripts, see FileMaker Pro Help.
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. In this lesson you will: First, notice that you are in Preview mode. Preview mode is necessary to view summarized data on the screen. Second, notice how the members are grouped by the membership type, and further alphabetized by last name within each category.

2. Click Next. 3. Go to the Top center list in the Header area and select Large Custom Text. 4. Type Membership Type Report for the header of the report and click OK. 5. Go to the Bottom center list and select Page Number to number the pages in the reports footer.
This sorts the records in each group of membership types alphabetically by members last names. Notice that the break field Membership Type appears automatically at the top of the Sort order list to enable FileMaker Pro to group records by this category.
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. Click Next.

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 47.

Subtotals

Generate a report with grouped data and totals
In the previous exercise, you created a report that grouped data in categories based on common field values. In this exercise, you will create a similar report that also includes subtotals for each category and a grand total for the entire report.

This subsummary report:

Grand Total
groups the members by membership type subtotals the fees collected for each membership type
4. Click the Continue button to complete the script and return to the

Data Entry layout.

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. Two additional elements are also required:
2. Select and move the Last Name field. 3. Select and move the First Name field. 4. Select and move the Fee Paid field. 5. Click Next. 6. You want to organize the records by Membership Type. Move this field to the Report categories list. 7. Click Next. 8. Move the Last Name field to the Sort order list to sort the records
a grand summary layout part summary field(s) to display totals, averages, or counts

7. Click Add Grand Total to add the Membership Revenue field as the

click OK.

5. Go to the Bottom center list and select Page Number to number the
pages in the reports footer.
grand total at the end of the report. This tells FileMaker Pro to create a grand total of membership fees.
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
In this lesson, you generated reports with grouped data, and reports with grouped data and totals. For more information on creating reports, see FileMaker Pro Help.
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:
4. Choose the Companies layout from Layout pop-up menu, and
browse through the records. The Companies layout is based on the Companies table, and shows records from that table. The Companies table has three records, one record for each of the three companies that has members in the Corporate Travelers Club. Each company record also contains a portal that displays the club members from that company.
5. Choose the Corporate Account Information layout from the
see how relational databases work define a relationship display data from one related record display a list of related records
Layout pop-up menu. Corporate Account Information is based on the Members table, and shows the company information for each of the 30 members in the database.
6. In Corporate Account Information, create a new record for Jane Doe. 7. For Company, click the Company field and choose ABC Company.
The sample file in this lesson uses two tables: a Members table with records for individual Corporate Travelers Club members a Companies table for companies where these members work. Each company has many members. 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.
View data from one related record
See how relationships work in the sample les
1. Open the Tutorial folder on your hard drive. 2. Open the Sample Files folder. 3. Open Sample10.fp7.
8. Notice that the address for ABC Company automatically appears.

About related tables

The Companies table and Members table are related tables. 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. How does this happen? The Members table has a relationship to the Companies table. The relationship is based on matching data in the Company field in both tables. The Company field is the match field. The Members file displays the company address using related fields. You will learn both parts of this process: how to define the relationship and how to display data using the relationship.

Work with related records in the sample le
View a list of related records 1. Choose Window menu > Sample10.
2. Choose the Companies layout from the Layout pop-up menu. 3. Browse through the records.
Choose the table from the table pop-up menu
You see a list of members at each company. They are all individuals in the Members table.
Add to the list 1. Go to the ABC Company record.
2. Add a new member by clicking the first empty line under First Name.
5. Click ::Company Address, then click OK. 6. Repeat steps 3-5 for City and Country. 7. Move the fields if necessary, and make the fields large enough to
display all the data they contain. The fields should look like this:
Click here to enter a new member for this company
Displays a list of members from the related table
3. Type Joe, then press Tab and type Williams in the last name

field.

8. Choose View menu > Browse Mode and flip through the records to view company addresses from the Companies table in the Corporate Account Information layout that is based on the Members table.
View the new record in the related table 1. Go to the Data Entry layout, which is based on the Members table.
2. Go to the last record.
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.
2. Choose the Companies layout from the Layout pop-up menu.
Add a new record and view it in the list 1. In the Data Entry layout, create a record for a new member who works at ABC Company.
2. Now return to Companies layout, and look at the ABC Company
A relationship must always be defined before you can display data from related records. You will use the relationship to the Members table you defined earlier in this lesson. This relationship must be edited to allow the creation of related records.
3. Choose File menu > Define > Database, and click the Relationships tab, if it isnt already selected. 4. In the relationships graph, double-click the relational operator
record. You see the new member in the list (scroll down if necessary).
About working with lists of related records
In View data from one related record on page 55, a relationship between the Members and Companies tables displays data (company addresses) from the Companies table. Here, the same relationship displays data (member names) from the Members table. Relationships are bidirectional. There is more than one matching record in the related table because there is more than one member from each company. A portal in the Companies table displays all the members for each company. When you added Joe Williams to the list of members who work at ABC Company, the relationship automatically created a record for Joe Williams in the Members table. ABC Company is automatically entered into the match field because the name was added to the portal of the ABC Company record. A new member added to the Members table automatically appears at the end of the members list in the matching company record in the Companies table.

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.
how and when to back up your database where to learn about other ways to protect your files

When to back up

You should back up:
How to back up your database
There are many ways to back up a file. This is one way.
1. Determine the size of the database:
as often as necessary to be sure you can restore all of your data in an emergency. before you make a major or irreversible change like deleting records, deleting a field, or replacing data using the Import or Replace features. Remember, it takes far less time to back up a file than it does to recreate a lost or damaged file!
Windows: Right-click the icon for the file, then choose Properties. Mac OS: Click the icon for the file, then choose File menu > Get Info.
2. Identify a disk or volume that has enough available space for a

copy of the database.

3. Open the database you want to back up. 4. Choose File menu > Save a Copy As.
This lesson has shown you one way to protect a database. For other ways to prevent and overcome difficulties with your files, see FileMaker Pro Help.
Accept the default filename for your copy, or change the name Select the disk and folder where you want to save your copy
Save a copy of current file

Clients table

Client ID 002 Name Phone Andre Common 818-555-1234

Opening files

Important For information about opening les created in previous versions of FileMaker Pro, see Converting FileMaker Databases from Previous Versions. To open a file if FileMaker Pro isnt running, choose one of the following methods:
Double-click the FileMaker Pro file. Drag the file onto the FileMaker Pro application icon.

Products table

Prod ID Product Price Stock ET3 Travel bag $29.00 34
Double-click the FileMaker Pro application icon. In the New Database dialog box, select Open an existing file, then click OK, then double-click the filename. To open a file if FileMaker Pro is already running:
1. Choose File menu > Open. 2. Double-click the name of the file to open.

Invoices table

Client ID 002
If youre asked for an account name and password, type them in the Password dialog box, then click OK. To open a file that is being shared over a network:
1. Choose File menu > Open Remote. 2. In the Open Remote File dialog box, select View for a list of hosts.

Price $29 $32

Client Name Andre Common Phone Invoice ID 818-555-1234 12345
Prod ID Product ET3 Travel bag ET4 Travel book
Choose Favorite Hosts Local Hosts Hosts Listed by LDAP
To display Hosts you previously added as favorites FileMaker Pro hosts on your TCP/IP subnet FileMaker Pro hosts available through the specified LDAP server
To enter data in a database table, you make a new record and enter values into the elds that belong to that record. Values can be typed into a eld, imported from another application, or entered in a number of other ways.
3. To see the available files from a host, select the host. In the
Available Files list, select the file you want to open. Or, for Address, type the network path (for example, fmnet:/hostIPaddress/fileName).

4. Click Open.

Using databases
You might be required to enter an account name, password, and domain name (Windows only), depending on how FileMaker Network Sharing is configured on the host. To open a file as the host, see Enabling file sharing and hosting files on page 74. Note You can open a FileMaker Pro for Windows le on the Mac OS, and vice versa. If you intend to move les across platforms, its best to include the.fp7 extension in the lename. If you add or remove lename extensions, you must re-specify the le references for related les and les with external scripts.

Current record in View as List
To move from one record to another, use the book icon in the status area.
Book icon Slider Current record number Number of records in the database To move To the next record in the current table To the previous record in the current table Quickly to a specific record Do this Click the right page of the book icon. Click the left page of the book icon.
To select a related record in a portal, select the portal row (click inside the row but outside any elds in the row). For information about related records and portals, see chapter 3, Working with related tables and les.
1 Click the current record number, type the record 1
number you want, then press Enter (Windows) or Return (Mac OS). Press Esc, type the record number, then press Enter or Return.
To move Quickly through records
Do this Drag the slider right or left to move forward or backward.
Note If a le is locked or write-protected, or if you dont have access privileges to create records, FileMaker Pro will not add or duplicate records. If the eld is dened to automatically enter eld values, you see those values entered in the new record. See Dening automatic data entry on page 45. To enter or change values in the new record, see Entering and changing data in elds on page 22.
Through records in Use the scroll bar on the right side of the window. View as List or View as Table To a record with a unique value that you know See Finding records on page 23.
Adding and duplicating related records
If youre working with records in related tables, you can add records to a related table when you enter data in a record in the current table. Note You can only add related records from the current table if the relationship is defined to allow the creation of related records. If a related table is in a write-protected file, or if you dont have access privileges to create records, FileMaker Pro will not add or duplicate records. (See Creating relationships on page 68.)
To Add a record to a related table Do this in Browse mode
For more information about viewing records, see Help.
Adding and duplicating records
When you add or duplicate records, FileMaker Pro stores new records at the end of the current table. In Browse mode, you see the new record after the current record, or after the last record in the found set (the set of records made active by a find request).

hours hours and minutes hours, minutes, and seconds hours, minutes, seconds, and fractional seconds Separate hours, minutes, and seconds by a nonnumeric character like : (colon). Separate fractional seconds by a. (period). Type the time of day in 24- or 12-hour format, with or without AM or PM. (AM is assumed for a time less than 12:00.)
Insert a file in a container field For information, see Help. Add data to value lists (fields formatted as radio or option buttons, checkboxes, lists, or menus) For information, see Help.
Edit, format, or check the For information, see Help. spelling of text in a field or edit dictionaries

Finding records

To nd records, work in Find mode. You type criteria (the value or values to nd) into elds in a nd request, which looks like a blank record. When you perform a nd, FileMaker Pro searches through all the records in a table, comparing the critieria you specify with the data in the table. Records with data matching the criteria become the found set, which is the subset of records being browsed. Records that dont match are omitted. You can constrain (narrow) or extend (broaden) the found set. You can work with just the records in the found set. For example, you can view, edit, calculate summaries for, sort, print, delete, export, or replace data in these records. You can also open a new window in order to perform different nd requests on the same data. You cant perform a nd in summary elds, container elds, or elds dened with the global storage option.
Choose a layout with fields you want to search on Type Find criteria in fields
Click the arrow to see the operators Click to begin searching

Making a nd request

To make a find request:
1. Go to a layout that has fields you want to enter criteria in.
4. Click Find in the status area, or choose Requests menu > Perform
Find. You can do one or more of the following during or after performing a find request:

To Do this

You can change layouts and enter criteria on more than one layout.

The found set now consists of customers in New York and Hong Kong.
Finding some records while omitting others For example, to find vendors in the state of New York, except those in New York City:
1. In Find mode, type the criteria for the records to find (type New
York in the State field.)
2. Choose Requests menu > Add New Request.
3. Type criteria for the records to exclude (type New York in the

City field.)

4. Select Omit. 5. Click Find.
To restore a request to the way it was when you last committed it, choose Requests menu > Revert Request. Requests are committed, for example, when you click out of all elds, go to a different layout or request, or perform a nd.
Keep these points in mind:
Viewing, repeating, or changing the last nd
To view the most recent nd criteria, choose Records menu > Modify Last Find. Then, to return to the found set, choose View menu > Browse Mode. To repeat the nd, click Find. Or you can modify the nd criteria, then click Find.
You can have Omit criteria in more than one request.
FileMaker Pro works through the requests in the order you create them. For example, in a Clients database with clients in the US and France:
1 If the first request finds all clients in Paris and the second request
omits all clients in the USA, the found set contains all clients in Paris, France but none in Paris, Texas or anywhere else in the USA.
Hiding records from a found set and viewing hidden records
All records that are not in the found set are omitted, or hidden. You can omit additional records from the found set without doing a new nd. Important Omitted records are temporarily excluded from the found set. They still exist in the database.
To Omit a specific record Omit a series of records Do this Display or select the record to omit, then choose Records menu > Omit Record. Display or select the first record in a number of consecutive records to omit, then choose Records menu > Omit Multiple. In the Omit Multiple dialog box, type the number of records to omit, then click Omit. Choose Records menu > Show Omitted. Choose Records menu (Browse mode) or Requests menu (Find mode) > Show All Records.
1 If the order of the requests is reversed (the first request omits all
clients in the USA and the second request finds all clients in Paris), the found set includes all clients in Paris, France and in Paris, Texas, but no records for clients elsewhere in the USA. To omit a related record from a portal row (or set of related records), you must change the relationship or the value in at least one of the match fields so that the record is no longer related. For information about related fields, see chapter 3, Working with related tables and files.

The selected script steps and options are printed.
For more information about previewing and printing databases, see Help.
Automating tasks with scripts
Scripts can do simple tasks like setting print orientation or complex tasks like preparing a customized mailing to clients. Scripts can incorporate conditional decisions (if-else statements) and perform repetitive tasks (loop statements). You use the ScriptMaker feature in FileMaker Pro to build scripts by selecting from a list of FileMaker Pro script steps, specifying options, and arranging the steps in the correct order to perform the task. Use scripts to combine and automate tasks like:

Setting preferences

You can customize the appearance and behavior of FileMaker Pro to suit the way you work by changing standard settings called preferences. Preferences affect the behavior of the application and are not specic to any le. Settings in the File Options dialog box affect the current les default layout, opening and closing scripts, login information, and spelling. To set application preferences:
1. Windows: Choose Edit menu > Preferences.
Mac OS: Choose FileMaker Pro menu > Preferences.
2. In the Preferences dialog box, click a tab, then set the options you
switching to another layout or mode finding, sorting, and printing records importing data from the same source
want to use. To set file preferences:
1. Choose File menu > File Options. 2. In the File Options dialog box, click a tab, then set the options you
Procedures for creating scripts and a reference to all FileMaker Pro script steps (organized alphabetically or by category) are available in Help.
want to use. For more information about setting application and le preferences, see Help.
Backing up database files
Routine backups are imperative for any document stored on a computer. It is easier to restore a backed up database than it is to recreate it. How often you should back up depends on the amount of data you are adding to your databases, and how difcult it would be to recreate your les if they become corrupt. For very important les, its a good idea to routinely store backups at an off-site location. For an example of a backup script, see Help.
Chapter 2 Creating databases
plan a database dene and modify elds dene tables create layouts work with fields, objects, and parts on a layout

3. Choose Layouts menu > Layout Setup. 4. In the Layout Setup dialog box, click the Printing tab, select Print in
To change the width of columns: The initial width of each column is calculated based on the paper size, orientation, and any margins you have set. To change the width of columns, do one of the following:
<value> columns, select options for the columns, then click OK.
To Specify the number of columns Do this For Print in <value> columns, type a number between 1 and 99.
Interactively adjust the Drag the right column boundary (the vertical dashed width of the columns line at the right edge of the sample column). If you widen the column, you see a medium-gray area that indicates a gutter area that wont print. Precisely measure and Choose View menu > Object Size. Position the pointer adjust the width of the over the right column boundary and hold down the columns mouse button (be careful not to drag the column width). The column width is the second-to-last field in the Size palette.
Arrange records to flow Select Across first. across the page first (left to Use this option for reports like labels, to use the right a row at a time) fewest number of rows and preserve label stock. Arrange records to flow Select Down first. down the page first (top to Use this option for reports like directories, where bottom a column at a time) you read from top to bottom, column by column.
5. On the layout, you see vertical lines indicating columns. 6. Place or arrange fields and other layout objects so they are
contained within the sample column on the left. Use merge fields or fields or objects with sliding enabled to fit more data into the width of a column. With either of these two features, fields can extend into the gray area of the second column, and blank space in fields is eliminated when you view or print data.

Sample column

If you change the number of columns, FileMaker Pro adjusts the width of the columns to fit within the width of the paper size you have defined, not including fixed margins. You may need to readjust fields to fit within the sample column. For more information about setting up a layout to print records, see Help.

1. In Layout mode, select one or more timestamp fields, or
You see examples of the options you choose in the Sample area. File icons do not scale.
Choose how to fit the graphic in the frame Choose how to align the graphic horizontally Choose how to align the graphic vertically
calculation fields with a timestamp result.
2. To format the date component, choose Format menu > Date. 3. In the Date Format dialog box, select the formats you want to use

and click OK.

4. To format the time component (except the seconds portion),
choose Format menu > Time.
5. In the Time Format dialog box, select the formats you want to use
6. To format the seconds and fractional seconds component, choose

Format menu > Number.

7. In the Number Format dialog box, select the formats you want to
use and click OK. Only two options affect the display of the seconds component: Fixed number of decimal digits and Decimal separator. The remaining options have no effect. You see examples of the options you choose in the Sample area.
Formatting fields and text for vertical writing If your operating system is congured to support Japanese text entry, you can format eld objects and text objects that display Japanese text in vertical writing format. In vertical writing, characters are displayed and read from top-to-bottom, with successive columns displayed vertically from right-to-left. This is useful when printing addresses on labels.
When you select one or more elds, then choose Format menu > Orientation > Sideways (Asian text only), the characters are rotated 90 degrees counter-clockwise within the eld. The eld itself is not rotated. To rotate the selected elds, choose Arrange menu > Rotate. Depending on how you want the text to print, you may want to change the page orientation of the layout. To do so, choose File menu > Print Setup (Windows) or Page Setup (Mac OS), and change the page orientation to Landscape or Portrait.
Specifying formats for container fields You can control how FileMaker Pro displays images and file icons in container fields.
Note The following instructions also apply to formatting graphic objects that you insert onto a layout.
1. In Layout mode, select one or more container fields or inserted

graphics.

2. Choose Format menu > Graphic. 3. In the Graphic Format dialog box, select the formats you want to use.
Allowing or preventing entry into elds
You can allow or prevent entry into a field. For example, you can restrict entry into a field containing a serialized number, so the field cant be modified. You can set separate Browse mode and Find mode data entry options, which would allow you to permit finding serialized numbers even though they cant be entered. If you allow entry into a field, you can also control whether the entire contents of the field is selected when you click or tab into the field in Browse mode or Find mode. Otherwise, FileMaker Pro places the insertion point after the last character in the field.

See and work with data from another (or the current) table in its most up-to-date state. For example, display data in related fields when you need current data from a related table, such as the current price of an item. As data changes in the related records, you see those changes in the current table.

About relationships

A relationship is a powerful method for organizing your data. Using a relationship, you can join data in one or more tables based on common eld values, different eld values, or a comparison of values in two or more elds. After you create a relationship, you can do either of the following to display the data from the related table:
Set up and manage data efficiently and with flexibility. Instead of creating many database tables or files with duplicate values, you store single occurrences of values and use relationships to make those values available. You can then make changes to data in only one place, which eliminates data duplication and promotes data accuracy.
Save disk space, because data is stored in only one place.
Design a relational database, which is one or more tables that, when used together, contain all the data you need for your work. Each occurrence of data is stored in only one table at a time, but can be accessed and displayed from any related table. You can change any occurrence of your related data, and the changes appear in all places where that related data is used.
1 Define a lookup to copy data from a related table into a field in the
target table. The copied data is now stored in two places, just as if it were copied and pasted into a target field. Looked up data is current at the time it is copied, but once copied it remains static unless it is re-looked up or the lookup is triggered again.

Current table

Client ID Route R20 NY-Paris

Tour ID T10

Invoice No Date Client ID A-200-61 16-Oct-2005 C100 Williams
Tour Name NY-Roma Route ID Origin R200 NY R42 Paris

Related table

Destination Paris Roma
Tour ID Tour Name Cost $ T10 NY-Roma 550.00 T20 Roma-Istan 700.00 Total 1,250.00
Clients table In relational databases, data from the related table is only displayed in the current table, not copied Current table
Client ID Route R20 NY-Paris Client ID Client Name Phone C100 Williams 408-555-3456
Tour ID Tour Name T10 NY-Roma T20 Roma-Istan
Records from the LineItems table displayed in a portal

Lookups copy data from the related table into the current table
For example, a database for a travel agency might have these tables: a Tours table, which stores the products (tours) and their current prices; a Clients table, which stores client information; an Invoices table, which keeps a record of each invoice; and a LineItems tables, which stores sales data for each line of the invoice, including the tour being sold and the price at which it is sold. Because invoices are a mix of dynamic and static data, you use both relational databases and lookups to display your data. Records from the LineItems table are displayed dynamically, in a portal on the Invoices layout, but the actual sales price of each line item is entered using a lookup, so the invoice totals remain historically accurate, even if prices change at some future date.
You create a relational database by dening a relationship between two elds, called match elds. These elds can be in different tables or they can be in the same table (a self-join). You are able to access related data when the value in the match eld(s) on one side of the relationship compares successfully with a value in the match eld(s) on the other side of the relationship, according to the criteria you establish in the relationship. After you have created a relationship, you can use elds from the related table just as you would use any elds in the current table: to display data on a layout, as part of a calculation formula, in a script, as a match eld for another relationship, and so on. You can work with the data in the related elds in all modes (if you have access privileges) when you work with records in the current table. When you display related data in a portal, values from all related records are displayed. When the related eld isnt in a portal, the value from the rst related record is displayed. For more information about relationships, see Help.
Working with related tables and files
Relational database terminology
Before you begin working with relational databases and lookups, you should understand the following terms. These terms are explained in the sections that follow.

Ending Date E

01/01/2006
Self-joining relationships
A self-join is a relationship in which both match elds are dened in the same table. Dene a self-join to create relationships between records in the same table. Use a self-join in a portal on a layout of the current table to display a subset of data that is in the current table, such as all the employees of each manager. When you dene a self-joining relationship in the relationships graph, FileMaker Pro generates a second occurrence of the table upon which you are basing the self-join. FileMaker Pro does this to prevent the relationships graph from forming a cycle, which would make it impossible to evaluate the relationship.

Creating relationships

To create a relationship:
1. With the database open, choose File menu > Define > Database. 2. In the Define Database dialog box, click the Relationships tab. 3. In the relationships graph, locate the two tables you will be using
for this relationship. They can appear anywhere on the graph but they must be present on the graph in order to create the relationship. For self-joining relationships, locate the single table; FileMaker Pro will create the second table occurrence for you.
4. Click a field in one of the tables and drag to the corresponding
match field in the other table. Or, you can click to open the Edit Relationship dialog box and create a relationship.
Self-joining relationship
5. Repeat step 4 for each match field you intend to use in this
6. Click OK to save your changes and close the relationships graph.
relationship. For more information about match fields, see About match fields for relationships on page 64.
Move the pointer over the table header to display the names of the source table and source file
Click once to collapse all but the match fields. Click again to collapse all but the table name. Click again to return the table to its original size Click to change the color of selected tables Use these buttons to enlarge or reduce the view of the graph
Click to delete a relationship
Click to add a table Click to display the Print Setup (Windows) or Page Setup (Mac OS) dialog box
Click to create a relationship
Click to change an existing relationship
Use these buttons to change the alignment of selected tables
Click to show page breaks

Changing relationships

After you have created a relationship, you can add or change match elds, change relational operators, and set options for creating, deleting, and sorting related records. To change a relationship:
1. With the database open, choose File menu > Define > Database. 2. In the Define Database dialog box, click the Relationships tab.
3. In the relationships graph, locate the relationship to edit, and
double-click the indicator line between the related tables to open the Edit Relationship dialog box. Or, you can select the relationship in the graph and click the Edit Relationship dialog box. to open

= indicates a match field > indicates to import the field indicates to not import the field
About the importing process
The basic steps for importing records are:
1. Make a backup copy of the target file youre importing into. 2. If the source file is a FileMaker Pro file, perform a find so that the
found set in the current window contains only the records you want to import.
3. Open the target file, and if it has multiple tables, select a layout
that shows records from the table you want to import data into.
4. If youre updating existing or matching records, make sure the
found set in the target file contains only the records you want to change or update.
5. Choose File menu > Import Records > File, and choose the name of
the file that contains the data you want to import (the source file).
6. In the Import Field Mapping dialog box, if the file youre
importing from is a FileMaker Pro file and it contains multiple tables, select the table you want to import from.
7. Select the type of import to perform. See Methods of importing
data into an existing file on page 76.
8. Map the fields in the target file to the fields in the source file.
Fields in the source file For FileMaker Pro files containing multiple tables, select a table to import from
= indicates match fields Arrows show that data will import into the fields at right
4. If you see the First Row Option dialog box, choose whether the
first row of data contains field names or the first record of data, then click OK.
5. If youre converting a Microsoft Excel spreadsheet and the
Specify Excel Data dialog box appears, choose a worksheet or named range from the workbook file, then click OK.
Fields in the target file
6. In the Create a New File Named dialog box, type a name for the
new file, choose a location, then click Save. FileMaker Pro converts the data to a FileMaker Pro file and displays the file. For more information about importing data, see Help.
About the exporting process

Select an import action

Converting a data le to a new FileMaker Pro le

Create accounts for individuals when it is necessary to verify the identities of particular users and you want to manage access at an individual level.
1 Create shared accounts when you want fewer accounts to maintain
and you are less concerned about managing individual access to the database file. If you host files on FileMaker Server, you can create External Server accounts that obtain authentication information from an authentication server such as an Apple OpenDirectory or Windows Domain. For more information, see Creating accounts that authenticate via an external server on page 87. A privilege set specifies a level of access to a database file. Each database file contains three predefined privilege sets for common types of access levels: Full Access, Data Entry Only, and Read-Only Access. When you create a privilege set, there are many options available that you can use to limit database access, such as which layouts are viewable, which menus are available, and whether printing is permitted. Privilege sets can also restrict access to records or fields from particular tables within a file. Each account is assigned a privilege set, which determines the level of access when someone opens a file using that account.

Access via ODBC/JDBC

Access via Instant Web Publishing Access via FileMaker Mobile
When a user attempts to open or access a protected file using one of the above methods, the user will be prompted to provide account information. If the privilege set for the account does not permit the type of extended privilege access the user is requesting, the user will get an error indicating that they cannot access the file. All extended privileges for a file are disabled by default, even in the Full Access privilege set.
Enabling extended privileges only makes it allowable for certain privilege sets to access shared data. To actually access the shared data, you must also set up sharing for the type of access that you want. For more information on sharing data, see chapter 4, Sharing and exchanging data. For more information on extended privileges, see Viewing extended privileges on page 89.
Initially, the Guest account is inactive, which disables the guest option in the password dialog box and prohibits users from opening files as a guest. You can enable the Guest account to permit guest access. The Guest account is not fully editable. You cannot delete the Guest account, change the Guest account name, or assign it a password.

For more information about extended privileges, see Help.

Privileges tab.

Chapter 6 Converting FileMaker databases from previous versions
Because FileMaker Pro 7 uses a new le format, you must convert FileMaker Pro les created in earlier versions to the new le format. The conversion process makes it possible for you to use your previous les with FileMaker Pro 7, but its important to note that: Manual corrections may be necessary. You might need to correct items in the converted file that did not convert properly. In some cases, you may need to correct items in the original file and then convert the file again. After you convert any file, you should review the Conversion.log file for items that may need to be corrected. You should test the converted file. The Conversion.log file may not indicate every item in the file that needs to be corrected. Before you begin using a converted database solution, you should test it thoroughly to make sure every aspect has converted successfully. For example, make sure every script works as you expect, and that accounts and privilege sets provide the required file security. You may want to migrate files to FileMaker Pro 7. When you convert a multi-file relational database, the converted files arent optimized for use with FileMaker Pro 7. The files are still related, but they are not combined into a single file with multiple, related tables, which may be desirable if you want to make better use of the new features in FileMaker Pro 7. You may want to go a step beyond conversion and migrate your files to FileMaker Pro 7, which you can do by either manually combining your files after conversion, or recreating your database in FileMaker Pro 7 and then importing the data from the original files. This chapter describes the basics of le conversion. It includes:
information on how to view the Conversion.log file after converting one or more files a summary of the top conversion issues you may encounter
If youre converting complex or business-critical databases, refer to the more detailed manual, Converting FileMaker Databases from Previous Versions, which is included with FileMaker Pro in PDF format. See Using FileMaker Pro documentation on page 7 for more information.

Conversion overview

If your database solution is fairly simple, you should be able to convert the files, review the Conversion.log file, test your converted files, and use them. For example, simple files that were built from the templates provided with your previous version of FileMaker Pro should convert accurately without a lot of manual corrections afterwards. If youre converting a multi-file relational database created with custom features such as custom scripts, or a database thats businesscritical, you should plan your conversion more carefully. The first chapter in Converting FileMaker Databases from Previous Versions contains a conversion checklist for complex databases that you can follow. FileMaker Pro 7 can convert files created with versions 3.x, 4.x, 5.x, and 6.x. If you have files created using FileMaker Pro version 2.x or earlier, you must first convert them to one of the supported formats listed above. Then you can convert the files to FileMaker Pro 7. For more information, see Converting FileMaker Pro 1.x and 2.x databases on page 96.