Contents

Welcome to FileMaker 9

Best of Three Worlds

How This Book Is Organized

Part I: Getting Started with FileMaker 9

Part II: Developing Solutions with FileMaker

Part III: Developer Techniques

Part IV: Data Integration and Publishing

Part V: Deploying a FileMaker Solution

Special Features

Downloadable Files

Who Should Use This Book

I Getting Started with FileMaker 9

1 FileMaker Overview

FileMaker and Its Marketplace

Rapid Application Development

Low Total Cost of Ownership

FileMaker Is a Seasoned Platform

You’re Not Alone

Introduction to Database Software

Database Software

What Database Software Does

Overview of the FileMaker 9 Product Line

FileMaker Deployment Options

Single User

Peer-to-Peer Hosting

FileMaker Server Hosting

FileMaker Server Advanced Hosting

Kiosk Mode

FileMaker Single-User Runtime

Extending the Functionality of FileMaker Pro

What’s New in FileMaker Pro 9

New Features

FileMaker 7 Architecture

Technical Specifications

2 Using FileMaker Pro

Getting Started

Registration

Activation

Software Updates

Using the Quick Start Screen

Getting Help

Understanding FileMaker Databases

Understanding Tables

Understanding Records and Fields

FileMaker Pro Navigation

Working in FileMaker Pro

Opening a Database

Working with Records

Working with Fields

Field Types

Data Validation

Working with Related Data

Finding Data with FileMaker

Using Find Mode to Perform a Find Request

Omitting and Showing All Records

Sorting

Printing

Presenting Data with Summary and Subsummary Reports

Importing and Exporting Data

Saving PDF and Excel Documents

Using the Web Viewer

Troubleshooting

FileMaker Extra: Becoming a FileMaker Pro Power User

Technique 1: Using Your Keyboard for More Speed

Technique 2: Working with Table View

Technique 3: Replacing Data

Technique 4: Inserting Specific Information

Technique 5: Getting to Know Your Entire Database

Technique 6: Using Multitiered Sorts

Technique 7: Using Multiple Windows

Technique 8: Applying Text Styling and Tabs

3 Defining and Working with Fields and Tables

Working Under the Hood

New Databases Begin with Field Definitions

Using the Manage Database Dialog

Working with Tables

Table Naming Conventions

Creating New Tables

Working with Fields

Field Naming Conventions

Adding Field Comments

Creating New Fields

Working with Field Types

Text

Number

Date

Time

Timestamp

Container

Calculation

Summary

Working with Field Options

Auto-Entry Field Options

Field Validation

Storage and Indexing

Furigana

Troubleshooting

FileMaker Extra: Indexing in FileMaker

4 Working with Layouts

What’s a Layout?

Creating and Managing Layouts

Creating a New Layout

Layout Context

Layout Setup

Hiding and Reordering Layouts

Restricting Access to Layouts

Layout Naming Conventions

Working with Parts

Adding and Ordering Parts

Formatting a Part

Part Definition

Working with Objects on a Layout

Adding Objects to a Layout

Positioning Objects on a Layout

Naming Objects on a Layout

Automatically Resizing Objects on a Layout

Arranging Objects

Working with the Tab Control Object

Adding a Tab Control Object to a Layout

Working with Fields

Adding Fields to Layouts

Field Control Style

Field Behavior

Setting the Tab Order

Merge Fields

Tooltips

Troubleshooting

FileMaker Extra: Designing Cross-Platform–Friendly Layouts

II Developing Solutions with FileMaker

5 Relational Database Design

Understanding Database Design

Database Analysis

Working with Entities and Attributes

Entities Versus Attributes: A Case Study

Design as an Iterative Process

Understanding Relationships

Representing Relationships in a Diagram

Relationship Types

Relationship Cardinality

Relationship Optionality

Optionality in Many-to-Many Relationships

Optionality in One-to-Many Relationships

Optionality in One-to-One Relationships

Understanding the Role of Keys in Database Design

Keys That Determine Uniqueness

Keys That Refer to Other Tables

Many-to-Many Relationships: Solving the Puzzle

Attributes in a Join Entity

Additional Many-to-Many Examples

Normalizing Data: What Goes Where

First Normal Form: Eliminate Repeating Groups

Second Normal Form: Eliminate Redundant Data

Third Normal Form: Eliminate Fields Not Dependent on the Key

The Basics of Process Analysis

Process Analysis: Legal Documents

FileMaker Extra: Complex Many-to-Many Relationships

6 Working with Multiple Tables

Multitable Systems in FileMaker Pro

Creating a One-to-Many Relationship in FileMaker

Creating the First Table in a Multitable System

Adding a Table to a Multitable System

Adding a Relationship

Working with Keys and Match Fields

The Database So Far

