FileMaker Pro 14 ® User’s Guide
© 2007–2015 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker and FileMaker Go are trademarks of FileMaker, Inc. registered in the U.S. and other countries. The file folder logo and FileMaker WebDirect are trademarks of FileMaker, Inc. All other trademarks are the property of their respective owners. FileMaker documentation is copyrighted.
Contents Chapter 1 Introducing FileMaker Pro About this guide Working with FileMaker Pro FileMaker Pro documentation FileMaker Pro Help Getting Started tour Starter Solutions Suggested reading FileMaker Pro overview Using layouts to display, enter, and print data Finding, sorting, and replacing data Protecting files Automating tasks with scripts Sharing and exchanging data Saving and sending data in other formats Chapter 2 Using FileMaker Pro About fields and records Opening and closing files Opening multi
Finding records Performing quick finds in Browse mode Making find requests in Find mode Finding data in related fields Viewing, repeating, or changing the last find Saving find requests Deleting and reverting requests Hiding records from a found set and viewing hidden records Finding and replacing data Sorting records Previewing and printing files Previewing data on a layout Printing records Printing table and field information Printing the relationships graph Backing up files Setting preferences Chapte
Working with fields on a layout Placing and removing fields on a layout Setting up fields to display 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 layout parts About layout part types Creating and editing charts Creating a chart Editing a chart 72 72 74 75 75 76 78 78 80 81 82 83 Chapter 4 Working with related tables and files 84 About relationships Relation
Working with external data sources Saving and sending data in other formats Sending email messages based on record data Publishing solutions on the web Chapter 6 Protecting files Protecting databases with accounts and privilege sets 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 Creating and editing accounts Creating accounts t
Chapter 1 Introducing FileMaker Pro FileMaker® Pro is the leading workgroup 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 in business, government, and education who needs to effortlessly manage all their information on iPad, iPhone, Windows, OS X, and the web.
Chapter 1 | Introducing FileMaker Pro 8 Getting Started tour You can explore FileMaker Pro through the interactive Getting Started tour. Practice creating, modifying, and deleting records; and learn about searching for data values and sorting records, displaying data in charts and reports, and importing and exporting data between files. You'll also get to know the rich features FileMaker Pro provides for creating your own custom solutions.
Chapter 1 | Introducing FileMaker Pro 9 A solution consisting of one table Portal A solution consisting of multiple tables, with a portal to display records from a related table You can organize your solutions as a single table in a single file, as multiple tables in a single file, or as multiple tables in multiple files. You create solutions using FileMaker Pro on a Windows or Mac computer. Or use FileMaker Pro Advanced for additional development and customization tools.
Chapter 1 | Introducing FileMaker Pro 10 Not all information needs to be stored in your FileMaker Pro file. For example, you can add a web viewer to display a web page on a layout, or use data that’s stored externally in a container field on a layout. There are some restrictions for displaying web content in FileMaker Pro. Choose different layout types in the New Layout/Report assistant.
Chapter 1 | Introducing FileMaker Pro 11 You can search for and replace data across multiple fields, similar to the way you find and replace data in word processing applications. You can also save a found set of records as a snapshot link and send the link to another person. For more information, see “Finding records” on page 28, “Sorting records” on page 32, and “Finding and replacing data” on page 32.
Chapter 1 | Introducing FileMaker Pro 12 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 or FileMaker Go users on a network and import or export data from another file. In addition, you can use ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) to share FileMaker Pro files with ODBC- and JDBC-compliant applications, or you can access data from external data sources.
Chapter 2 Using FileMaker Pro 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 file add and edit data in fields find records using different criteria omit records from the found set find and replace data sort data print data, scripts, table and field information, and the relationships graph use scripts See Help for detailed, comprehensive information and step-by-step procedures about using FileMaker Pro.
Chapter 2 | Using FileMaker Pro 14 B C Records Michelle Cannon 123 4th St. SW Edmonton Fields CAN N4V 1LN (717) 555-0011 Your address book is like a database An invoicing system is an example of a more complex database, with multiple related tables, multiple layouts, and calculated fields. You can also view a subset of your data; for example, view only invoices for a particular month.
Chapter 2 | Using FileMaker Pro 15 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 many other ways. FileMaker Pro also allows you to access and view data that is stored in non-FileMaker Pro files.
Chapter 2 | Using FileMaker Pro 16 To open a file that is being shared over a network 1. Choose File menu > Open Remote. The Launch Center window opens to the Hosts tab. If hosts aren’t displayed, click . Favorite hosts appear first in the list. 2. To see the files available from a host, click the host name. To view files in a list, click . To view files as tiles, click . To add or edit hosts, see Help. 3. Select the file you want to open, then click Open (or double-click the filename).
Chapter 2 | Using FileMaker Pro 17 Layout pop-up menu The original window, in Browse mode, in which you can view the records A second window, in Find mode, in which you can perform a different search on the same found set To view a different layout in the new window, choose a layout from the Layout pop-up menu in the layout bar, or choose View menu > Go to Layout. For information on finding records, see “Finding records” on page 28. For information on sorting records, see “Sorting records” on page 32.
Chapter 2 | Using FileMaker Pro 18 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 or from the mode pop-up menu at the bottom of the document window. Mode pop-up menu 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.
Chapter 2 | Using FileMaker Pro 19 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 Do this in Browse mode or Find mode Individually Choose View menu > View as Form, or click Form View In a list Choose View menu > View as List, or click List View In a spreadsheet-like table Choose View menu > View as Table, or click Table View in the layout bar.
Chapter 2 | Using FileMaker Pro 20 The following illustration shows the same layout in three views.
Chapter 2 | Using FileMaker Pro 21 Selecting the current record In Browse mode, a table has one current (active or selected) record at a time for each window. For more information about moving through records, see “Moving through records” on page 21. In this view The current record is Form View The record that you’re displaying. To select another record and make it the current record, move to the other record. List View The record that has a different fill from the other records.
Chapter 2 | Using FileMaker Pro 22 To move Do this To the next record in the current table Click or choose Records menu > Go to Record > Next. To the previous record in the current table Click or choose Records menu > Go to Record > Previous. Quickly to a specific record 1 Click the current record number, type the record number, then press Enter (or Return. 1 With the current record selected, press Esc, type the record number, then press Enter or Return.
Chapter 2 | Using FileMaker Pro 23 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.
Chapter 2 | Using FileMaker Pro 24 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.
Chapter 2 | Using FileMaker Pro 25 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. Note For information about how FileMaker Pro uses system formats to display and sort dates, times, and numbers, see Help. To Click in the field in Browse mode, then Enter data in a text field Type the text.
Chapter 2 | Using FileMaker Pro 26 To Click in the field in Browse mode, then Add data to value lists (fields formatted as radio or option buttons, checkboxes, lists, or menus) For information, see “Setting up fields to display checkbox sets and other controls on a layout” on page 74. Edit, format, or check the spelling of text in a field or edit dictionaries See Help.
Chapter 2 | Using FileMaker Pro 27 Working with records in Table View When you view records in a table, FileMaker Pro displays data in rows and columns. Each row displays a record, and each column displays a field. Note You can set field options such as validation or indexing when you define a field, or at a later time. See “Setting options for fields” on page 50. To Do this in Browse mode Add a new record Click + in the left margin at the bottom of the table.
Chapter 2 | Using FileMaker Pro 28 Finding records To find particular records in your file, you can: 1 perform a quick find in Browse mode 1 make a find request in Find mode Performing quick finds in Browse mode If a layout is enabled for quick find in Browse mode, you can use the search box in Browse mode to search for data across multiple fields on the layout. Quick find is helpful if the data you are looking for might be in multiple fields or if you don’t know which field contains the data.
Chapter 2 | Using FileMaker Pro 29 To configure and enable quick find If the selected layout doesn’t show the quick find badges for supported fields in Layout mode, use the following steps. 1. In Layout mode, choose View menu > Show > Quick Find. A small badge next to each field shows if the field has been enabled for quick find.
Chapter 2 | Using FileMaker Pro 30 To make a find request 1. In Find mode, from the Layout pop-up menu, select a layout that includes the fields that contain the data to search. If necessary, you can change layouts and enter criteria on more than one layout. 2. In the find request, select a text, number, date, time, timestamp, or calculation field to use for finding, and then type a value in the field. You can use the Insert Operators list in the layout bar to help you enter criteria.
Chapter 2 | Using FileMaker Pro 31 You can do the following during or after performing a find request: To Do this Cancel a find operation before it is finished and leave the previous found set unchanged Press Esc (Windows) or 2-period (OS X). Show all records again In Browse mode, choose Records menu > Show All Records. In Find mode, choose Requests menu > Show All Records. You don’t need to choose Show All Records before performing finds.
Chapter 2 | Using FileMaker Pro 32 Saving find requests When you have a complex find request that you want to use regularly, you can save the request and retrieve it when you need it later. You can save and retrieve find requests without having to go to Find mode. FileMaker Pro automatically saves the last five finds you have performed so you can access them again. Once a find is saved, the saved find is also available to any network clients that share the account.
Chapter 2 | Using FileMaker Pro 33 To sort the records in the current found set: 1. In Browse mode, click Sort in the status toolbar. Note To sort records in Table View in Browse mode, see “Working with records in Table View” on page 27. 2. In the Sort Records dialog box, choose fields for sorting, in the order you want them sorted, and sort options.
Chapter 2 | Using FileMaker Pro 34 Contacts Name Juanita Alvarez Michelle Cannon Andre Common Marie Durand Jean Durand William Johnson Phone 408-555-1234 213-555-2345 714-555-3456 619-555-4567 408-555-5678 408-555-6789 Contacts Name Juanita Alvarez Phone 408-555-1234 Contacts Name Phone Records being browsed Current record Blank record Print Phone List Go to Layout [Contacts] Sort [Sort Order: Last name (Ascending) ] [Restore sort order, No Dialog] Enter Preview Mode [Pause] Print [no dialog] Enter B
Chapter 2 | Using FileMaker Pro 35 Printing records You can print information from your file, or you can print a blank record in order to print a blank “form.” 1. Specify printer and confirm printing options by choosing File menu > Print Setup (Windows) or File menu > Page Setup (OS X), then click OK. In Preview mode, you can also click Print Setup (Windows) or Page Setup (OS X) in the status toolbar. For information about printer options, refer to your printer and system documentation. 2.
Chapter 2 | Using FileMaker Pro 36 6. In the Printing Options dialog box, choose to print on one page or multiple pages, then click Continue. 7. Click OK (Windows) or Print (OS X). Backing up files Routine backups are strongly recommended for any document stored on a computer. It is easier to restore a backed-up file than it is to re-create it.
Chapter 3 Designing and creating solutions This chapter explains the basics of how to: 1 1 1 1 1 plan a FileMaker Pro solution 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-by-step procedures about using FileMaker Pro. Planning a solution The first step in creating a solution is to plan the content, structure, and design.
Chapter 3 | Designing and creating solutions 38 4. After you’ve identified your categories of information, you are ready to organize your database. In database terminology, these categories of information are referred to as tables. Tables are used to group data containing a common element or purpose. For example, you might use one table to store names and addresses, while you use another table to store transaction details, such as date of sale, item number, unit price, and so on.
Chapter 3 | Designing and creating solutions 39 A Products table might have fields for a product identification number, the unit price for each product, and the quantity in stock. A Line Items table might have fields for product and invoice identification numbers, the name, unit price, quantity, and total price of each product sold. An Invoices table might have fields for an invoice identification number, order date, and salesperson.
Chapter 3 | Designing and creating solutions 40 8. For each table, decide which fields will store data and which ones will be used from other (related) tables. Based on a table’s subject, you can see where it makes sense to store the data and where to use data from a related table. Other than match fields, all fields should appear only once in your database. Cross out occurrences of fields that don’t pertain to the table’s subject.
Chapter 3 | Designing and creating solutions 41 12. Consider who will use the database and whether you want to restrict access to it. When you create the database, assign access privileges as needed. For more information about accounts and privilege sets, see chapter 6, “Protecting files.” 13. Decide what layouts you need, and plan a separate layout for each task.
Chapter 3 | Designing and creating solutions 42 3. Type a name for the file (choose a different folder, if needed), then click Save. Windows: For Save as type, select FileMaker Files. 4. Click Save. FileMaker Pro creates the file on your disk and opens the file.The pre-designed solution contains fields and buttons set up for tasks that are commonly performed, but it contains no data. You can modify fields and layouts in Starter Solutions. For more information, see the remaining sections in this chapter.
Chapter 3 | Designing and creating solutions 43 Creating and changing fields To define a new field in the database, you give it a name and select a field type. Then you select options that determine how the field interprets, enters, calculates, stores, and displays data. These characteristics make up the field definition. Product ID FB11 Product Name Focaccia Category Breads Flavor Plain Herb This field stores multiple values Unit Price $6.95 This field stores only numbers Discount $1.
Chapter 3 | Designing and creating solutions 44 About choosing a field type When you define a field, you select a field type based on the kind of information the field will contain. The field type determines what kind of data can be entered and what kinds of operations FileMaker Pro can perform with the data. FileMaker Pro uses the field type to interpret the data for tasks like sorting records and performing calculations.
Chapter 3 | Designing and creating solutions 45 Sales Report Salesperson Item Qty Extended Price David Michaels David Michaels David Michaels David Michaels FB3 FB4 FB1 FB5 1 1 2 3 Total $93.85 Sophie Tang Sophie Tang Sophie Tang FB6 FB7 FB2 2 5 2 $39.00 $77.50 $45.00 Total Grand Total $3.95 $17.50 $19.90 $52.50 $161.50 $255.
Chapter 3 | Designing and creating solutions 46 Creating and changing fields in the Field Picker dialog box When you create a new file, FileMaker Pro opens the file in Layout mode, and displays a blank layout and the Field Picker dialog box. Use the Field Picker dialog box to define fields for the file, and to add or change fields for an existing layout. Note Fields are not automatically added to the layout when you define them in the Field Picker dialog box.
Chapter 3 | Designing and creating solutions 47 To Do this Sort fields in the list Click Locate a field in a long list Type the field name in the search box near the top of the Field Picker dialog box. , then choose how to sort the fields. 3. Repeat step 2 to continue defining fields. 4. When you drag fields to the layout, by default the fields are placed vertically on the layout, with field labels to the left of the fields.
Chapter 3 | Designing and creating solutions 48 Creating calculation fields 1. In Layout mode, click . 2. In the Field Picker dialog box, click New Field and type a name for the calculation field. 3. Click the field type and choose Calculation. 4. In the Specify Calculation dialog box, if necessary, for Evaluate this calculation from the context of, choose a table from the list.
Chapter 3 | Designing and creating solutions 49 Choose the table from which the calculation will be evaluated Choose operators Search for a function Choose a table Choose field references Choose functions Formula box Choose a formula result type Make the calculated field repeating Calculate only if at least one field in the formula has a value Choose indexing and storage options To Do this Set the field type of the result Choose a data type for Calculation result is .
Chapter 3 | Designing and creating solutions 50 The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, whether the records are sorted, and which mode you’re using. If you’re modifying a layout that contains a summary field, you must know about layout parts to get the results you want (see “Working with layout parts” on page 78). 1. In Layout mode, click . 2.
Chapter 3 | Designing and creating solutions 51 3. In the Options for Field dialog box, click a tab. To Click Automatically enter a default value into a field for each record Auto-Enter Define field validation options to ensure that data is entered into the field correctly.
Chapter 3 | Designing and creating solutions 52 . Click Storage Select the storage option for the field Storage options for all field types except container fields Select storage options for a container field Storage options for container fields For information on value lists, see “Setting up fields to display checkbox sets and other controls on a layout” on page 74. About indexing and storage options FileMaker Pro provides options for indexing fields and for storing the results of calculations.
Chapter 3 | Designing and creating solutions 53 1 Set any field (except summary fields) to share one value across all records in a file if you select Use global storage in the Storage tab of the Options for Field dialog box. Fields defined with global storage are also referred to as global fields. 1 Store data for a container field in a location that’s external to the file, such as in a different folder or directory on the local or a host computer.
Chapter 3 | Designing and creating solutions 54 Creating layouts and reports FileMaker Pro layouts determine how information is organized for viewing, printing, reporting, finding, and entering data. Layouts don’t store your data—they just display it. Layouts are sometimes called reports, especially when printed. You can create as many layouts as you need for a file (for example, layouts for entering data, reporting summaries, or printing mailing labels).
Chapter 3 | Designing and creating solutions 55 Keep the following points in mind: 1 If you are designing solutions for use with FileMaker Go or FileMaker WebDirect, see the design guidelines in FileMaker Go Development Guide or FileMaker WebDirect Guide.
Chapter 3 | Designing and creating solutions 56 Keep the following points in mind: 1 You can use the tools and commands in Layout mode to further customize the layout. See “Working with objects on a layout” on page 66 and “Working with fields on a layout” on page 72. 1 If you click Finish in the first panel of the New Layout/Report assistant without choosing a layout type, FileMaker Pro creates a blank layout. A blank layout contains no fields; you add the fields in Layout mode.
Chapter 3 | Designing and creating solutions 57 Labels Use a Labels layout to arrange fields you select to print on one of the predefined standard label sizes. (FileMaker Pro includes the dimensions of a large number of standard label types.) If the label type isn't available, you can specify custom label dimensions. You can only print (or preview) data; you cannot add information. (Use another layout type for data entry or finding data.
Chapter 3 | Designing and creating solutions 58 Envelopes Use an Envelopes layout to print the fields you select, arranged to print on a standard “Number 10” business envelope. You can only print (or preview) data; you can't add information. (Use another layout type for data entry or finding data.) For more information about creating an Envelopes layout and printing on envelopes, see “Printing the labels or envelope” on page 59.
Chapter 3 | Designing and creating solutions 59 Labels layouts and Envelopes layouts use merge fields, which are placeholder fields for displaying and printing only; you can’t enter data into merge fields. Merge fields expand and contract to fit the data in the field as needed, and close up space if the field is empty. (See “About merge fields” on page 75.) Setting up the layout 1. In Layout mode, click New Layout/Report. The New Layout/Report assistant appears.
Chapter 3 | Designing and creating solutions 60 Keep the following points in mind: 1 To specify how labels will print on the page, choose Layouts menu > Layout Setup, and click the Printing tab. Then choose the number of columns (labels) across and the direction in which you want the labels printed, and set custom page margins, if needed. 1 You may need to adjust your printer settings to print the labels correctly. Choose File menu > Print Setup (Windows) or Page Setup (OS X).
Chapter 3 | Designing and creating solutions 61 Keep the following points in mind: 1 When you change a layout’s theme, FileMaker Pro applies the attributes of the new theme to all objects on the layout except the following: 1 The font and font size remain from the original layout or theme. 1 Buttons created using versions of FileMaker Pro earlier than version 12 retain their original formatting.
Chapter 3 | Designing and creating solutions 62 You can modify default styles or design new styles and save them for use on the current layout. When you edit and save formatting attributes for styles that are applied to multiple objects or layout parts, your changes are applied to all objects and parts that use those styles on the current layout. You can also create new styles with custom names, which you can then apply to objects, layout parts, or the layout background on the current layout.
Chapter 3 | Designing and creating solutions 63 Use the Appearance tab in the Inspector to create and modify styles and themes.
Chapter 3 | Designing and creating solutions 64 You can choose to arrange columns across the page or down the page. 1 5 1 2 2 6 3 4 3 7 5 6 4 8 7 8 Down first Across first Setting up columns in a layout 1. Choose a printer, and print setup or page setup options. FileMaker Pro considers the selected printer, and print or page setup information when it calculates margins and other measurements on the layout. 2.
Chapter 3 | Designing and creating solutions 65 6. Place or arrange fields and other layout objects so they are contained within the sample column on the left. Use merge fields or fields or objects with sliding enabled to fit more data into the width of a column. With either of these two features, fields can extend into the gray area of the second column, and blank space in fields is eliminated when you view or print data.
Chapter 3 | Designing and creating solutions 66 Working with objects on a layout An object is a discrete element—a field, text, a graphic object (such as an oval or imported picture), a button, a button bar, a portal (for displaying rows of related records), a tab control or slide control, a popover button and its popover, a chart, or a web viewer—that you can select, move, resize, delete, copy, format, or otherwise change. Place objects on a layout to enhance its design.
Chapter 3 | Designing and creating solutions 67 1 To create a slide control, in Layout mode, choose Insert menu > Slide Control or click the Slide Control tool . Slide panel Slide control Click a dot to display another slide panel Slide control in Browse mode 1 To create a popover, in Layout mode, choose Insert menu > Popover Button or click the Popover Button tool .
Chapter 3 | Designing and creating solutions 68 Using the Inspector to format objects In Layout mode, you can use the Inspector to view and modify the settings for objects. Each tab focuses on a different aspect of formatting. Hold your pointer over options in each tab to see a description of what they do. To open the Inspector: 1 Click Inspector in the layout bar. 1 Choose View menu > Inspector.
Chapter 3 | Designing and creating solutions 69 Using tools to precisely position objects FileMaker Pro provides several tools to help you precisely create, size, reshape, move, and position objects in Layout mode. To show or hide page breaks in Layout mode, choose View menu > Page Breaks. Note To use the Inspector to position objects, see Help. Rulers Showing the rulers displays a horizontal and vertical ruler along the edge of a layout.
Chapter 3 | Designing and creating solutions 70 Dynamic guides When you drag objects to move, resize, or position them on a layout, dynamic guides appear near or on the objects to help you create and edit them more precisely. Dynamic guides also “snap to” the upper and lower boundaries and the centers of objects as you move, resize, or position them. Dynamic guides Dynamic guides also appear when you use the keyboard to move objects, but they do not “snap to” objects when you move them this way.
Chapter 3 | Designing and creating solutions 71 Selecting objects To work with an object on a layout, select it in Layout mode. You see small squares, or handles, surrounding the selected object. Handles surround a selected object Note If an object’s selection handles display as To select an object, click the Selection tool arrow pointer. Then do one of the following. , the object is locked and can’t be changed. in the status toolbar.
Chapter 3 | Designing and creating solutions 72 Working with fields on a layout After you’ve added fields to a file, you can place the fields on a layout, remove fields, determine control settings for data entered in fields, and determine a display format for data. 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 3 | Designing and creating solutions 73 Placing a field on a layout 1. In Layout mode, choose the layout from the Layout pop-up menu. 2. Do one of the following: 1 Drag a field from the Field tool in the status toolbar to the layout. As you drag the field to the layout, you see a border and text baselines to help you align the field with other objects on the layout. Release the mouse button when the field is where you want it.
Chapter 3 | Designing and creating solutions 74 Removing a field from a layout In Layout mode, click the field to select it, then press Backspace or Delete, or choose Edit menu > Clear. Removing a field from a layout does not delete the field or its data from your database. Setting up fields to display checkbox sets and other controls on a layout You can format fields to display as edit boxes, drop-down lists, pop-up menus, checkbox sets, radio button sets, and drop-down calendars.
Chapter 3 | Designing and creating solutions 75 Deciding where to place related fields You can place related fields directly on layouts or in portals. 1 Directly on layouts: Place related fields directly on a layout to display data from the first related record, even when there are multiple related records that match the criteria of the relationship. (The first related record that’s displayed is determined by whether the relationship specifies a sort order.
Chapter 3 | Designing and creating solutions 76 Formatting field data on a layout Use FileMaker Pro field formats to control how text, numeric, date, time, and timestamp data appears on a layout, how results are displayed in calculation and summary fields, and how images and file icons are displayed in container fields.
Chapter 3 | Designing and creating solutions 77 1. In Layout mode, select one or more fields. 2. Click Inspector in the layout bar, click Appearance, then specify formats for the field.
Chapter 3 | Designing and creating solutions 78 Allowing or preventing entry into fields 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 finding serialized numbers even though they can’t be entered.
Chapter 3 | Designing and creating solutions 79 Header Body Subsummary Trailing grand summary Footer Display part labels vertically or horizontally Sales Report A subsummary below the body summarizes data for each group of records sorted on the break field (Salesperson) Trailing grand summary Footer Salesperson Item Qty David Michaels David Michaels David Michaels David Michaels FB3 FB4 FB1 FB5 1 1 2 3 Total Extended Price $3.95 $17.50 $19.90 $52.50 $93.
Chapter 3 | Designing and creating solutions 80 About layout part types The following table describes layout part types. Layout part Description Top navigation Appears at the top of every screen and can contain buttons or other controls. This part cannot be scrolled or zoomed in or out, and it does not print or display in Preview mode. Fields added to a top navigation part use data from the current record. You can have only one top navigation part on a layout.
Chapter 3 | Designing and creating solutions 81 Place the Product Name break field in a subsummary part in Layout mode The number of items for each product in stock is displayed in Browse and Preview modes or when data is printed as a report Subsummary parts appear in Table View and List View when sorted by break fields, and update dynamically whenever data in the file is changed. Creating and editing charts You can create various types of charts in FileMaker Pro to compare data graphically.
Chapter 3 | Designing and creating solutions 82 Use To Area chart Compare data that trends over time while emphasizing highs, lows, and movement between data points. For example, show sales totals or averages. Scatter chart Plot x and y coordinates as individual values to reveal correlation patterns. For example, compare target wait time to actual wait time at a call center. Bubble chart Compare individual data points in terms of size.
Chapter 3 | Designing and creating solutions 83 4. You can choose settings in the Chart inspector on the right to modify the chart, and preview the result of your changes in the Chart Setup dialog box. To Do this Add a title to the chart With the Chart options displayed, for Title, type or change the chart title, or click to specify a title. For example, “Accounts by Company.” Choose a chart type and specify the data series For Type, choose a chart type. For example, choose Pie.
Chapter 4 Working with related tables and files This chapter explains the basics of how to: 1 plan a relational database 1 create and edit relationships 1 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 when you change data in one place, your changes are reflected in all places where the related data appears.
Chapter 4 | Working with related tables and files 85 About relationships A relationship is a powerful method for organizing your data. Using a relationship, you can join data in one or more tables based on common field values, different field values, or a comparison of values in two or more fields.
Chapter 4 | Working with related tables and files 86 For example, a database for a bakery might have these tables: a Products table, which stores information about bakery products and their current prices; a Customers table, which stores customer information such as customer names and contact information; an Invoices table, which keeps a record of each order; and a LineItems table, which stores sales data for each line of the invoice, including the item being sold and the price at which it is sold.
Chapter 4 | Working with related tables and files 87 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.
Chapter 4 | Working with related tables and files 88 About the relationships graph When you work with tables in the relationships graph, you organize your view of your data. Tables shown in the relationships graph can be located in the current FileMaker Pro file, in an external FileMaker Pro file, or in an external ODBC data source. Each table occurrence in the relationships graph represents a separate view into your data.
Chapter 4 | Working with related tables and files 89 Single-criteria relationships In a single-criteria relationship, data in one field is matched to data in another field. For example, a record in either table is related to any record in the other table when the values TextFieldA and TextFieldB are the same. Match field Match field Use a single-criteria relationship to relate two tables based on a single common value, such as a serial number or an ID number.
Chapter 4 | Working with related tables and files Match field 90 Match field Because the relationship is defined to allow the creation of related records in the Contacts table, the Customer ID number from a Customers record will be automatically inserted in the Customer ID field in the Contacts table in each new related record. This allows many records in the Contacts table to be related to a single record in the Customers table. You can then add a portal to the Contacts table on the Customer layout.
Chapter 4 | Working with related tables and files 91 Match fields Match fields In this relationship, both Customer ID and Event Date are match fields to the Catering Line Items table. A record in the Customers table with a Customer ID value of 1000 and a Event Date value of 10/10/2014 will only match those records in the Catering Line Items table where both fields Customer ID and Event Date have values of 1000 and 10/10/2014, respectively.
Chapter 4 | Working with related tables and files 92 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 field Greater than relational operator In this example, the Promotion Date fields in the Sales Promotions table and the Customers table are match fields.
Chapter 4 | Working with related tables and files 93 For example: If fields Contain values These related records are returned from Table F Starting Date E 11/01/2014 Ending Date E 12/01/2014 11/14/2014 11/27/2014 Another example: If fields Contain values These related records are returned from Table F Starting Date E 12/01/2014 Ending Date E 01/01/2015 12/02/2014 12/15/2014 12/17/2014 12/26/2014 Self-joining relationships A self-join is a relationship in which both match fields are defined
Chapter 4 | Working with related tables and files 94 Creating relationships To create a relationship: 1. With the database open, choose File menu > Manage > Database. 2. In the Manage Database dialog box, click the Relationships tab. 3. In the relationships graph, locate the two tables you will be using for this relationship. They can appear anywhere on the graph but they must be present on the graph in order to create the relationship.
Chapter 4 | Working with related tables and files 95 3. In the relationships graph, locate the relationship to edit, and double-click the indicator line between the related tables to open the Edit Relationship dialog box. Or, you can select the relationship in the graph and click box. to open the Edit Relationship dialog Select the relational operator Select options for the tables in the relationship 4. Edit tables and match fields for the relationship.
Chapter 4 | Working with related tables and files 96 About lookups A lookup copies data from another table into a field in the current table. After data is copied, it becomes part of the current table (and remains in the table from which it was copied). Data copied to the current table does not change automatically when the data in the other table changes, unless the data in the match field of the current table changes. To establish a connection between tables for a lookup, you create a relationship.
Chapter 4 | Working with related tables and files 97 10. Select options for the lookup. To Do this Specify an action when values in the match fields do not match exactly For If no exact match, then, select an option to copy no value, copy the next lower or next higher value that’s in the lookup source field, or enter a fixed value to be used instead Prevent null (empty) data in the lookup source field from being copied to the lookup target field Select Don’t copy contents if empty.
Chapter 5 Sharing 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 up to 5 FileMaker Pro and FileMaker Go users to access and use the same database file simultaneously. If you want to share a database with more than 5 users, host the database using FileMaker Server.
Chapter 5 | Sharing data 99 The first person to open a shared file is the host. Any user who opens a shared file after the host is a client. The host opens the file Clients open connections to the shared file Once a shared file is open, the host and clients have access to the same information, and all changes to the file appear in each user’s window. Modifications to the data made by any user are saved in the shared file. The shared file is saved on the disk where the file resides.
Chapter 5 | Sharing data 100 3. In the FileMaker Network Settings dialog box, for Network Sharing, click On. For TCP/IP Address, you see the TCP/IP address of your system. Click to enable network sharing Choose the level of network access for the selected file(s) 4. Select the file(s) to be hosted from the list of Currently open files. 5. Set the level of network access for the file(s). To Select Provide access to all FileMaker Pro and FileMaker Go users on your network All users.
Chapter 5 | Sharing data 101 Opening shared files as a client 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). Note You might be required to enter an account name, password, and domain name (Windows only), depending on how file sharing is configured on the host. To open a shared file using a local host: 1. Choose File menu > Open Remote.
Chapter 5 | Sharing data 102 1 After the file is open, in the lower-left corner of the FileMaker Pro window: 1 No icon means the connection is not encrypted. 1 means the connection is encrypted, but the SSL certificate for FileMaker Server cannot be verified. You may be connected to a server pretending to be your actual destination, which could put your confidential information at risk. 1 means the connection is encrypted with a verified SSL certificate.
Chapter 5 | Sharing data 103 2. If any clients have the shared file open, you see a dialog box listing those clients. Click Ask to send a message to these clients requesting that they close the file.
Chapter 5 | Sharing data 104 ODBC and JDBC You can exchange FileMaker Pro data with ODBC- and JDBC-compliant applications. For example, you can chart FileMaker Pro data in Microsoft Excel. What do you want to do? How do you do it? See Use FileMaker Pro as a data source/share FileMaker Pro data 1. ODBC and JDBC 2. SQL queries FileMaker ODBC and JDBC Guide and FileMaker SQL Reference Use FileMaker Pro as an ODBC client/access ODBC data 1. Interactively via the relationships graph 2.
Chapter 5 | Sharing data 105 About updating matching records You can update matching records and fields in your target file with data from another file. For example, you might have a copy of a database on your desktop computer and another copy on your laptop computer. You can update the file in your office with the changes you make on the road. You determine which records in the source file update which records in the target file by choosing one or more match fields in each file.
Chapter 5 | Sharing data 106 5. Choose File menu > Import Records > File, and choose the name of the file that contains the data to import (the source file). 6. In the Import Field Mapping dialog box, if necessary, select a table from the Source list. 7. Accept the table shown in the Target 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 104. 9.
Chapter 5 | Sharing data 107 1 Converted field names if they are available in the file or source you convert. Otherwise, field names are generic: f1, f2, f3, and so on. 1 Converted field types if they are available in the file or source you convert. Otherwise, all fields are text fields. To import data into a new file: 1. In the source application, save the data to import in a file type that FileMaker Pro supports. See “Supported import/export file formats” on page 103. 2.
Chapter 5 | Sharing data 108 7. Select whether or not to apply the current layout’s formatting to the exported data.
Chapter 5 | Sharing data 109 Saving and sending data in other formats You can save FileMaker Pro data as a Microsoft Excel worksheet or a PDF file, allowing you to give your record data to users who don’t have FileMaker Pro. FileMaker Pro lets you email the Excel worksheet or PDF file when you save it. You can also create scripts to save records as Excel or PDF. In order to save files as Excel, you need the Allow Exporting Access privilege.
Chapter 6 Protecting files This chapter describes the basics of how to restrict what users can see and do in a FileMaker Pro file.
Chapter 6 | Protecting files 111 The privileges that you set up apply to a single file only and all database tables within that file. If your database solution consists of multiple files that you want to protect, you may want to combine all of these files into one multi-table file. Then you can define privileges in only a single file to manage access to the entire database solution.
Chapter 6 | Protecting files 112 The following table lists the default extended privileges that are available. (FileMaker and thirdparty developers may define additional extended privileges to manage access for other software products designed to work with FileMaker Pro or FileMaker Server.) Keyword Extended privilege Determines if a privilege permits fmwebdirect Access via FileMaker WebDirect Accessing a database file from a web browser via FileMaker WebDirect.
Chapter 6 | Protecting files 113 Guest account The Guest account permits users to open a file without specifying any account information. The Guest account is not assigned a password. By default, the Guest account is assigned the ReadOnly Access privilege set, but you can assign any privilege set to the Guest account. Initially, the Guest account is inactive, which disables the guest option in the Open dialog box and prohibits users from opening files as a guest.
Chapter 6 | Protecting files 114 The following table summarizes the properties of these privilege sets.
Chapter 6 | Protecting files 115 1 If you need to share a database file with others and provide varying levels of file access to different users, you need to plan the security for the file.
Chapter 6 | Protecting files 116 Password-protecting a file After password-protecting a file, you will be prompted to enter an account name and password every time you open the file. Anyone who does not know this account information will not be able to open the file. To password-protect a file: 1. Make the following changes to the accounts in the file: 1 Edit the Admin account so that it has a different account name, a password, and uses the Full Access privilege set.
Chapter 6 | Protecting files 117 Viewing accounts 1. With the file open, choose File menu > Manage > Security. The Manage Security dialog box appears. If the detailed security settings are displayed, click Use Basic Setup. 2. You see the accounts defined for this file on the left. Accounts defined for the file 3. Click OK.
Chapter 6 | Protecting files 118 5. For Account Name, enter or change the account name. Account names are not case-sensitive. 6. To define or change the password, for Password, click Change. Then, enter and confirm the new password, and click Set Password. 7. To force the account user to create a new password during the next login, select Require password change on next login. 8. For Privilege Set, do one of the following: 1 Choose a privilege set. 1 Choose New Privilege Set, then create a new one.
Chapter 6 | Protecting files 119 To create a privilege set: 1. With the file open, choose File menu > Manage > Security. If the Manage Security dialog box displays the basic security settings, click Use Detailed Setup. 2. Click the Privilege Sets tab. By default, each privilege set option is set to its most restrictive setting. Click to create a new privilege set 3. To create a new privilege set, click New. To edit an existing privilege set, select the privilege set to edit, then click Edit. 4.
Chapter 6 | Protecting files 120 Viewing extended privileges Extended privileges determine the data sharing options that are permitted by a privilege set for a file. For example, if the file is shared, the Access via FileMaker Network extended privilege determines if the privilege set allows opening the shared file as a client. The Access via FileMaker WebDirect extended privilege determines whether the privilege set allows accessing the database from a web browser.
Chapter 6 | Protecting files 121 5. To authorize additional files that are not currently open, click Authorize. In the Open File dialog box, choose a local or remote file to authorize, and click Open. You may be asked to enter the name and password of an account with Full Access privileges. 6. Click OK. Security measures Be sure that your database files, host computers, workstations, and the networks that access them are safe from theft and corruption.
Chapter 6 | Protecting files 122 Establishing network security Databases shared on an intranet or the Internet use the TCP/IP protocol. You may also use the TCP/IP protocol when you share databases peer-to-peer, or with FileMaker Server. Though TCP/IP is good for moving data and allowing clients to connect to your data, it was not designed with security as a primary objective.
Chapter 6 | Protecting files 123 1 Plan for redundancy. If the power goes off, a universal power supply (UPS) should sustain power for at least 15 minutes, enabling you to safely close all files. If the power can’t be restored in a timely fashion, consider using a generator to supply power to servers. Also consider power sources for routers and firewalls.
Chapter 6 | Protecting files 124 This situation also applies to files that FileMaker Server uses as backup targets. If the backup file is being scanned by antivirus software when a scheduled backup occurs, the destination file might be locked. This causes the backup process to fail, which may result in data loss. This same data loss issue applies when an open, non-shared FileMaker Pro database file is scanned by antivirus software.
Chapter 7 Using FileMaker Pro Advanced FileMaker® Pro Advanced includes advanced development and customization tools designed especially for solution developers. You can use either FileMaker Pro or FileMaker Pro Advanced to create and test your solutions.
Chapter 7 | Using FileMaker Pro Advanced 126 2. Choose Tools menu > Developer Utilities. 3. Click Add to locate the files you want to customize. 4. Choose a location for the runtime solution. 5. Select options for the runtime solution. 6. Click Create. Binding the solution To bind database files into a runtime database solution: 1. In the Specify Solution Options dialog box, select Create Runtime Solution application(s). 2. Name your runtime solution. 3. Create a bindkey.
Chapter 7 | Using FileMaker Pro Advanced 127 Organizing solution components When you bind your files into a runtime solution, the Developer Utilities create a new solution folder and place the runtime application, the bound primary and auxiliary database files, and an Extensions folder inside it. For Windows runtime solutions, there are also required Dynamic Link Library (DLL) files.
Chapter 7 | Using FileMaker Pro Advanced 128 1 Verify that all scripts and buttons work as expected. This is especially important if you’re displaying your solution in Kiosk mode. 1 Verify your installation procedures and test other instructions in the documentation. 1 Verify that your layouts display well on monitors with different color capabilities and resolutions and on the smallest size monitor your users may be using. 1 Test your runtime solution with actual data.
Chapter 7 | Using FileMaker Pro Advanced 129 To distribute a new or updated auxiliary file: 1. In FileMaker Pro Advanced, create the new auxiliary file or open the original auxiliary file (before it was bound), and make changes as required. 2. If necessary, create an Import script so users can import their existing data into the new file. For more information about importing data into upgraded runtime solutions, see Help. 3.
Chapter 7 | Using FileMaker Pro Advanced 130 To create a custom function: 1. Choose File menu > Manage > Custom Functions. 2. Click New. Functions list Parameters list Formula box Your custom parameters appear in this space 3. In the Edit Custom Function dialog box, type a name for the function and build a formula. Custom menus, menu items, and menu sets With FileMaker Pro Advanced, you can create custom menus, menu items, and menu sets for your solutions.
Chapter 7 | Using FileMaker Pro Advanced 131 About custom menus You can customize menus by: 1 editing a copy of a standard FileMaker menu. Use this method to make minor changes to existing menus—for example, to modify the properties of a few menu items. 1 starting with an empty menu. Use this method to make significant changes to menus—for example, to add menus and change menu item properties.
Chapter 7 | Using FileMaker Pro Advanced 132 About custom menu sets Custom menu sets are collections of menus that appear in the FileMaker Pro application menu bar. You can create custom menu sets to include only the menus you require.
Chapter 7 | Using FileMaker Pro Advanced 133 Monitoring expressions Use the Data Viewer to monitor expressions like field values, local and global variables, and calculations. You can monitor these expressions while running scripts or while testing them in the Script Debugger. To monitor the currently running script: 1. Choose Tools menu > Data Viewer, or click in the Script Debugger. 2. Click the Current tab.
Chapter 7 | Using FileMaker Pro Advanced 134 2. Perform the following tasks: 1 To add an expression, click . In the Edit Expression dialog box, build an expression, then click Monitor to add the expression to the Data Viewer.
Chapter 7 | Using FileMaker Pro Advanced To create a database design report: 1. Choose Tools menu > Database Design Report. 2. Set options for the report. Select the files and tables to include Select the elements to include Choose the format of the published report Choose whether to open the report when it’s completed 3. Click Create.
Index Symbols – 43 ^ 43 _ 43 , 43 ; 43 : 43 :: 43 " 43, 48 " " 28 ( 43, 48 ) 43, 48 [ 43 ] 43 } 43 * 43 / 43 & 43 + 43 < 43 = 43 > 43 $ 43 ≤ 43 … 43 ≥ 43 A Access privileges.
Calendars, displaying in fields 74 Characters maximum in fields 25 not allowed in field names 43 Charting data creating charts 82 described 81 editing charts in Layout mode 83 quick charts 82 Checkbox sets, displaying fields as 74 Clients described 99 performing tasks 102 saving files 99 Closing windows and files 16 Color, changing for text 77 Column charts 81 Columns changing width 65 printing records in 63 resizing in tables 26 Comma-separated text format 103 Comparison operators 48 Constant values 4
Duplicating records 22, 27 related records 23 tables in the relationships graph 93 Dynamic guides, for designing layouts 70 Dynamic Link Libraries (DLLs) 127 Dynamic reports, creating in Table View 27, 49 E Edit boxes, displaying fields as 74 Elements.
Files See also Databases; Solutions accounts 111 authorizing access to protected files 120 backing up 36 closing 16 copying 17 creating 42 described 8, 14 encrypted connection (SSL) to host 101 exporting to 107 hosted 101 naming 42 opening 15, 101 planning 37 privilege sets 111 protecting 110 publishing on the web 109 saving 17 shared on web 109 planning security for 115 Find mode 18 Find requests See also Finding records described 29 editing 32 operators in 30 repeating 31 Finding records all records
K Key fields. See Match fields Kiosk solutions, creating 129 L Labels field 73 layouts for 57 mailing 58 part 78 printing 58 vertical 57 Labels layouts 57 Launch Center creating solutions 41, 42 opening files 15, 101 Layout background, setting styles for 61 Layout bar 17, 18 Layout folders, managing 55 Layout mode creating charts in 82 described 18 Layout objects.
ODBC overview 104 using FileMaker as client described 108 supplemental fields 50 using FileMaker as data source field name considerations 43 privileges for 112, 119 Omitting fields during printing 75 records from the found set 30 related records 31 Opening files 15, 101 Operators comparison 48 in find requests 30 mathematical 48 relational 87, 91, 95 Options field 50 file 36 OS X runtime application package 127 P Page breaks showing or hiding in Layout mode 69 showing or hiding in relationships graph
Records active 21 adding 22, 27, 104 browsing 19, 27 copying 22, 27 deleting 27 described 15 duplicating 22, 27 exporting 107 finding 28 hiding 32 locked 102 matching 105 moving through 21 printing in columns 63 selecting 21 sorting 27, 33, 95 viewing as forms, lists, or tables 19 Recurring imports 12 Related fields See also Fields; Relational databases calculations 48 described 87 on layouts 73 planning 40 Related files, restricting access 41 Related records See also Records; Relational databases addi
Shared files See also Networks accounts 115 clients 99 host 99 limitations 98 on web 109 privilege sets 115 saving 99 setting up privileges for 111 Shortcut menus, choosing from 13 Simple Mail Transfer Protocol (SMTP), sending email through 109 Single-page form layout.
Tools Field tool 73 Field/Control tool 73 for designing layouts 66 Popover Button tool 67 Selection tool 71 Slide Control tool 67 status toolbar 18 Tab Control tool 66 Top navigation part 80 Touch screens, designing solutions for 129 Triggers, script 11 Troubleshooting calculations 133 fields 133 schema, using Database Design Report 134 variables 133 True result 44, 51 Type-ahead feature 74 U Unit of measure, changing 69 Unstored results 51, 52 Updates to runtime solutions 128 Updating existing data d
