FileMaker Pro 8 ® User's Guide © 1995-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. 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 Preface Introducing FileMaker Pro Using this manual Using FileMaker Pro documentation Where to find PDF documentation Online Help Templates, examples, and more information Suggested reading FileMaker Pro overview Creating simple or complex databases Using layouts to display, enter, and print data Finding, sorting, and replacing data Protecting databases with accounts and privilege sets Creating scripts to automate tasks Sharing and exchanging data Saving and sending databases in other formats New features Information sharing features Usability improvements Ensure data accuracy Functions and script steps 7 7 7 7 8 8 8 9 9 9 10 10 10 11 11 11 11 12 12 12 Viewing records Viewing records in table format Selecting the active record Moving through records Adding and duplicating records Adding and duplicating related records Deleting records Entering data Selecting a field Entering and changing data in fields Finding records Making a find request Performing quick finds based on data in one field Finding text and characters Finding numbers, dates, times, and timestamps Finding ranges of information Finding data in related fields Finding empty or non-empty fields Finding duplicate values Finding records that match multiple criteria Finding records except those matching criteria Deleting and reverting requests Viewing, repeating, or changing the last find Hiding records from a found set and viewing hidden records Finding and replacing data Sorting records 18 18 19 20 20 21 21 22 22 22 23 24 25 25 26 28 29 29 29 29 30 32 32 32 32 33 Chapter 1 Using databases About database fields and records Opening files Opening multiple windows per file Closing windows and files Saving files When FileMaker Pro saves a file About modes 13 13 14 15 16 16 17 17 4 FileMaker Pro User's Guide Previewing and printing databases Previewing data on a layout Printing records Printing scripts Printing table and field information Printing the relationships graph Automating tasks with scripts Backing up database files Setting preferences 34 35 35 36 37 37 37 37 38 Working with fields on a layout Placing and removing fields on a layout Setting up checkbox sets and other controls on a layout Deciding where to place related fields About merge fields Formatting field data on a layout Allowing or preventing entry into fields Working with parts on a layout About layout part types 60 60 62 63 63 63 67 67 68 Chapter 2 Creating databases Planning a database Creating a FileMaker Pro file Defining database fields About naming fields About choosing a field type Defining text, number, date, time, timestamp, or container fields Defining calculation fields Defining summary fields Setting options for fields Defining automatic data entry Defining field validation About storage and indexing options Defining database tables Creating layouts and reports About layout types Setting up a layout to print records in columns Working with objects on a layout Selecting objects 39 39 42 43 44 44 45 46 48 49 50 50 52 52 53 55 57 58 59 Chapter 3 Working with related tables and files About relationships Relational database terminology About the relationships graph About match fields for relationships Types of relationships Planning a relational database Working with the relationships graph Creating relationships Changing relationships Summarizing data in portals About lookups Creating lookups Editing lookups 69 69 71 72 72 72 76 76 76 78 79 79 79 80 Chapter 4 Sharing and exchanging data Sharing databases on a network Enabling file sharing and hosting files Opening shared files as a client Opening shared databases remotely using a URL Closing a hosted file 81 81 82 83 84 84 | Importing and exporting data Saving and sending data in other formats Sending email messages based on record data Supported import/export file formats ODBC and JDBC Methods of importing data into an existing file About adding records About updating existing records About updating matching records About the importing process Converting a data file to a new FileMaker Pro file About the exporting process Publishing databases on the web 84 84 85 85 85 85 85 86 86 86 87 88 88 Contents 5 Chapter 6 Converting FileMaker databases from previous versions Converting FileMaker Pro 6 and earlier files Conversion overview Converting a single file Converting multiple files at once Reading the Conversion.log file Solving basic conversion problems Converting FileMaker Pro 1.x and 2.x databases Top conversion issues Passwords Preserving user dictionary information Print Setup script step stored setup options (Windows) Switching windows in scripts Boolean functions and find requests File references Chapter 5 Protecting databases with accounts and privilege sets Protecting databases Accounts, privilege sets, and extended privileges About the predefined accounts About the predefined privilege sets Planning security for a file Password-protecting a file Creating accounts and privilege sets Viewing accounts and privilege sets Creating accounts Creating accounts that authenticate via an external server Creating privilege sets About enabling record-level access Viewing extended privileges 91 91 92 93 93 94 95 95 96 96 97 98 99 99 101 101 102 102 103 105 106 106 107 107 107 107 108 108 108 109 Index 6 FileMaker Pro User's Guide Preface Introducing FileMaker Pro FileMaker® Pro is the leading workgroup database software for quickly creating and sharing solutions adapted to your business needs. Powerful features, broad platform support, and an easy-to-use interface make FileMaker Pro indispensable for anyone who needs to track and manage people, projects, and information. 1 Installing FileMaker ODBC and JDBC Client Drivers: describes how to install the drivers needed to access a FileMaker data source via ODBC and JDBC 1 FileMaker ODBC and JDBC Developer's Guide: describes concepts and details to help you share FileMaker data with other applications, using ODBC and JDBC Where to find PDF documentation Most PDF manuals are located in the folder where you installed FileMaker Pro. If you installed FileMaker Pro in the default folder location, the PDF manuals are located here: Windows: C:\Program Files\FileMaker\FileMaker Pro 8\English Extras\Electronic Documentation Mac OS: Macintosh HD/Applications/FileMaker Pro 8/English Extras/Electronic Documentation PDF manuals for ODBC and JDBC are on the FileMaker Pro CD, in the /xDBC/Electronic Documentation folder. To view the PDF files, you need a PDF reader. In Mac OS X, you can use either the built-in Preview application or Adobe® Reader®. Windows users need Adobe Reader. If you do not have Adobe Reader, you can download it from the Adobe web site at www.adobe.com. Important You can download PDFs of FileMaker documentation (and any updates) by choosing Help menu > Downloads and Updates. All of the PDF files use the tagged Adobe Portable Document format (PDF). Tagged PDF files work with assistive technology such as the screen readers JAWS for Windows and Window-Eyes. For more information about tagged PDF files, see the Adobe web site at www.adobe.com. Using this manual This User's Guide contains an introduction to FileMaker Pro features and provides step-by-step instructions on the most common FileMaker Pro tasks. You may need to refer to FileMaker Pro Help for detailed information on more advanced product features. Using FileMaker Pro documentation The FileMaker Pro documentation provides a learning path to productivity, installation instructions, online Help, and more. The following manuals are included: 1 FileMaker Pro User's Guide (this manual): contains key concepts and basic procedures 1 Installation Guide for FileMaker Pro and FileMaker Pro Advanced: contains installation instructions FileMaker Instant Web Publishing Guide: describes how to make FileMaker Pro databases accessible to web browser users over an intranet or the Internet Customizing Starter Solutions: describes how to customize the included database template files for your own needs 1 1 8 FileMaker Pro User's Guide Online Help FileMaker Pro Help provides comprehensive step-by-step instructions on FileMaker Pro features. You can open Help from the Help menu in FileMaker Pro. There are three ways to access Help: Suggested reading Here are some suggestions for using the documentation to help you learn how to do specific tasks in FileMaker Pro. Type of user Data entry person Small business owner or educator who creates a basic database to start, then improves it over time Database administrator Suggested reading Chapter 1, "Using databases," in this guide Chapter 2, "Creating databases," in this guide. As features are added to the database, refer to Help 1 1 1 Browse the Help table of contents to locate topics by subject. Search for particular topics by entering keywords. Get information about the currently displayed dialog box by pressing F1 (Windows) or 2-? (Mac OS). Templates, examples, and more information FileMaker Pro includes database template files (Starter Solutions) that you can use to quickly get started. When you start creating databases, check the list of templates to see if one is available that suits your needs. The English Extras folder contains references and examples to help you with specific features such as XML support. You can find more information, tips, and database ideas on the FileMaker web site at www.filemaker.com. This guide Help, for information on advanced topics FileMaker Instant Web Publishing Guide New FileMaker Pro Installation Guide for FileMaker Pro and FileMaker Pro user Advanced This guide User upgrading from a previous version Installation Guide for FileMaker Pro and FileMaker Pro Advanced Chapter 6, "Converting FileMaker databases from previous versions" in this guide Help Note If your database was developed using FileMaker Pro Advanced, you may see customized menus or tooltips for layout objects. For an overview of these features, see Help. Preface | Introducing FileMaker Pro 9 FileMaker Pro overview Here is an overview of the capabilities of FileMaker Pro. Using layouts to display, enter, and print data FileMaker Pro layouts determine how information is organized for viewing, printing, reporting, finding, and entering data. Layouts do not store your data--they just display it. Database files can have many different layouts, which display data in a variety of ways. Within one database file, you can design separate layouts for entering data, reporting summaries, printing mailing labels, publishing a database on the web, and so on. You can change a layout's design without affecting the data or other layouts for the file. When you change the data in a field, the changes are reflected in the same field on all the layouts in the database. In a layout, you choose, arrange, and modify fields and field labels, create reports, add buttons to initiate scripts, add graphics and text, and specify printing options. There are six types of layouts and one view that you can create with the New Layout/Report assistant. Use these different types for various purposes, like displaying a data entry screen or printing a totaled sales report or mailing labels. You can customize each layout further by using the design tools that are available in Layout mode. Creating simple or complex databases With FileMaker Pro you can create simple databases consisting of just one database table, such as an address list, or you can create a complex database with relationships and multiple database tables. For example, you can create a complex database that relates two tables based on a single common value, such as a serial number or an ID number. An address list database consisting of one table A database consisting of multiple tables, with a portal to display records from a related table Steve Williams 789 Ninth Avenue New York, NY 10001 You can organize your databases as a single table in a single file, as multiple tables in a single file, or as multiple tables in multiple files. FileMaker Pro provides database templates designed for business, education, and personal purposes. The templates can be quickly customized to suit your needs. For more information about creating databases, see chapter 2, "Creating databases." 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 Dear Sophie, Thank you for reserving a cruise with us. We value your business and appreciate the opportunity to serve you. 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 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 The same data can be arranged differently with multiple layouts 10 FileMaker Pro User's Guide For more information about creating layouts, see "Creating layouts and reports" on page 53. Protecting databases with accounts and privilege sets You can restrict what users see and do in a database file by defining accounts and privilege sets. Accounts authenticate users who are attempting to open a protected file. Each account specifies an account name and (usually) a password. Any user that cannot specify valid account information won't be able to open a protected file. A privilege set specifies a level of access to a database file. Each account is assigned a privilege set, which determines the level of access when someone opens a file using that account. Extended privileges determine the data sharing options that are permitted by a privilege set, such as whether a privilege set permits users to open a shared file or view a database in a web browser. For more information about accounts and privilege sets, see chapter 5, "Protecting databases with accounts and privilege sets." Finding, sorting, and replacing data Features for finding, sorting, and replacing data allow you to manipulate your data in many ways. You can find records that meet criteria you specify, such as records entered on a certain date or addresses in a particular city. You can open another window in order to perform different searches on the same data. You can rearrange records in order to view or print them in a different sequence. You can sort fields alphabetically, numerically, by date, or by a custom sort order. Creating scripts to automate tasks Scripts can do simple tasks like setting print orientation or complex tasks like preparing a customized mailing to clients. For example, you could define a script that creates a thank you letter to clients in your clients database who have made a purchase in the last week. The script composes a letter customized to each client. The script then switches to Preview mode and pauses so that you can see what the letter looks like before it prints. The whole task is started by clicking a button on a layout. You use the ScriptMakerTM 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. For more information about scripts, see "Automating tasks with scripts" on page 37. You can search for and replace data across multiple fields, similar to the way you find and replace data in word processing applications. For more information about finding, sorting, and replacing data, see "Finding records" on page 23, "Sorting records" on page 33, and "Finding and replacing data" on page 32. Preface | Introducing FileMaker Pro 11 Sharing and exchanging data A benefit of storing data in FileMaker Pro is sharing your data with others. You can share your data with other FileMaker Pro users on a network, use the web publishing capabilities to share data over the web, or use ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) to share FileMaker Pro files with ODBC- and JDBC-compliant applications. If your computer is connected to a network, you and other Windows and Mac OS FileMaker Pro users can use the same database file simultaneously. FileMaker Pro Network Sharing supports the sharing of files with up to five concurrent users. Note If you need to share files with more than five concurrent users on a network, FileMaker recommends the use of FileMaker Server. You can quickly and easily publish FileMaker Pro layouts as web pages, which allows anyone with a web browser on your intranet (or the Internet) to access your FileMaker Pro databases. With FileMaker Pro accounts and privilege sets, you can make your web databases secure. Using SQL (Structured Query Language) queries, you can get data from FileMaker Pro files to create charts, generate reports, and analyze your data with ODBC- and JDBC-compliant software applications. You can also use FileMaker Pro as an ODBC client application, accessing data sources such as Microsoft Access or Oracle databases. For more information about using, and the limitations of using, ODBC and JDBC with FileMaker Pro, see Help. For more information about sharing and exchanging data, see chapter 4, "Sharing and exchanging data." Saving and sending databases in other formats FileMaker Pro lets you save data in different file formats, such as Microsoft Excel worksheets and Adobe PDF files, so you can give the files to users who do not have FileMaker Pro. You can automatically email the files after saving them. For more information, see "Saving and sending data in other formats" on page 84. New features Information sharing features For more information on this feature Saving data in Adobe PDF and Microsoft Excel format New command to send emails Importing data from another file into a new table in the current file. This allows you to consolidate data previously stored in separate databases. Export data in Excel format (.xls) See "Saving and sending data in other formats" on page 84 "Sending email messages based on record data" on page 85 "Methods of importing data into an existing file" on page 85 "About the exporting process" on page 88 12 FileMaker Pro User's Guide Usability improvements For more information on this feature New tool for creating and managing tab controls (tabbed panels) on layouts New Field/Control Setup dialog box makes it easier to create and edit checkbox sets and other controls that display data from a value list See "Creating layouts and reports" on page 53 "Setting up checkbox sets and other controls on a layout" on page 62 Ensure data accuracy For more information on this feature New auto-complete capability for text fields Visual spell checking New drop-down calendar style for data entry See "Entering and changing data in fields" on page 22 Help "Setting up checkbox sets and other controls on a layout" on page 62 New scroll wheel support for Windows and the Help Mac OS Adding text notes to tables in the relationships "Working with the relationships graph graph" on page 76 Support for variables in file paths Help Functions and script steps For more information on this feature New function to return a value from a script New path functions for calculating a file's location and name Setting a variable to a specified value in a script or calculation expression New Get, Logical, Text, and Text Formatting functions Enhancements to the Go to Related Record script step Exporting records in Microsoft Excel format using the Export Records script step See Help Help Help Help Help Help By default, fields on the current layout are Help listed first when you sort records, replace field contents, export records, or send email Improved database templates, called Starter Solutions Improved Instant Web Publishing. For example, create and use your own home page instead of the default Database Homepage New menu commands to navigate through records and layouts Performing finds in Browse mode using shortcut menus The Customizing Starter Solutions guide "Publishing databases on the web" on page 88 Help "Performing quick finds based on data in one field" on page 25 and "Finding records that match multiple criteria" on page 29 Restricting the Windows script step selections Help to the windows in the current file New Save Records as Excel and Save Records Help as PDF script steps Sending single and multiple email messages with the Send Mail script step Help New tools to align objects in Layout mode and Help in the relationships graph Chapter 1 Using databases This chapter explains the basics of how to: 1 1 1 1 1 1 1 1 1 open, close, and save files create records in a database add and edit data in fields find records using different criteria omit records from the found set find and replace data sort data You can also view a subset of your data, for example, only invoices for a particular month. A database doesn't just hold information--you use a database to organize and analyze information so that you understand its significance. B C Records print data, scripts, table and field information, and the relationships graph use scripts Michelle Cannon 123 4th St. SW Edmonton CAN N4V 1LN (717) 555-0011 Your address book is like a database Fields Note See Help for detailed, comprehensive information and step-bystep procedures about using FileMaker Pro. About database fields and records A database is a collection of information, or data, that you can organize, update, sort, search through, and print as needed. Your own address book or filing cabinet is an example of a simple database. With the address book and filing cabinet, you store similar pieces of information organized for easy retrieval. With a filing cabinet, you must choose a way to sort your data, for example, alphabetically by last name or by region. By storing contacts and addresses in FileMaker Pro, you can organize your information in many ways. You can sort your information by country, city, last name, or even sort by all three of these fields. An invoicing system is an example of a more complex database, with multiple related tables, multiple layouts, and calculated fields. Fields are the basis of a database table. Fields are used to store, calculate, and display the data you enter into a record. Each piece of information in a record--like name, ID number, and telephone number--is stored in a field. A database table contains one or more records. Each record contains fields that hold similar information about one subject or activity. For example, the fields on a record in the Clients table contain address information for one client. A database can consist of one file with one table, which might hold the names, addresses, and telephone and fax numbers of all your clients. 14 FileMaker Pro User's Guide A database can also consist of several files, each of which can contain one or more tables that, together, contain all the information about related topics (sometimes called a database system). Using the relational capabilities of FileMaker Pro, you can join information between files and tables to create a relational database. For example, one table can show you which clients bought a particular item and a related table can show how much they paid. Clients table Client ID 002 Name Phone Andre Common 818-555-1234 Opening files To open a file if FileMaker Pro isn't running, choose one of the following methods: 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. 1 1 1 Double-click the FileMaker Pro file. Drag the file onto the FileMaker Pro application icon. Invoices table Client ID 002 If you're 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 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 fields that belong to that record. Values can be typed into a field, imported from another application, or entered in a number of other ways. Hosts Listed by LDAP Chapter 1 | Using databases 15 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). The original database window, in which you can search the records 4. Click Open. 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 and share a file, see "Enabling file sharing and hosting files" on page 82. Note You can open a FileMaker Pro for Windows file on the Mac OS, and vice versa. If you intend to move files across platforms, it's best to include the .fp7 extension in the filename. If you add or remove filename extensions, you must re-specify the file references for related files and files with external scripts. A second window, in which you can perform a different search on the same found set Opening multiple windows per file You can open more than one window per file. This allows you to view the same layout in separate windows, or simultaneously view different layouts in the same file. You can perform find or sort operations independently in the different windows. To open an additional window, choose Window menu > New Window. Another window appears. Initially, the window displays the same layout, sort order, and found set as the previously active window. To view a different layout in the new window, choose a layout from the Layout pop-up menu in the status area. You can also choose a layout by choosing View menu > Go to Layout. For information on finding records, see "Finding records" on page 23. For information on sorting records, see "Sorting records" on page 33. For more information about opening multiple windows, see Help. 16 FileMaker Pro User's Guide Closing windows and files You can close FileMaker Pro windows and files as you would in most Windows and Mac OS applications. To close a window, do one of the following: Important Always save a copy before you make extensive changes. You can't restore data after you delete a field from the Define Database dialog box or remove records using the Delete Record or Delete All commands. To save a copy of your file: 1. Make the file you want to save the active file, and choose File menu > Save a Copy As. 2. Navigate to where you want to save the copy on your hard disk. 3. For File name (Windows) or Save As (Mac OS), type a filename. 4. For Save as type, choose FileMaker Files (Windows only). 5. For Save a (Windows) or Type (Mac OS), choose one of the following options: Choose Copy of current file To Duplicate the entire file, including the tables, field definitions, layouts, scripts, page setup options, and data. Duplicating a file is useful before you make extensive changes. Compact the entire file into less disk space by reclaiming unused space in the file. Saving a compacted copy is useful if you edit many records frequently. Save a file's tables, field definitions, layouts, scripts, and page setup options, without the data. Make a clone when you want a template of the file. 1 1 1 1 Windows: Double-click the document icon in the upper left corner of the document window. Windows: Click the close box in the upper right corner of the document window. Mac OS: Click the red close button in the upper left corner of the document window. Choose File menu > Close. To close a file, use the procedures above to close all the open windows for a file. Note Even if you close a file, it may remain open as a hidden file if the window of any other file is displaying data from that file. (For example, another window may be displaying related data from the file you attempted to close.) FileMaker Pro will close the file when you close all the dependent windows. Compacted copy (smaller) Clone (no records) Saving files FileMaker Pro saves the changes you make to your file as you work. You can save a copy of your file as a backup or save a clone of your file with no data. To save a copy of your file in a non-FileMaker format, see "Saving and sending data in other formats" on page 84. Some minor changes to files are not always saved with the file. For details, see the next section, "When FileMaker Pro saves a file." 6. Click Save. Chapter 1 | Using databases 17 When FileMaker Pro saves a file In FileMaker Pro, the following actions do not cause the file to be saved: Mode tabs 1 1 1 1 1 1 Displaying a different record or layout Sorting records or modifying a sort order Performing find operations such as Find, Show All Records, Omit Record, or modifying a find request Status area Changing the position and size of windows or hiding/showing the status area Changing layout object default settings Changing ruler display settings Mode pop-up menu If you perform any of the above actions without also performing an action that does cause a save (such as modifying data), the change won't be saved when you close the file. FileMaker Pro considers the above actions to be minor changes, and they are not saved with the file unless the file is also modified in a more significant way. For example, if you open a file, perform a find, then close the file, FileMaker Pro does not save the found set with the file. However, if you open a file, add a new record, perform a find, then close the file, FileMaker Pro will save the found set as well as the new record with the file. For more information about saving files, see Help. Use this mode To Browse mode Work with the data in a file. You can add, view, change, sort, omit (hide), and delete records. Browse mode is where you'll do most of your work, such as data entry. Search for particular records that match a set of criteria. You can then work with this subset of your records, called the found set. After you find a group of records, FileMaker Pro returns to Browse mode so you can begin working with them. Determine how information is presented on your screen or in printed reports, labels, and forms. Design layouts for specific tasks, like data entry or printing invoices or envelopes. Select and arrange fields and other objects to modify an existing layout or create new layouts. See how data in forms or reports will look before you print. Preview mode is especially useful for viewing layouts with multiple columns (like mailing labels) and reports with summaries. Find mode Layout mode About modes You work with data in FileMaker Pro in Browse, Find, Layout, or Preview mode. You can choose a mode from the View menu, the Mode tabs in the status area, or from the mode pop-up menu at the bottom of the document window. Preview mode 18 FileMaker Pro User's Guide Viewing records FileMaker Pro provides three views of each layout: form, list and table. When you change views, you temporarily change the way records display or print. To view records Individually In a list In a spreadsheet-like table Do this in Browse mode or Find mode Choose View menu > View as Form. Choose View menu > View as List. Choose View menu > View as Table. Viewing records in table format While viewing records in table format, you can make one or more of the following changes to columns and records: To Reorder columns Resize a column Do this Click a column heading and drag it to a new location. Move the pointer to the edge of the column heading. When the pointer changes to a double arrow ( ), drag it to the desired size. Select one or more columns, then right-click (Windows) or Control-click (Mac OS). Choose Set Column Width from the shortcut menu. In the dialog box, type a width, choose units from the list, then click OK. Set a precise column width The following illustration shows the same layout in three views. View as Form Sort records One record 1 1 Click a column heading. Select one or more columns, right-click (Windows) or Control-click (Mac OS), then choose a sort order from the shortcut menu. If you select multiple columns, the order of the columns (left to right) determines the sort order. Field labels View as List One record Note You must have Sort data when selecting column selected in the Table View Properties dialog box. (In Layout mode, select this option by right-clicking (Windows) or Control-clicking (Mac OS), then choosing Layout Setup from the shortcut menu. In the Layout Setup dialog box, click the Views tab, then click Properties.) View as Table One record Chapter 1 | Using databases 19 Column headings. Each column displays one field from the current layout. If you've added a tab control, fields from all panels are displayed. Current record in View as List View as Table To select a related record in a portal, select the portal row (click inside the row but outside any fields in the row). For information about related records and portals, see chapter 3, "Working with related tables and files." Selecting the active record In Browse mode, a table has one active (selected) record at a time for each window. For more information about working with multiple windows, see "Opening multiple windows per file" on page 15. In this view View as Form View as List or View as Table The active record is The record that you're displaying. The record that's marked with a solid bar along the left side. To work with another record, click in the record to make it active. Portal Click inside the portal row to select a related record 20 FileMaker Pro User's Guide Moving through records To move from one record to another, use the book icon in the status area. 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). Book icon Slider Current record number Number of records in the database To move Do this 1 1 If records are sorted, the new record appears immediately after the current record. If records are unsorted, the new record appears after the last record in the found set. For more information, see "Sorting records" on page 33. To Add a new blank record Do this in Browse mode Choose Records menu > New Record. You see a blank record with one field selected. To the next record in the Click the right page of the book icon or choose current table Records menu > Go to Record > Next. To the previous record in Click the left page of the book icon or choose Records the current table menu > Go to Record > Previous. Quickly to a specific record Quickly add a record Select the record to duplicate. Then, choose Records with the same or similar menu > Duplicate Record. (See "Selecting the active data as an existing record record" on page 19.) Add or duplicate related records See the next section. 1 1 1 Click the current record number, type the record number you want, then press Enter (Windows) or Return (Mac OS). Press Esc, type the record number, then press Enter or Return. Choose Records menu > Go to Record > Specify, then type the number of the record. Note If a file is locked or write-protected, or if you don't have access privileges to create records, FileMaker Pro will not add or duplicate records. If the field is defined to automatically enter field values, you see those values entered in the new record. See "Defining automatic data entry" on page 50. To enter or change values in the new record, see "Entering and changing data in fields" on page 22. Quickly through records Drag the slider right or left to move forward or backward. Through records in View Use the scroll bar on the right side of the window. as List or View as Table To a record with a unique See "Finding records" on page 23. value that you know You can also use a mouse scroll wheel to move through records. For more information, see Help. For more information about viewing records, see Help. Chapter 1 | Using databases 21 Adding and duplicating related records If you're 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 don't have access privileges to create records, FileMaker Pro will not add or duplicate records. (See "Creating relationships" on page 76.) To Add a record to a related table Do this in Browse mode Deleting records When you delete a record, you permanently discard the data in all the fields in that record. Important You can't retrieve deleted data, and you can't undo the action of deleting records. Before you delete records, consider making a backup copy of your file. If you're working in a relational database and the option to delete related records is selected in the Edit Relationship dialog box, FileMaker Pro deletes related records when you delete a record. To delete One record A related record Do this in Browse mode Select the record to delete. Choose Records menu > Delete Record, then click Delete. Select a portal row in the current table by clicking inside the row but outside any fields in the row. Choose Records menu > Delete Record, then click Delete. Note To delete related records, Allow deletion of portal records must be selected in the Portal Setup dialog box. A group of records Make sure the found set contains only the records you want to delete. (See "Finding records" on page 23.) Choose Records menu > Delete Found Records, then click Delete. 1 1 If the related field is in a portal, type data into the field in the last (empty) row of the portal, then press Enter. If the related field isn't in a portal, type data into the field, then press Enter. Duplicate a record that is Be sure no records in a portal are selected, then displaying related data choose Records menu > Duplicate Record. Duplicate a related record in a portal Select the record in the portal, then choose Records menu > Duplicate Record. All records in a database Choose Records menu > Show All Records, then choose Records menu > Delete All Records. Portal displaying related records from the Line Items table Type in the last row of the portal to add a related record to the Line Items table For more information about adding and duplicating records, see Help. 22 FileMaker Pro User's Guide Entering data Note If a file is locked or write-protected, or if you don't have privileges to edit certain records or fields, you may not be able to modify data. Entering and changing data in fields You can enter or import data up to the character limit for the field type. You can view and print only the data that fits within the field boundaries. To Enter data in a text field Do this in Browse or Find mode Click in the field. Select the field, then choose Edit menu > Select All. Enter data in a number field Enter data in a date field Click in the field in Browse mode, then Type the text (up to 1 billion characters). Note In a field set up to auto-complete, you see suggestions based on what you type. Type the values (from 10400 to 10-400). Type one date on one line in the field. Type the day, month, and year as numbers (for example, 3/3/2005), separated by a nonnumeric character like ­ (minus) or / (forward slash). Note You cannot use a : (colon) as a date separator. Important If you enter dates with two-digit years, FileMaker Pro converts them to fourdigit year dates using a conversion method described in Help. To avoid misinterpretations during conversion, always enter dates with four-digit years. Enter the current date in a date, Choose Insert menu > Current Date. number, or text field Select the date using the drop-down calendar, if available. Selecting a field To Select a field Select the contents of a field Move to the next field in Press Tab. the tab order Note To set the Return or Enter key(s) to move to the next field, in Layout mode, select the fields, then choose Format menu > Field/Control > Behavior. Buttons and tab controls can be included in the tab order. Fields on tab panels that are not in front are skipped. Move to the previous field in the tab order Press Shift-Tab (or Shift-Return or Shift-Enter, if enabled). Move to items in a value Press the arrow keys. list Chapter 1 | Using databases 23 To Enter data in a time field Click in the field in Browse mode, then Type the time of day (or time duration) as: To Insert a tab character in a field Click in the field in Browse mode, then Click where you want to insert the tab. Press Ctrl+Tab (Windows) or Option-Tab (Mac OS). Select the data, then press Backspace or Delete. For information, see Help. For information, see Help. For information, see "Setting up checkbox sets and other controls on a layout" on page 62. 1 1 1 1 hours hours and minutes hours, minutes, and seconds hours, minutes, seconds, and fractional seconds Delete data from a field Add data to a container field Insert a file in a container field Add data to value lists (fields formatted as radio or option buttons, checkboxes, lists, or menus) 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.) Enter the current time in a time, Choose Insert menu > Current Time. number, or text field Enter data in a timestamp field Type the date and time (for example, 10/15/2005 3:15:05 PM). Type the day, month, and four-digit year as numbers, separated by a nonnumeric character like - (minus) or / (forward slash). Note You cannot use a : (colon) as a date separator. Then type the time of day as: Edit, format, or check the For information, see Help. spelling of text in a field or edit dictionaries Finding records To find records, work in Find mode. You type criteria (the value or values to find) into fields in a find request, which looks like a blank record. When you perform a find, FileMaker Pro searches through all the records in a table, comparing the criteria 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 don't 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 find requests on the same data. You can't perform a find in summary fields, container fields, or fields defined with the global storage option. 1 1 1 1 hours and minutes hours, minutes, and seconds hours, minutes, seconds, and fractional seconds Type AM or PM if the time is not in 24-hour format Separate hours, minutes, and seconds by a nonnumeric character like : (colon). Enter the current date and time in a timestamp field Choose Insert menu > Current Time. Select the date using the drop-down calendar, if available. 24 FileMaker Pro User's Guide Making a find request To make a find request: 1. Go to a layout that has fields you want to enter criteria in. You can change layouts and enter criteria on more than one layout. 2. Choose View menu > Find Mode. 3. In the find request, select a text, number, date, time, timestamp, or calculation field to use for finding, then type a value in the field. You can use the Symbols pop-up menu to help you enter criteria that include operators. 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 Cancel a find operation before it Press Esc (Windows) or 2-period (Mac OS). is finished and leave the previous found set unchanged Change or refine criteria after performing a find request Narrow an existing found set Broaden an existing found set Show all records again See "Viewing, repeating, or changing the last find" on page 32. See "Constraining (narrowing) a found set" on page 29. See "Extending (broadening) a found set" on page 30. In Browse mode, choose Records menu > Show All Records. In Find mode, choose Requests menu > Show All Records. 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 Open another window in order to perform different find requests on the same data You don't need to choose Show All Records before performing finds. FileMaker Pro always searches all records in the tables you specify unless you have narrowed the existing found set. Choose Window menu > New Window. Chapter 1 | Using databases 25 Performing quick finds based on data in one field You can perform quick finds based on field data without having to go to Find mode. You can perform a find based on an entire field or a selection of text within a field. For more information, see Help. 1. In Browse mode, select the text in a field you want to use for the find. 2. Right-click (Windows) or Control-click (Mac OS) the selected text. From the shortcut menu, choose Find Matching Records. To find A phrase or sequence of characters Type this in the field The text, including spaces and punctuation, between the double quotation marks ("). Examples "Marten and Jones Interiors" finds Marten and Jones Interiors but not Jones and Marten Interiors ", Ltd." finds all companies with ", Ltd." in the name, but not those without the comma. Words with one or more unknown or variable characters Invalid characters One wildcard Gr@y finds Gray and Grey character (@) for each @on finds Don and Ron but unknown character. not Bron ? Invalid characters display as blank characters Note To find the ? character, search for "?" Words with zero or more unknown or variable text characters in a row * for all unknown characters. Jo*n finds Jon and John J*r finds Jr. and Junior *phan* finds Phan and Finding text and characters Search for text in text fields, or in calculation fields that return a text result. Unless you search for phrases and exact matches, the field can contain other values in addition to the one(s) you specify, and the values can be in any order. For example, typing hotel in a field named Accommodation finds records for Hotel, Discount Hotel, and Hotel, Luxury. To find Words that start with specific characters (works with fields that use any language except Japanese) Words that start with specific characters (Japanese field only) Type this in the field The characters. Examples Chris Smith finds Chris Stephanie Digits in a text field A # character for each digit. # finds 3 but not 30 ## finds 30 but not 3 or Smith, Smith Chris, Chris Smithson, and Smith Christenson The characters between = and *. 300 Symbols or other nonalphanumeric characters, such as punctuation or spaces The characters, including spaces and punctuation, between the double quotation marks ("). "@" finds @ (or an email address, for example) "," finds records containing a comma " finds three spaces in a row " 1 1 = * finds , and , =Chris* =Smith* finds Chris Smith, Smith Chris, and Smith Words that contain specific The characters. characters in a Japanese field finds , and , Words with accented characters The text, including spaces and punctuation, between the double quotation marks ("). "òpera" finds òpera but not opera (òpera without quotes finds both òpera and opera) 26 FileMaker Pro User's Guide To find Type this in the field Examples *"son & Phillips" To find Exact matches of whole words you specify Type this in the field = Examples =Market finds Market, Partial phrases (a sequence Characters, of words or characters) punctuation, and spaces between quotation marks (""). Use * to find this text in the middle of a longer text string. Case-sensitive text Change the default language for indexing the field to Unicode. Note This procedure will change the order in which records sort. For more information, see Help. Japanese characters using a ~ (tilde) followed by relaxed search method that the character. does not differentiate between Hiragana/ Katakana, Voiced/SemiVoiced/Unvoiced Kana, Small/Regular Kana, and Kana Voiced/Unvoiced Iteration Marks (Available only in Japanese-indexed fields) A character with special meaning, such as database syntax recognized by FileMaker Pro: @, *, #, ?, !, =, <, >, " Exact matches of the text you specify \ followed by the special character. finds Johnson & Phillips and Paulson & Phillips Market Services, and Ongoing Market Research but not Marketing or Supermarket =Chris =Smith finds fred finds fred but not Fred Ranges of information See "Finding ranges of information" on page 28. Chris Smith or Smith Chris but not Chris or Christopher Smithson Finding numbers, dates, times, and timestamps ~ finds , and , , , , Numbers, dates, times, and timestamps should be entered in the corresponding field types (or calculation fields returning the corresponding field type) to ensure correct behavior when finding them. See "About choosing a field type" on page 44. Important To avoid confusion when working with dates, always use four-digit years. For more information, see the Help topic "Conversion of dates with two-digit years." To find Type this in the field Examples .50 finds .5, .50, and $.50 \"Joey\" finds "Joey" joe\@abc.net finds the email address joe@abc.net == (two equal signs) ==John finds John but A number in a number field The number, in the or in a calculation field that format used when the produces a numeric result file was created (unless Use System Formats is chosen) A Boolean number in a number field or in a calculation field that produces a Boolean result 1 to find True values 0 to find False values 1 finds 1 0 finds 0 not John Smith ==John Smith finds John Smith but not Smith, John or John Smithers Chapter 1 | Using databases 27 To find Type this in the field Examples 3/3/2005 finds To find Invalid data (fields containing no numbers) in a number field or calculation field that produces a numeric result Type this in the field ? Examples ? finds: A date in a date field or in a The date as digits, calculation field that separated by a produces a date result nonnumeric character, in the format used when the file was created (unless Use System Formats is chosen) Today's date in a date field // or in a calculation field that produces a date result A time in a time field or in a calculation field that produces a time result 3/3/2005, March 3, 2005, and 3-3-2005 twelve but not 12 or twelve30 // finds April 4, 2005 (when the current date is 4/4/2005) Dates on a day of the week The day of the week in a date or timestamp field Note Full or short day names (for example, Friday or Fri) are acceptable in day of week searches Any valid value for a date or time component in a date, time, or timestamp field * or leave component Tuesday finds all dates that occur on a Tuesday =Thu finds all dates that occur on a Thursday The time as digits, 12:53:09 finds 12:53:09 separated by colons, in the format used when the file was created (unless Use System Formats is chosen) The date as digits, 3/3/2005 12:53:09 separated by a PM finds nonnumeric character, 3/3/2005 12:53:09 PM then the time as digits, separated by colons, in the format used when the file was created (unless Use System Formats is chosen) ? ? finds: 3/3/* finds the 3rd day unspecified, while specifying the other components you want to find of March in any year *:15 finds times that are 15 minutes after any hour 1/1/* 7 PM finds A timestamp in a timestamp field or in a calculation field that produces a timestamp result timestamps in the 7 o'clock hour on January 1st in any year Note When the year is unspecified, the current year is assumed. 3/3 finds the 3rd day of March in the current year Ranges of information See the next section. Invalid dates, times, timestamps, or calculated date or time results Next Tuesday or 2/33/2005 in a date field, or midnight in a time field 28 FileMaker Pro User's Guide Finding ranges of information To find values that are Less than a specified value Use this operator < Examples <40 <9/7/2005 >95129 >9/7/2005 >M To find times In the 3 o'clock hour, not including 4:00 PM Between 8:00 AM and 8:59:59 PM In the morning Any of the times 4:30, 5:30, 6:30 PM Type this in the field 3 PM Greater than or equal to a specified value >= or (Mac OS) >=100 >=9/7/2005 8 AM..8 PM AM {4..6}:30 PM 8:00 M Within the range you specify. A .. or ... range is different based on the data (two or three type: periods) 12:30...17:30 1/1/2005..6/6/2005 A...M 1 1 1 To find timestamps In the 3 o'clock hour today In the 7 o'clock hour in May, 2005 That occur on a Monday in 2006 Type this in the field // 3 PM 5/2005 7 PM =Mon 2006 Numbers: least to greatest Dates and times: earliest to latest Text: first to last word, based on the index order of words (not the sort order) 11/{10..16}/2006 {3..5} PM From the 10th through the 16th of November, 2006 and from 3:00 PM to less than 6:00 PM (if the date format is MM/DD/YYYY) To find dates In June, 2005 Type this in the field 6/2005 From July 1997 through October 1998 7/1997...10/1998 That occur on a Friday From the 10th through the 16th of October or November, 2006 (if the date format is MM/DD/YYYY) =Friday {10..11}/{10..16}/2006 Chapter 1 | Using databases 29 Finding data in related fields You can enter find criteria in related fields that are displayed in a portal or directly on a layout. When you perform a find in a related field, FileMaker Pro displays all the records that have a related record matching the criteria you enter. For example, suppose you have an invoice file that displays line items as related records in a portal. To find all invoices listing a computer, type Computer in the Item field in the portal. For information about related fields, see chapter 3, "Working with related tables and files." For information about omitting related records, see "Finding records except those matching criteria" on page 30. Find request Invoice ID Name Item ID Item Computer Finding empty or non-empty fields To find fields that are Not empty (i.e., fields that have data) Empty Type this in the field * = Finding duplicate values You can find all records for which one or more fields contain duplicate values. For example, you might want to find all records that aren't unique, then examine them to decide which records to use or delete. To find all duplicate records, choose View menu > Find Mode and type ! in the field. Finding records that match multiple criteria You can narrow or broaden your search by using more than one criterion. Logical AND search To narrow your search, enter criteria in as many fields as needed to make your request specific, then click Find. Records in table Invoice ID Name Item ID I100 I202 I153 I229 I448 E154 Alvarez Item Computer Desk Lamp Bookcase Chair Invoice ID Name Item ID I202 I443 I153 I288 I100 E153 Tang Item Invoice ID E152 Desk Name Durand Long table Lamp Item ID Item Round table I384 Swivel chair Computer I554 Panels I288 Round table I100 Computer I448 Chair You can narrow find results incrementally, looking for more specific detail as you search your database. See the next section. Constraining (narrowing) a found set For example, after searching for all of the employees who work in Sales, you can narrow the search to find all of the employees in Sales named Alvarez: 1. In Find mode, perform a find to find all of the employees who work in Sales. FileMaker Pro displays the found set in Browse mode. 2. Switch back to Find mode and type the criteria to narrow the search (type Alvarez in the Last Name field). 3. Choose Requests menu > Constrain Found Set. The found set now consists of employees in Sales named Alvarez. 30 FileMaker Pro User's Guide Note To quickly constrain the found set in Browse mode, use the shortcut menu. Click in the field (or select a partial value in the field), then right-click (Windows) or Control-click (Mac OS), and choose Constrain Found Set. Extending (broadening) a found set For example, after searching for customers in New York, you can broaden the search to also find customers in Hong Kong: 1. In Find mode, perform a find to find customers in New York. FileMaker Pro displays the found set in Browse mode. 2. Switch back to Find mode and type the criteria to broaden the search (type Hong Kong in the City field). 3. Choose Requests menu > Extend Found Set. The found set now consists of customers in New York and Hong Kong. Note To quickly extend the found set in Browse mode, use the shortcut menu. Click in the field (or select a partial value in the field), then right-click (Windows) or Control-click (Mac OS), and choose Extend Found Set. Logical OR search To widen your search, enter criteria in the first request. Choose Requests menu > Add New Request. Enter the second (set of) criteria. Continue adding requests for each (set of) criteria, then click Find. You can navigate among multiple requests using Go to Request on the Requests menu or using the book icon in Find mode. Examples: To include customers in New York and customers in Paris in the found set, type New York in the City field in the first request, then type Paris in the City field in the second request.