Working with Related Data

Using a Portal to View Related Child Data

Using a Portal to Add Related Records

Working with Related Parent Data in a Child File

Creating a Many-to-Many Relationship

Building the Structure

Creating Value Lists

Designing the Interface

Rapid Multitable Development

Troubleshooting

7 Working with Relationships

Relationships Graphs and ERDs

Relationships as Queries

Non-Equijoins

Matching Multiple Values

Adding a Table Occurrence to the Relationships Graph

Defining a Relationship with Multiple Match Criteria

Creating Self-Relationships

Creating a Relationship with a Global Value

Creating Cross-Product Relationships

Working with Multiple Files

Creating an External Data Source

Adding an External Table to the Relationships Graph

How and When to Use Multiple Files

Working with Converted Files

Separation of a System into Modules

Troubleshooting

FileMaker Extra: Managing the Relationships Graph

8 Getting Started with Calculations

Understanding How and Where Calculations Are Used

Writing Calculation Formulas

Uses for Calculation Formulas

Exploring the Specify Calculation Dialog

Writing the Formula

Options

Specifying Context

Essential Functions

The Parts of a Function

Text Operations

Nested Functions

Number Functions

Working with Dates and Times

Using Conditional Functions

Aggregate Functions

Learning About the Environment

Get Function

Design Functions

Troubleshooting

FileMaker Extra: Tips for Becoming a Calculation Master

9 Getting Started with Scripting

Scripts in FileMaker Pro

Creating Scripts

The ScriptMaker Interface

Script Naming Practices

Script Editing

Full Access Privileges

Commenting Scripts

Exiting a Script

Using a Script Template

Using Subscripts

Importing Scripts

Managing Scripts

Managing the Scripts Menu

Common Scripting Topics

Error Management

Setting and Controlling Data

Providing User Navigation

Saved Script Options

Using Conditional Logic

Using Loops

Working with Custom Dialogs

Triggering Scripts

Working with Buttons on Layouts

Troubleshooting

FileMaker Extra: Creating a Script Library

10 Getting Started with Reporting

Deriving Meaning from Data

Begin with the End in Mind

Determine Report Requirements

Generic Versus Specific Report Structures

Working with Lists of Data

Trailing Summaries

Alternating Row Color

Horizontal and Vertical Dividers

Sorting by Columns

Go to Detail

Summarized Reports

Using a Break Field

Creating a Subsummary Report

Using Summary Fields in Subsummary Reports

Calculations Involving Summary Fields

Summarizing on Multiple Criteria

Reordering a Report Based on Summary Data

Subsummary Reports with No Body Part

Charting in FileMaker

Delivering Reports

Save/Send as PDF

Save/Send as Excel

Send Mail

Troubleshooting

FileMaker Extra: Incorporating Reports into the Workflow

III Developer Techniques

11 Developing for Multiuser Deployment

Developing for Multiple Users

Sessions in FileMaker Pro

Session-Specific Elements

Global Behavior

User Accounts and Session Data

Concurrency

The ACID Test

Script Log

Commit Versus Create and Serial IDs

Record Locking

Multicontext Locking Within Scripts

Audit Trails in FileMaker Pro

Record Create/Modify Meta Data

Script-Controlled Editing

Auto-Entry Technique for Audit Trails

Creating Rollback Functionality

Launch Files

Troubleshooting

FileMaker Extra: Development with a Team

12 Implementing Security

Approaching Security

Identifying Risks

Planning Security

Maintaining Security

User-Level Internal Security

User Accounts

Privilege Sets

Extended Privileges

File-Level Access Security

Server Administration Security

Security over the Network

User Authentication

External Authentication

File List Filtering

Troubleshooting

FileMaker Extra: Working with Multiple Files

13 Using the Web Viewer

Introducing the Web Viewer

Renovating Issue Tracking

Adding a Web Viewer to Issue Tracking

Setting Web Viewer Options

Setting Up a Web Viewer with the Templates

Controlling the Web Viewer with the Set Web Viewer Script Step

GetLayoutObjectAttribute with Web Viewer

FileMaker Extra: Using the Web Viewer for Files

14 Advanced Interface Techniques

User Interfaces in FileMaker Pro

FileMaker’s Native User Interface

Building Your Own Interface

A Happy Medium

Interface Look and Feel

Single File Interface Versus Distributed Interface

Working with Custom Menus

Menu Sets Interface

Customizing a Menu

Loading and Activating Menu Sets

Creating a New Menu from Scratch

Conditional Formatting

Working with Table View

FileMaker Extra: User Interface Heuristics

15 Advanced Calculation Techniques

What’s an Advanced Calculation Technique?

Logical Functions

The Self Function

The Let Function

The Choose Function

The GetField Function

The Evaluate Function

