FileMaker 12 In Depth

Jesse Feiler has worked with FileMaker since its beginnings. He has written a number of books about FileMaker as well as OS X, iOS, iWork, Core Data, Objective-C, and other new technologies. His books have been translated into Japanese, Chinese, Polish, German, Spanish, French, Arabic, Hungarian, and other languages. As software director of North Country Consulting, he has designed and implemented a variety of FileMaker solutions for small businesses and nonprofits in fields such as production, marketing, the arts, printing and publishing, food service, and construction. His meeting management software for iOS devices, MinutesMachine, is published by Champlain Arts Corp (champlainarts.com).   His website is www.northcountryconsulting.com . You can find updates and file downloads there.

Introduction

Part I Getting Started with FileMaker 12

Chapter 1 FileMaker Overview

FileMaker and Its Marketplace

    Mobility

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

Technical Specifications

Chapter 2 Using FileMaker Pro

Getting Started

    Registration

    Software Updates

    Using the Quick Start Screen

    Getting Help

Understanding FileMaker Pro Features

    Understanding FileMaker Databases

    Understanding Tables

    Understanding Records and Fields

    The FileMaker Pro User Interface

Using the Status Toolbar

    Customizing the Status Toolbar (OS X)

    Customizing the Status Toolbar (Windows)

Working in FileMaker Pro

    Opening a Database

Working with Records

Working with Fields

    Data in Formatted Fields

    Modifying Value Lists

    Field Types

    Data Validation

Working with Related Data

    Understanding the Mechanics of a Portal

Finding Data with FileMaker

    Using Quick Find

    Using Find Mode to Perform a Find Request

    Omitting and Showing All Records

    Saving Find Requests

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

Chapter 3 Defining and Working with Fields and Tables

Working Under the Hood

    Creating New Databases

    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 Fields

Working with Field Options

    Auto-Entry Field Options

    Field Validation

    Storage and Indexing

    Furigana

Troubleshooting

FileMaker Extra: Indexing in FileMaker

Chapter 4 Working with Layouts

What’s a Layout?

Using Multiple Layouts Automatically

Creating and Managing Layouts

    Creating a New Layout

    Layout Context

    Layout Setup

    Hiding and Reordering Layouts

    Layout Naming Conventions

Working with Parts

    Adding and Ordering Parts

    Formatting a Part

    Part Definition

Working with the Layout Status Toolbar

    Using the Layout Bar

    Using the Customizable Status Toolbar Tool Groups

    Using the Status Toolbar Items

Using the Inspector

    Inspecting Data Settings

    Inspecting Appearance Settings

    Inspecting Position Settings

Working with Objects on a Layout

    Adding Objects to a Layout

    Positioning Objects on a Layout

Working with the Tab Control Object

    Adding a Tab Control Object to a Layout

Working with Fields

    Adding Fields to Layouts

    Setting the Tab Order

    Merge Fields

Troubleshooting

Part II Developing Solutions with FileMaker

Chapter 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

Understanding the Role of Keys in Database Design

    Keys That Determine Uniqueness

    Keys That Refer to Other Tables

Many-to-Many Relationships

    Using Join Tables

    Using Checkboxes and Multiple Values

    Attributes in a Join Entity

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

FileMaker Extra: Complex Many-to-Many Relationships

Chapter 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

Chapter 7 Working with Relationships

Relationships Graphs and ERDs

Relationships as Queries

    Nonequijoins

    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

Troubleshooting

FileMaker Extra: Managing the Relationships Graph

    Using Formatting Tools to Manage the Relationships Graph

    Using Table Occurrences to Manage the Relationships Graph

Chapter 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

    Parts of a Function

    Text Operations

    Nested Functions

    Number Functions

    Character Functions

    Working with Dates and Times

Using Conditional Functions

Aggregate Functions

Learning About the Environment: Introspective Functions

    Get Function

    Design Functions

Device Identification Functions

Mobile Functions

Troubleshooting

FileMaker Extra: Tips for Becoming a Calculation Master

Chapter 9 Getting Started with Scripting

Scripts in FileMaker Pro

Creating Scripts

    The Scripting Interface

    Script Editing

    Full Access Privileges

    Commenting Scripts

    Exiting a Script

    Using a Script Template

    Using Subscripts

    Importing 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

Starting and Triggering Scripts

    Starting Scripts

    Triggering Scripts

Working with Buttons on Layouts

Naming Scripts

Troubleshooting

FileMaker Extra: Creating a Script Library

Chapter 10 Getting Started with Reporting and Charting

Reporting in FileMaker Pro

Deriving Meaning from Data

    Begin with the End in Mind

    Determine Report Requirements

    Generic Versus Specific Report Structures

Working with Reports, Layouts, View As Options, and Modes

Working with Lists of Data

Using the New Layout/Report Assistant

Using Summarized Reports

    Creating a Summary Field

    Working with Subsummary Parts

    Calculations Involving Summary Fields

Modifying Table Views

Customizing Layouts and Reports

    Alternating Row Color

    Sorting Data in a Table

    Sliding Objects

Delivering Reports

    Save/Send as PDF

    Save/Send as Excel

    Send Mail

    Scripting Send Mail

Introducing Charting

Troubleshooting

FileMaker Extra: Incorporating Reports into the Workflow

Part III Developer Techniques

Chapter 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

Launch Files

Troubleshooting

FileMaker Extra: Development with a Team

Chapter 12 Implementing Security

Approaching Security

    Identifying Risks

    Planning Security

    Maintaining Security

