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. All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. FileMaker documentation is copyrighted.
Contents 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 f
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 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
| 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 Chapter 5 Protecting databases with accounts and privilege sets Protecti
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.
FileMaker Pro User’s Guide Online Help Suggested reading FileMaker Pro Help provides comprehensive step-by-step instructions on FileMaker Pro features. You can open Help from the Help menu in FileMaker Pro. Here are some suggestions for using the documentation to help you learn how to do specific tasks in FileMaker Pro. There are three ways to access Help: 1 1 1 Browse the Help table of contents to locate topics by subject. Search for particular topics by entering keywords.
Preface | Introducing FileMaker Pro FileMaker Pro overview Using layouts to display, enter, and print data Here is an overview of the capabilities of FileMaker Pro. 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.
FileMaker Pro User’s Guide For more information about creating layouts, see “Creating layouts and reports” on page 53. 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.
Preface | Introducing FileMaker Pro 11 Sharing and exchanging data Saving and sending databases in other formats 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.
FileMaker Pro User’s Guide Usability improvements Ensure data accuracy For more information on this feature See For more information on this feature See New tool for creating and managing tab controls (tabbed panels) on layouts “Creating layouts and reports” on page 53 New auto-complete capability for text fields “Entering and changing data in fields” on page 22 New Field/Control Setup dialog box makes it easier to create and edit checkbox sets and other controls that display data from a valu
Chapter 1 Using databases This chapter explains the basics of how to: 1 1 1 1 1 1 1 1 open, close, and save files create records in a database 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.
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.
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). 4. Click Open. The original database window, in which you can search the records 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.
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. 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 close a window, do one of the following: 1 Windows: Double-click the document icon in the upper left corner of the document window.
Chapter 1 When FileMaker Pro saves a file | Using databases 17 Mode tabs In FileMaker Pro, the following actions do not cause the file to be saved: 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 1 Changing the position and size of windows or hiding/showing the status area 1 1 Changing layout object default settings Changing ruler display settin
FileMaker Pro User’s Guide Viewing records Viewing records in table format 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. While viewing records in table format, you can make one or more of the following changes to columns and records: To Do this To view records Do this in Browse mode or Find mode Reorder columns Click a column heading and drag it to a new location.
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.
FileMaker Pro User’s Guide Moving through records Adding and duplicating records To move from one record to another, use the book icon in the status area. 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). 1 If records are sorted, the new record appears immediately after the current record.
Chapter 1 | Using databases 21 Adding and duplicating related records Deleting 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. When you delete a record, you permanently discard the data in all the fields in that record. Note You can only add related records from the current table if the relationship is defined to allow the creation of related records.
FileMaker Pro User’s Guide Entering data Entering and changing data in fields 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. 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 Selecting a field Enter data in a text field To Do this in Browse or Find mode Select a field Click in the field.
Chapter 1 To Enter data in a time field 23 To Click in the field in Browse mode, then Type the time of day (or time duration) as: Insert a tab character in a field Click where you want to insert the tab. Press Ctrl+Tab (Windows) or Option-Tab (Mac OS). hours, minutes, and seconds Delete data from a field hours, minutes, seconds, and fractional seconds Select the data, then press Backspace or Delete. Add data to a container field For information, see Help.
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. 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: 2. Choose View menu > Find Mode. To 3.
Chapter 1 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. To find A phrase or sequence of characters 1. In Browse mode, select the text in a field you want to use for the find. Type this in the field The text, including spaces and punctuation, between the double quotation marks (").
FileMaker Pro User’s Guide To find Type this in the field Examples To find 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. *"son & Phillips" Exact matches of whole words you specify Case-sensitive text fred finds fred Change the default language for indexing the field to Unicode.
Chapter 1 To find Type this in the field | Using databases Type this in the field Examples ? ? finds: Examples To find 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) 3/3/2005 finds 3/3/2005, March 3, 2005, and 3-3-2005 Invalid data (fields containing no numbers) in a number field or calculation field that produces a numeric re
FileMaker Pro User’s Guide Finding ranges of information To find values that are Use this operator Less than a specified value < To find dates Examples <40 <9/7/2005
Chapter 1 | Using databases Finding data in related fields Finding empty or non-empty fields You can enter find criteria in related fields that are displayed in a portal or directly on a layout. To find fields that are Type this in the field Not empty (i.e., fields that have data) * Empty = 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.
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 Logical OR search FileMaker Pro displays the found set in Browse mode. To widen your search, enter criteria in the first request. Choose Requests menu > Add New Request. Enter the second (set of) criteria.
Chapter 1 Finding records that don’t match criteria Keep these points in mind: For example, to find all sales records except those for the city of London: 1 1 1. In Find mode, type criteria for the records to omit. 2. Select Omit in the status area. | Using databases 31 You can have Omit criteria in more than one request. FileMaker Pro works through the requests in the order you create them.
FileMaker Pro User’s Guide Deleting and reverting requests Finding and replacing data To delete a request, go to the request you want to delete, then choose Requests menu > Delete Request. As in a word processing application, you can find and replace data across multiple fields (including related fields) in a record or in a find request, across a found set of records or find requests, or across text objects in a layout.
Chapter 1 Select To An option in Search across In Browse mode, search across all records in the current layout or just in the current record. In Find mode, search across all find requests in the current layout or just in the current find request. Note In Layout mode, FileMaker Pro searches just in the current layout. An option in Search within In Browse and Find modes, search within all fields in the current layout or just in the current field.
FileMaker Pro User’s Guide Previewing and printing databases You can print data based on the current layout or you can print information about the structure of the database With FileMaker Pro, you can print: 1 all the records in the database, a subset of the records, or only the current record 1 1 a blank record to create a paper form definitions of scripts, fields, tables, and relationships to see the structure of your database 1 Current layout Phone List data saved as a PDF file FileMaker
Chapter 1 | Using databases 35 Previewing data on a layout Printing records When you switch to Preview mode, you see the layout as it appears on the printed page. You can’t enter or edit information in fields in Preview mode. You can print information from your database, or you can print a blank record in order to print a blank “form.” In Preview mode, you see: 1. If you have more than one printer, specify which one you want to use.
FileMaker Pro User’s Guide 6. Choose File menu > Print. 7. In the Print dialog box, for Print, choose an option (Windows) or choose FileMaker from the pop-up menu (Mac OS).
Chapter 1 | Using databases 37 Printing table and field information 7. Click OK (Windows) or Print (Mac OS). To print table or field information: For more information about previewing and printing databases, see Help. 1. Choose File menu > Define > Database, then click the Tables or Fields tab. 2. Select the table(s) or field(s) you want to print. Automating tasks with scripts To select more than one contiguous table or field in the list, Shift-click the tables or fields.
FileMaker Pro User’s Guide 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 specific to any file. Settings in the File Options dialog box affect the current file’s default layout, opening and closing scripts, login information, and spelling. To set application preferences: 1. Windows: Choose Edit menu > Preferences.
Chapter 2 Creating databases This chapter explains the basics of how to: 1 1 1 1 1 plan a database define and modify fields define tables create layouts work with fields, objects, and parts on a layout Note See Help for detailed, comprehensive information and step-bystep procedures about using FileMaker Pro. Planning a database A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables.
FileMaker Pro User’s Guide Typically, databases are organized in one of three ways: 1 A single table in a single file. Use a single table if you need to track data in one category only, such as names and addresses. 1 Multiple tables in a single file. Use multiple tables if your data is more complex, such as students, classes, and grades. 1 Multiple tables in multiple files. Use multiple files if you need to share the same data among several different database solutions.
Chapter 2 Client ID Client Name Street Route ID Origin Destination Client ID Tour ID Tour Name Client Name Street Route ID Origin Destination | Creating databases Tour ID Tour Name City Route ID City Route ID Postal Code Origin Postal Code Origin Phone Destination Phone Destination Tour ID Tour ID Tour Name Tour Name Clients table Routes table Clients table Tours table For example, in the Clients table you might want to assign each client a unique, identifying number.
FileMaker Pro User’s Guide 12. Decide what layouts you need, and plan a separate layout for each task. Client ID For example, create separate layouts for printing labels or envelopes. Client Name Route ID Street Origin City Destination For more information about creating layouts, see “Creating layouts and reports” on page 53. Tour ID Postal Code Tour Name Phone Route ID Tour ID Origin Tour Name Destination 13.
Chapter 2 If you didn’t install templates and want to use them, see the Installation Guide for FileMaker Pro and FileMaker Pro Advanced. 1. In FileMaker Pro, choose File menu > New Database. 2. In the New Database dialog box, do the following: To Do this Create a file using a template Click Create a new file using a template, then double-click the name of a template. Create a file without Click Create a new empty file, then click OK.
FileMaker Pro User’s Guide Choose the way fields are listed in the dialog box 1 If you’re exchanging data with another application, check the field naming restrictions in the file formats supported by the other application. 1 Click to sort by field name or type If you’re using ODBC or JDBC to share FileMaker Pro data, avoid using SQL keywords in field names.
Chapter 2 | Creating databases 45 Keep these points in mind: Select this field type If the field data will be Container 1 Graphics, movies, photos, bitmaps, sounds of up to 4 GB per field 1 Documents, including Microsoft Word or Excel files, PDF files, and so on 1 Multimedia file types supported by QuickTime 6.0 or later (Win) or QuickTime 6.4 to 6.5.
FileMaker Pro User’s Guide 7. To define options for the field, click Options. Defining calculation fields For more information about field options, see Help. 1. With the database open, choose File menu > Define > Database. To Do this in the Options for Field dialog box 2. In the Define Database dialog box, click the Fields tab. Automatically enter values into the field Click the Auto-Enter tab, select the options, then click OK. See “Defining automatic data entry” on page 50. 3.
Chapter 2 | Creating databases 47 8. Build a formula for your calculation. Click where you want the item to appear in the formula box, then do the following. You can also type the formula into the box. To add a Do this Reference to a field In the field list, double-click a field name. To display field names from another table, choose a table from the table list. Mathematical or text In the keypad in the dialog box, click an operator.
FileMaker Pro User’s Guide 10. To select indexing and global storage options for the field, click Storage Options, select options in the Storage Options dialog box, then click OK. 11. Click OK to close the Specify Calculation dialog box. 12. Continue to define fields, or click OK. For more information about defining calculation fields and the use of context in calculations, see Help.
Chapter 2 | Creating databases 49 7. Select an option, if applicable, for the summary type. 8. Select an option, if applicable, for summarizing a repeating field. Options change according to the summary type you select in the previous step.
FileMaker Pro User’s Guide To set options for summary fields, see “Defining summary fields” on page 48. Defining automatic data entry To automatically enter a default value into a field for each record: 1. With the database open, choose File menu > Define > Database. 2. In the Define Database dialog box, click the Fields tab. 3. If your file contains more than one table, select the appropriate table from the Table list. 4. Double-click a field in the fields list.
Chapter 2 3. If your file contains more than one table, select the appropriate table from the Table list. To require that the entered value 4. Double-click a field in the fields list. Matches the result of a calculation 5. In the Options for Field dialog box, click the Validation tab. 6. Specify how FileMaker Pro handles an invalid entry.
FileMaker Pro User’s Guide About storage and indexing options Defining database tables FileMaker Pro provides options for indexing fields and for storing the results of calculations. You can: Use database tables to organize and group your data by a common characteristic or principle. Your database can contain as many tables as you need to organize your data (limited only by the amount of storage space on your hard disk). 1 Create an index, which is a list of the values stored in a field.
Chapter 2 3. In the Table Name box, type a name for the table, then click Create. The table can be renamed or deleted. Tables you add to a file are automatically displayed in the relationships graph. 4. Continue to define tables, or click the Fields tab to define fields.
FileMaker Pro User’s Guide Click to move to another tab panel To create a layout: 1. Choose View menu > Layout Mode. 2. If you intend to print the new layout in landscape orientation or on a special paper size, choose File menu > Print Setup (Windows) or File menu > Page Setup (Mac OS), confirm the orientation and paper settings, then click OK.
Chapter 2 About layout types | Creating databases 55 Simple columnar report layout The following sections describe each of the layouts that you can create with the New Layout/Report assistant. Standard form A Standard form layout is a good choice for data entry or onscreen browsing. It contains the fields you select, each on a separate line, in the order you specify, with field labels to the left of the fields.
FileMaker Pro User’s Guide Table view Labels Table view is not a type of layout; it’s a view of your data in a spreadsheet-like grid format where you can quickly rearrange fields in Browse mode. For example, you can quickly reorder, resize, or sort columns (fields) with just a mouse click (if those options are set). Table view is one of three views available for any layout in FileMaker Pro. For more information, see “Viewing records” on page 18.
Chapter 2 Envelope | Creating databases 57 You can choose to arrange columns across the page or down the page. Use an Envelope layout to print the fields you select, arranged to print on a standard “Number 10” business envelope. 1 5 1 2 2 6 3 4 3 7 5 6 4 8 7 8 Down first Across first To set up columns in a layout: Blank layout 1. Choose a printer and print or page setup options.
FileMaker Pro User’s Guide 4. In the Layout Setup dialog box, click the Printing tab, select Print in columns, select options for the columns, then click OK. To Do this Specify the number of columns For Print in columns, type a number between 1 and 99. 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.
Chapter 2 Toolbars | Creating databases 59 To select In Layout mode, do this One object With the arrow pointer, click the object. (If the object is transparent, click its border.) Several objects at once Drag the arrow pointer to make a selection box that completely surrounds the objects. (If you press Ctrl (Windows) or 2 (Mac OS) while dragging, the selection box does not have to completely surround the objects.) Selection tool Or, press Shift as you click each object individually.
FileMaker Pro User’s Guide Working with fields on a layout Keep these points in mind: After you create a layout, you can place fields on it, remove fields you don’t want displayed, and determine the format in which you want data displayed. Fields on a layout are objects, which you can select, move, resize, and reshape. In Layout mode, each field displays its field name, formatted with its attributes for font, size, style, alignment, line spacing, and color.
Chapter 2 | Creating databases 61 3. In the Specify Field dialog box, select the field to place. To place a field on a layout: 1. In Layout mode, choose the layout you want to work on from the Layout pop-up menu. 2. Drag a field from the Field tool to the position you want on the layout. (Or choose Insert menu > Field.
FileMaker Pro User’s Guide Setting up checkbox sets and other controls on a layout You can format fields to display as drop-down lists, pop-up menus, checkboxes, radio buttons, and drop-down calendars. You can also format fields to auto-complete by suggesting possible matches as the user types data in the field. All of these options aid in data entry. To set up a field to display a pop-up menu, checkbox set, or other control: 1.
Chapter 2 | Creating databases 63 Deciding where to place related fields Formatting field data on a layout You can place related fields directly on layouts or in portals. Use FileMaker Pro field formats to control how data appears on a layout. The formats you specify don’t change the data as it’s stored in the database, and you can specify different formats each time you place a field on a layout (whether it’s on the same layout or a different layout).
FileMaker Pro User’s Guide Specifying text formats for fields Specifying formats for fields containing numbers In Layout mode, you can specify a set of text character attributes for each field. All the characters in that instance of the field (including all repetitions of repeating fields) share the same font, size, style, color, line spacing, tab, and paragraph settings. You can control how FileMaker Pro displays the values in number fields, calculation fields with numeric results, and summary fields.
Chapter 2 1. In Layout mode, select one or more date fields or calculation fields with a date result. | Creating databases 65 4. Click OK. 2. Choose Format menu > Date. If you want to format the seconds component to display fractional seconds, continue with the next step. 3. In the Date Format dialog box, select the formats you want to use. 5. Choose Format menu > Number. You see examples of the options you choose in the Sample area. 6.
FileMaker Pro User’s Guide 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. Choose how to fit the graphic in the frame Choose how to align the graphic horizontally Choose how to align the graphic vertically 5. In the Time Format dialog box, select the formats you want to use and click OK. 6.
Chapter 2 | Creating databases 67 Allowing or preventing entry into fields Working with parts on a layout 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 can’t 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 can’t be entered.
FileMaker Pro User’s Guide About layout part types The following table describes layout part types. Header Body Layout part Description Title header Appears only once at the top of the first screen or page and replaces the normal header (if one is specified). In reports, can be used to print a separate title page. You can have only one title header in a layout. Header Appears at the top of every screen or page (unless you add a title header, which supersedes the header on the first page).
Chapter 3 Working with related tables and files This chapter explains the basics of how to: Use lookups to: 1 1 1 1 plan a relational database create and edit relationships create and edit lookups FileMaker Pro uses related tables as the basis for relational databases and lookups. Relational databases allow you to work with data from other tables dynamically, so that you can change data in one place and your changes are reflected in all places where the related data appears.
FileMaker Pro User’s Guide Current table Related table Tours table Invoices table Client ID R20 Client ID R20 Tour ID Route NY-Paris Route NY-Paris Tour Name NY-Roma In relational databases, data from the related table is only displayed in the current table, not copied Current table T10 Route ID Origin R200 NY R42 Paris Destination Paris Roma Invoice No A-200-61 Date 16-Oct-2005 Client ID C100 Name Williams Tour ID Tour Name Cost $ T10 NY-Roma 550.00 T20 Roma-Istan 700.
Chapter 3 For more information about relationships, see Help. 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. Term Description Current table For relational databases, the table that you are currently working in. For lookups, the table that the data is copied to. External table A table outside of the current file, in another file.
FileMaker Pro User’s Guide About the relationships graph When you work with tables in the relationships graph, you are using them to organize your view into your data. Each table occurrence in the relationships graph represents a separate view into your data. When you join two tables, you are leveraging the two existing views to create a third view.
Chapter 3 Use a single-criteria relationship when you want to relate two tables based on a single common value, such as a serial number or an ID number. For example, a clients database has two tables: Clients, which stores names and addresses, and Phone Numbers, which stores phone numbers and types of phone numbers, such as work, home, fax, and so on. The data is split between two tables because a single client can have multiple phone numbers.
FileMaker Pro User’s Guide Table Match fields Match fields In this relationship, both Client ID and Rental Date are match fields to the Rental Line Items table. A record in the Clients table with a Client ID value of 1000 and a Rental Date value of 10/10/2004 will only match those records in the Rental Line Items table where both fields Client ID and Rental Date have values of 1000 and 10/10/2004, respectively.
Chapter 3 | Working with related tables and files 75 Relational operator Matches these records ≥ Values in the left match field are greater than or equal to values in the right match field x All records in the left table are matched to all records in the right table, regardless of the values in the match fields Match field Match fields Indicates that several different relational operators are used in this relationship Match field Match field Greater than relational operator In this example, th
FileMaker Pro User’s Guide Planning a relational database Another example: If fields Contain values Starting Date E 12/01/2005 Ending Date E 01/01/2006 These related records are returned from Table F 12/02/2005 12/15/2005 12/17/2005 12/26/2005 Working with the relationships graph Self-joining relationships A self-join is a relationship in which both match fields are defined in the same table. Define a self-join to create relationships between records in the same table.
Chapter 3 | Working with related tables and files 77 4. Click a field in one of the tables and drag to the corresponding match field in the other table. 5. Repeat step 4 for each match field you intend to use in this relationship. Or, you can click to open the Edit Relationship dialog box and create a relationship. For more information about match fields, see “About match fields for relationships” on page 72. 6. Click OK to save your changes and close the relationships graph.
FileMaker Pro User’s Guide Changing relationships 4. Edit tables and match fields for the relationship. After you have created a relationship, you can add or change match fields, change relational operators, and set options for creating, deleting, and sorting related records. To Do this Change a match field Select the new match field and click Change. Add a match field Select the new match field and click Add.
Chapter 3 | Working with related tables and files 79 For more information about creating and editing relationships, see Help. To establish a connection between tables for a lookup, you create a relationship. Then you define a lookup to copy data from a field in the related table into a field in the current table.
FileMaker Pro User’s Guide 6. Click Options (or double-click the field name). 11. Click OK. 7. In the Options for Field dialog box, click the Auto-Enter tab, then select Looked-up value. 12. Repeat steps 5-11 for each additional field for which you want to define a lookup. 8. In the Lookup for Field dialog box, choose the table the lookup will start with, and the table from which the value will be copied.
Chapter 4 Sharing and exchanging data This chapter explains the basics of how you can share FileMaker Pro databases: 1 FileMaker Network Sharing: You can share FileMaker Pro databases on your network, which allows multiple FileMaker Pro users to access and use the same database file simultaneously. 1 Importing and exporting data: You can exchange information between FileMaker Pro and other applications by importing and exporting data.
FileMaker Pro User’s Guide Enabling file sharing and hosting files Click to enable network sharing Because many FileMaker Pro commands are processed on the host machine, you will see better performance if you host your shared files from the fastest available computer. The file you’re hosting must be on the hard disk of your computer. Remotely hosting a file stored on another computer or server is not supported, as it might compromise data integrity and will result in poor performance.
Chapter 4 | Sharing and exchanging data 83 6. Click OK. Limitations on changes to shared files The open files are now hosted. Although it is possible for multiple users to make changes in the same shared file at the same time, there are limits to the types of simultaneous changes that are permitted. The following table describes the types of changes you can and cannot make to shared files. Important You must keep your shared file(s) open to make them available to clients.
FileMaker Pro User’s Guide Opening shared databases remotely using a URL To close a hosted file: The FileMaker Pro installer registers FMP7 as an Internet protocol. This enables you to open a shared database using a URL. 1. On the computer hosting the file, choose File menu > Close. FileMaker Pro databases shared via TCP/IP can be opened remotely using the list of local hosts, the IP address of the host machine, or the DNS name of the host machine if one has been defined.
Chapter 4 In order to save files as Excel or PDF, you need the following access privileges: 1 1 Allow exporting to save Excel files. Allow printing to save PDF files. Note The saved file will only include fields that are on the layout when you perform the save. If you want to include other fields (for example, fields on tab panels that are not in front), use the Export Records command instead of Save/Send Records As.
FileMaker Pro User’s Guide About updating existing records With this option, you replace data in your file with data from the source file. For each field you import into, data from the first importable record (or row of data) in the source file overwrites fields in the first record in the target file. Data from the second importable record (or row of data) in the source file overwrites fields in the second record in the target file, and so on.
Chapter 4 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 necessary, select a table from the Source drop-down list. 7. Accept the table shown in the Target drop-down list, or select New Table to create a table with the same schema as the source table. 8. Select the type of import to perform. See “Methods of importing data into an existing file” on page 85. 9.
FileMaker Pro User’s Guide About the exporting process You can export FileMaker Pro data to a new file, then open it in another application. To export related fields, select the related table from the list FileMaker Pro exports these fields The basic steps for exporting records are: 1. Open the FileMaker Pro file and display a layout that shows the records you want to export. Select fields from this list 2. Find and sort the records to export.
Chapter 4 To work with a published FileMaker Pro database, users need: 1 1 web browser software access to the Internet or an intranet.
FileMaker Pro User’s Guide
Chapter 5 Protecting databases with accounts and privilege sets This chapter describes the basics of how to use accounts and privilege sets to restrict what users can see and do in a FileMaker Pro file.
FileMaker Pro User’s Guide Accounts, privilege sets, and extended privileges 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. Each database file contains two predefined accounts: Admin and Guest. For more information, see “About the predefined accounts” on page 93.
Chapter 5 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 99.
FileMaker Pro User’s Guide Planning security for a file Privilege Sets Privilege Full Access Data Entry Only Read-Only Access Disconnect user from FileMaker Server when idle off on on Allow password modification on on on Password change number of days off off off Minimum password length off off off Available menu commands all all all A new FileMaker Pro file is initially unprotected.
Chapter 5 7. Open the file using different accounts and test each privilege set that you created. Make sure the restrictions work the way you want, and make any needed corrections to your privilege sets. Password-protecting a file If you have a non-shared database file on your computer and you want to prevent others from opening it, you can password-protect the file. After password-protecting a file, you will be prompted to enter an account name and password every time you open the file.
FileMaker Pro User’s Guide Viewing accounts and privilege sets The Define Accounts & Privileges dialog box displays a list of all the accounts and privilege sets for a file. To view the accounts and privilege sets for a file: 1. Open the database file. 2. Choose File menu > Define > Accounts & Privileges. You see the accounts defined for this file.
Chapter 5 3. In the Edit Account dialog box, for Account is authenticated via, choose FileMaker. | Protecting databases with accounts and privilege sets 97 6. For Account Status, choose whether you want the account to be active or inactive. For example, you may want to keep the account inactive until you finish setting up its privilege set. Users cannot open a database using an inactive account name and password. 7. For Privilege Set, choose the privilege set you want to use with this account.
FileMaker Pro User’s Guide Important When a database file contains one or more External Server accounts, make sure you use operating system security settings to limit direct access to the file. Otherwise, it might be possible for an unauthorized user to move the file to another system that replicates your authentication server environment and gain access to the file. For more information, see the FileMaker Server documentation.
Chapter 5 | Protecting databases with accounts and privilege sets About enabling record-level access To view the extended privileges: You can set individual record access for each table. For example, the privilege set can limit the ability to: 1. Open the database file. 1 1 View, edit, create, or delete all or certain records within each table. Access or modify certain fields within each table. 2. Choose File menu > Define > Accounts & Privileges. 3.
FileMaker Pro User’s Guide
Chapter 6 Converting FileMaker databases from previous versions Because FileMaker Pro 7 and FileMaker Pro 8 share the same file format, FileMaker Pro 8 can open FileMaker Pro 7 files without converting them. You can even use FileMaker Pro 8 files with FileMaker Pro 7. However, new features might not work as expected when you open the file in a previous version.
FileMaker Pro User’s Guide This chapter describes the basics of file conversion. It includes: information on how to view the Conversion.log file after converting one or more files When you convert files, FileMaker Pro 8 preserves the contents of your original files and creates new, converted files in FileMaker Pro 8 format. The content of the original files are not modified, and you can open them in the previous version of FileMaker Pro that created them.
Chapter 6 4. In the Open dialog box, select the file to convert, and then click Open. Note You can also begin conversion by dragging the file onto the FileMaker Pro 8 application icon. The Conversion dialog box appears, where you can choose to rename the existing file prior to conversion. By default, FileMaker Pro renames the file by adding “Old” to the filename. 5. Do one of the following: 1 Skip renaming the old file by clearing the checkbox named Rename old file and clicking OK.
FileMaker Pro User’s Guide There are several ways that you can select the files that you want to convert; you can drag and drop the files (or a folder containing the files) onto the FileMaker Pro 8 application icon, or you can select multiple files in the FileMaker Pro Open dialog box. To convert multiple files at once: 1. If the files you want to convert are currently open in the previous version of FileMaker Pro, close the files.
Chapter 6 | Converting FileMaker databases from previous versions 105 5. Click Specify, choose the folder you created in step 2 where you want to save the converted files, and click OK (Windows) or Choose (Mac OS). 7. Using a text editor such as Notepad (Windows) or TextEdit (Mac OS) open the Conversion.log file located in the folder that contains your converted database files. Important Do not choose a folder that contains any of your original files.
FileMaker Pro User’s Guide In most cases, you can correct problems in converted files. If the log file indicates that there is a problem that would be complicated and time-consuming to fix after conversion, you may want to try correcting the problem in the original file using a previous version of FileMaker Pro, and then convert the file again. Important The Conversion.log file may not indicate every item in the file that needs to be corrected.
Chapter 6 Top conversion issues The following sections briefly describe the top issues that you may encounter when converting FileMaker Pro databases earlier than 7.0 to FileMaker Pro 8. For complete details, see Converting FileMaker Databases from Previous Versions on www.filemaker.com/ downloads. Passwords During conversion, passwords are converted into accounts in FileMaker Pro 8.
FileMaker Pro User’s Guide Switching windows in scripts When necessary, pre-7.0 versions of FileMaker Pro would automatically change the active window after running a script. This automatic windowing behavior is no longer supported because script writers can use the Select Window script step to set the active window. To emulate the behavior of pre-7.
Index Symbols A B ! 29 – 44 " 44, 47 " " 25 # 25 $ 44 & 44 ( 44, 47 ) 44, 47 * 25, 29, 44 + 44, 84 , 44 ... 28 / 44 // 27 : 44 :: 44 ; 44 < 28, 44 <= 28 = 26, 29, 44 == 26 > 28, 44 >= 28 ? 27 @ 25 [ 44 ] 44 ^ 44 _ 44 { 44 } 44 ~ 26 ≠ 44 ≤ 28, 44 ≥ 28, 44 Access privileges.
FileMaker Pro User’s Guide Changing data in fields 22 find requests 32 lookups 80 preferences 38 relationships 78 Characters maximum in fields 22 not allowed in field names 44 Checkboxes, displaying fields as 62 Clients described 81 limits, on the web 88 performing tasks 83 saving files 81 Clones 16 Closing windows and files 16 Columnar list/report layout See also Layouts described 55 Columns changing width 58 in tables 18 printing records in 57 Combo boxes, displaying fields as 62 Comma-Separated Tex
| E Electronic documentation 7 Elements. See Objects Emails, sending from FileMaker Pro 85 Empty or non-empty fields 29 Encryption.
FileMaker Pro User’s Guide Find mode, described 17 Find requests See also Finding records adding new 30 broadening criteria 30 deleting 32 described 23 editing 32 mixing find and omit 31 multiple 30 narrowing criteria 29 operators in 24, 28 order of 31 repeating 32 reverting 32 Finding and replacing field values 32 Finding records See also Find requests, Quick finds adding new request 30 all records 24 Boolean numbers 26, 108 described 23 duplicate records 29 empty or non-empty fields 29 exact values
| Labels layout See also Layouts described 56 standard sizes 56 Layout mode, described 17 Layout objects.
FileMaker Pro User’s Guide Options, file 38 OR searches 30 P Page breaks, viewing in Preview mode 35 Page margins, viewing in Preview mode 35 Page numbers 55 viewing in Preview mode 35 Page orientation for printing 36 Panels, tab panels on layouts 53 Parts. See Layout parts Passwords defining 95 protecting files with 95 related files, planning 42 troubleshooting 106, 107 PDFs saving data as 84 Photos 45 Picture fields.
| Related tables described 71 planning 39 Relational databases described 14, 69 match fields 71 planning 72 planning 39, 76 portals 71 related tables described 71 planning 39 terminology 71 Relational operators 71, 74, 78 Relationships See also Relational databases changing 78 comparative 74 creating 76 described 71 multi-criteria 73 planning 41 returning range of records 75 self-joining 76 single-criteria 72 types 72 Relationships graph 71, 72, 77 described 77 Repeating find requests 32 Reports See also Co
FileMaker Pro User’s Guide Symbols and field names 44 finding 25 Symbols pop-up menu 24 T Tab controls described 53 including in tab order 22 Tab order 22 Table view, described 56 Tables defining 52 described 13 importing data into new 85 naming 53 table occurrences 72 viewing records as 18 Tabs, inserting in fields 23 Tab-Separated Text format 85 Tagged PDF files 7 Templates creating files 42 described 7, 42 Text See also Fields attributes 64 fields 44 finding 26 formatting 64 rotating Asian text 66