The Lookup Functions

Text Formatting Functions

Text Color, Font, and Size

Text Style

Removing Text Formatting

Array Functions

Working with Return-Delimited Data Arrays

Stepping Through an Array

The "Filter"-ing Functions

The Filter Function

The FilterValues Function

Custom Functions

Uses of Custom Functions

Creating Custom Functions

Examples of Custom Functions

GetNthRecord

Troubleshooting

FileMaker Extra: Creating a Custom Function Library

16 Advanced Scripting Techniques

What Is Advanced Scripting?

Script Parameters and Script Results

Script Parameters

Specifying Script Parameters

Retrieving a Script Parameter

Passing Multivalued Parameters

Strategies for Using Script Parameters

Script Results

Final Thoughts on Script Input/Output

Script Variables

About Local Variables

About Global Variables

Other Ways to Work with Variables

About Dynamic File Paths

Viewing Your Variables

FileMaker Extra: Recursive Scripts

17 Advanced Portal Techniques

Portals in FileMaker Pro

Portal Basics

Portals Versus List View/Table View

New Portal Setup

Relationship Properties

Sticky Portals

Scripted New Portal Records

Horizontal Portals

Using Portals to Create Calendars

Filtered Portals

Multivalue And Filtered Portals

Multivalue Or Filtered Portals

Ranged, Multivalue Or Filtered Portals

Dynamic Portal Sorting

Multiple Field Type Portal Sorting

Descending Dynamic Portal Sorting

Troubleshooting

FileMaker Extra: Portals and Record Locking

18 Advanced FileMaker Solution Architecture

Window Management Techniques

The Anatomy of a Window

Positioning a Window Relative to Another Window

Multiwindow Interfaces

Tool and Function Palettes

Rich Dialog Windows

Special Effects

Selection Portals

Basic Selection Portals

Portal Row Highlights

Multikey and Multirow Selections

Go to Related Record

GTRR Basics

Predicting the Found Set

Jumping to Disconnected Table Occurrences

Showing/Hiding Layout Elements

Dedicated Find Layouts

Dedicated Find Mode Layouts

Script-Driven Finds

Troubleshooting

FileMaker Extra: Recursive Scripts

19 Debugging and Troubleshooting

What Is Troubleshooting?

Staying Out of Trouble

Understand Software Requirements

Avoid Unclear Code

Planning for Trouble

Troubleshooting Scripts and Calculations

Handling Errors in Scripts

Tracking Down Errors

Troubleshooting in Specific Areas: Performance, Context, Connectivity, and Globals

Performance

Connectivity and Related Issues

Context Dependencies

Globals

File Maintenance and Recovery

File Recovery

File Maintenance

Using the Database Design Report

Creating a DDR

Using the Script Debugger

About the Script Debugger

Placing Breakpoints

Using the Data Viewer

20 Converting Systems from Previous Versions of FileMaker Pro

Migration Choices

Converting Files

Converting Single-File Solutions

Converting Relational Solutions

Preconversion Tasks

Document Your Solution

Fix File References and External Data Sources

Do Some Housekeeping

Post-Conversion Tasks

Security

Relationships

Scripts

Fields and Formulas

Layouts

Troubleshooting

FileMaker Extra: Converting Web-Enabled Databases

Instant Web Publishing

Custom Web Publishing with CDML

IV Data Integration and Publishing

21 Connecting to External SQL Data Sources

ODBC Basics

SQL

FileMaker Architecture

ODBC Architecture

Setting Up FileMaker Databases for ODBC

Setting Up and Administering ODBC

Installing Drivers

Administering ODBC

Example: Setting Up a DSN on Mac OS X to Connect to MySQL

Example: Setting Up a DSN on Windows to Connect to FileMaker

Importing ODBC Data into FileMaker

Using External ODBC Data Sources with the Relationships Graph

Specifying the Data Source

Adding the External Data Source to the Relationships Graph

Using Supplemental Fields

Troubleshooting

22 Importing Data into FileMaker Pro

Working with External Data

Flat-File Data Sources

Choosing the Target Table

Initiating the Import

The Import Field Mapping Dialog

Updating Records with Imported Data

Importing from Another FileMaker Pro File

Importing from a Microsoft Excel File

Using an Import to Create a New Table

Importing Multiple Files from a Folder

Importing Text Files

Importing Image Files

Importing Photos from a Digital Camera

Using a Script to Import Data

Troubleshooting

FileMaker Extra: Exploiting the FileMaker-to-FileMaker Import

Duplicating a Found Set

Duplicating Between Tables

Moving, Consolidating, and Re-creating Tables

23 Exporting Data from FileMaker

Getting Out What You Put In

The Basic Mechanics of Exporting

Choosing a Source Table

Choosing an Output File Format