User-Level Internal Security

    User Accounts

    Privilege Sets

    Extended Privileges

    File Access

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

Chapter 13 Using the Web Viewer

Introducing the Web Viewer

    Exploring the Web Viewer in Contacts

Creating and Editing a Web Viewer

    Creating a Web Viewer

    Setting a Web Viewer to a Constant URL

    Constructing a URL Dynamically Based on a Search

    Setting Up a Web Viewer with the Templates

Setting Web Viewer Options

Controlling the Web Viewer with the Set Web Viewer Script Step

FileMaker Extra: Using the Web Viewer for Files

Chapter 14 Advanced Interface Techniques

What’s New in the Interface World

Working with Themes

    Changing a Theme

    Exploring Themes

Using Styles and States

    Using Styles

    Using States

    Copying Styles

Using FileMaker Formatting Tools

    Conditional Formatting

    Setting the Layout Width

    Using Grids

    Using Guides

    Using Dynamic Guides

    Using Screen Stencils

Using GetLayoutObjectAttribute

Working with Custom Menus

    Specifying Custom Menu Elements

    Using the Menu Sets Interface

Providing Accessibility

    Set Up Accessibility Attributes in Layout Mode

    Turn On Accessibility Features

    Use Accessibility Features

FileMaker Extra: User Interface Heuristics

Chapter 15 Advanced Calculation Techniques

Logical Functions

    The Let Function

    The Choose Function

    The GetField Function

    The Evaluate Function

    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

Matching Multiple Values

Chapter 16 Advanced Scripting Techniques

What Is Advanced Scripting?

Script Parameters

    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

Chapter 17 Working with FileMaker Triggers

Introducing FileMaker Triggers

    FileMaker Triggers Before FileMaker Pro 10

    Triggers in FileMaker Pro Today

    Trigger Targets

    Trigger Events

    Triggers and Underlying Data

    Triggers and Web Publishing

Attaching Triggers

    Layout Triggers

    Object Triggers

    Window Triggers

Using a Timer

Trigger Functions

    The Self Function

    Char and Code Functions

    The GetFieldName Function

    The Get (TriggerKeystroke) and Get (TriggerModifierKeys) Functions

FileMaker Extra: Using Triggers for an Interactive Interface

Chapter 18 Advanced FileMaker Solution Architecture

Window Management Techniques

Multiwindow Interfaces

    Using Window Styles

    Working with Document Windows

    Creating a Modal Dialog with a Window Style

Creating a Modal Dialog Using a Script Pause State

    Adding a Pause State

Go to Related Record

    GTRR Basics

    Predicting the Found Set

    Jumping to Disconnected Table Occurrences

Dedicated Find Layouts

    Dedicated Find Mode Layouts

    Script-Driven Finds

Troubleshooting

Chapter 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

Using the Database Design Report

    Creating a DDR

Using the Script Debugger

    About the Script Debugger

    Placing Breakpoints

Using the Data Viewer

Chapter 20 Converting Systems from Previous Versions of FileMaker Pro

Updating and Upgrading FileMaker Software

Migrating to New FileMaker File Formats

Planning the Conversion

Preconversion Tasks

    Document Your Solution

    Do Some Housekeeping

Converting Files

    Post-Conversion Tasks

Part IV Data Integration and Publishing

Chapter 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 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 theRelationships Graph

    Using Supplemental Fields

Troubleshooting

Chapter 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

    Updating Records Without Using Match Fields

    Importing from Another FileMaker Pro File

    Using an Import to Create a New Table

Importing from a Microsoft Excel File

Setting Import Options and Reviewing Status

Importing Multiple Files from a Folder

    Importing Text Files

    Importing Image Files

Scripting Imports with FileMaker

    Creating Automatic Recurring Imports

    Using a Script to Import Data

Using Bento Data Sources

Troubleshooting

FileMaker Extra: Exploiting the FileMaker-to-FileMaker Import

    Duplicating a Found Set

    Duplicating Between Tables

Chapter 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

Chapter 24 Instant Web Publishing

Overview of Instant Web Publishing

    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

Troubleshooting

Chapter 25 Custom Web Publishing with PHP and XML

About Custom Web Publishing

    Understanding the Three Parts of FileMaker Web Publishing

Custom Web Publishing Versus Instant Web Publishing

Preparing for Custom Web Publishing

    Getting Your Databases Ready for CWP

    Getting FileMaker Server Ready for Custom Web Publishing

Choosing a Custom Web Publishing Technology

Using 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

     Writing the PHP code for the FileMaker PHP API

Using Custom Web Publishing with 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

    Other Query Parameters

About Sessions

    Managing Sessions

Troubleshooting

Part V Deploying a FileMaker Solution

Chapter 26 Deploying and Extending FileMaker

FileMaker Deployment Options

Renaming Files

Runtime Solutions

    Solution Options

    Removing Admin Access

    Polishing Your Custom Solution

    Error Log

Developing Kiosk Solutions

    Preparing a Kiosk Interface

    Maintaining a Kiosk Solution

Plug-Ins

    Understanding Plug-ins

    Installing Plug-Ins

    Configuring and Enabling Plug-Ins

Troubleshooting

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

FileMaker Extra: Best Practices Checklist

    Determine Network Infrastructure

    Purchase Hardware

    Install Software

    Configure FileMaker Server

    Deploy Databases and Schedule Backups

    Monitor Usage Statistics

    Recheck Performance

    Stay on Top of Java

    Monitor Log Viewer

    Keep Current with Software Updates

 

 

9780789748461   TOC   5/8/2012