Selecting Fields to Export

Exporting Issues to Consider

Export File Formats

Character Transformations

Formatting Exported Data

Exporting Related Fields

Exporting Grouped Data

Exporting to Fixed-Width Formats

Working with Large Fields and Container Fields

Scripted Exports

24 Sharing Data with XML

About XML and Web Services

FileMaker and XML

The Basics of XML

FileMaker’s XML Grammars

Transforming XML

Introducing XSL Stylesheets

Analyzing a Stylesheet

Applying an Export Transformation to FileMaker XML

XML Import: Understanding Web Services

FileMaker’s XML Import Capability

Web Services Reviewed

A Stylesheet for XML Import

Working with Web Services

Accessing the Amazon Web Services

Writing a Stylesheet to Import Amazon Data

Building a More Flexible Interface to a Web Service

Troubleshooting

FileMaker Extra: Write Your Own Web Services

25 Instant Web Publishing

An Overview of Instant Web Publishing

What Is IWP?

Getting Started with IWP

Enabling and Configuring IWP

Configuring FileMaker Pro for IWP

Configuring FileMaker Server Advanced for IWP

Sharing and Securing Files via IWP

Designing for IWP Deployment

Constraints of IWP

Scripting for IWP

Layout Design

Container Fields

Application Flow

Using an IWP Solution

Browse Mode

Edit Mode

Find Mode

Sorting Records

Troubleshooting

26 Custom Web Publishing with XML/XSLT

About Custom Web Publishing

Custom Web Publishing Versus Instant Web Publishing

Custom Web Publishing Versus XML Export

Preparing for Custom Web Publishing

Getting Your Databases Ready for CWP

Getting FileMaker Server Ready for Custom Web Publishing

Publishing FileMaker Data as XML

Preparing for XML Publishing

Introduction to XML Publishing

Understanding Query Strings

Performing Specific Searches with CWP URLs

Applications of Custom Web Publishing with XML

About Server-Side XSLT

Preparing for XSLT Publishing

Using the XSLT Site Assistant

Build the Site with the Site Assistant

Explore the Generated Site

Basic Cleanups

Writing Your Own XSLT Code

Format of the XSLT URL

Embedding Query Parameters in a Stylesheet

Using Tokens to Share Data Between Stylesheets

Other Custom Web Publishing Commands and Parameters

Other Query Commands

Other Query Parameters

About the FileMaker XSLT Extensions

About Sessions

Troubleshooting

27 Custom Web Publishing with PHP

Choosing a Custom Web Publishing Technology

Preparing for Custom Web Publishing with PHP

Getting Your Databases Ready for Custom Web Publishing with PHP

Getting FileMaker Server Ready for Custom Web Publishing with PHP

Placing Files on the Web Server

Using the PHP Site Assistant

Building the Site with the Site Assistant

Exploring the Site

Troubleshooting

V Deploying a FileMaker Solution

28 Deploying and Extending FileMaker

FileMaker Deployment Options

Runtime Solutions

Renaming Files

Solution Options

Creating a Runtime Application

Removing Admin Access

Developing Kiosk Solutions

Polishing Your Custom Solution

Error Log

Plug-ins

Understanding Plug-ins

Using FileMaker’s Sample Plug-in

Installing Plug-ins

Deploying Plug-ins via FileMaker Server

Configuring and Enabling Plug-ins

Troubleshooting

29 FileMaker Server and Server Advanced

About FileMaker Server

The FileMaker Server Product Line

FileMaker Server Versus Peer-to-Peer Database Hosting

FileMaker Server Capabilities

FileMaker Server Requirements

Installing and Deploying FileMaker Server

The Installation Process

The Deployment Process

Running FileMaker Server

Starting and Stopping FileMaker Server

Hosting Databases

Using Admin Console

FileMaker Server Overview

Administration

Configuration

Working with External Services

Registering with an LDAP Server

Using External Authentication Services

Automatically Updating Plug-ins

Preparing FileMaker Server

Preparing FileMaker Pro

Performing the Auto Update

Putting It All Together

Troubleshooting

FileMaker Extra: Best Practices Checklist

Determine Network Infrastructure

Purchase Hardware

Install Software

Configure FileMaker Server

Deploy Databases and Schedule Backups

Monitor Usage Statistics

Monitor Event Logs

Perform Regular File Maintenance

Keep Current with Software Updates

30 FileMaker Mobile

FileMaker Mobile 8 Overview

FileMaker Mobile Components

System Requirements

Using FileMaker Mobile on Your Handheld Device

Installing on Your Handheld Device

Using Mobile Database Files

Synchronizing with a FileMaker Database

Configuring a File to Be Published

Using iPhone with FileMaker

Troubleshooting

FileMaker Extra: Publishing Related Data

Index