Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
FileMaker Pro 13: The Missing Manual
The Missing Credits
About the Authors
About the Creative Team
Acknowledgments
The Missing Manual Series
Introduction
Why FileMaker Pro?
About This Book
Macintosh and Windows
About the Outline
What’s New in FileMaker Pro 13
The Very Basics
About→These→Arrows
About the Online Resources
The Missing CD
Feedback
Errata
Registration
Safari® Books Online
1. Getting Started with FileMaker
1. Working with Your Database
A Very Quick Database Tour
Content Area
Scroll Bars
Zoom Controls
Understanding Modes
Status Toolbar
Opening and Closing Database Files
Opening a Database
Closing a Database
Adding Data to Your Database
Creating a Record
Entering Information
Moving Between Fields
Editing a Record
Reverting a Record
Duplicating a Record
Deleting Records
Fields for Lots of Text
Adding a New Button to the Toolbar
Understanding Browse Mode Error Messages
Typing in Vain
Navigating Your Database
Navigating Record by Record
Navigating with Keyboard Shortcuts
Finding Records
Performing a Find
Fast Match
Quick Find
Viewing All Records
Understanding Find Mode Error Messages
No Records Match
No Valid Criteria
Accidental Data Entry in Find Mode
Sorting Records
Understanding Sorting
Sort Order
Multiple Sort Fields
Same Database, Different Views
Viewing a List
Viewing a Table
Creating a New Window
Hiding Windows
Closing Hidden Windows
Automatically Arranging Windows
Saving Your Database
Saving a Copy of Your Database
Saving a Clone of Your Database
Using FileMaker’s Help Menu
2. Organizing and Editing Records
Views
Form View
List View
Table View
Rearranging Columns in Table View
Modifying Table View
Modifying Table View in Layout Mode
Advanced Find Techniques
Multiple Requests
Searching with “AND” Conditions
Searching with “OR” Conditions
Finding by Omitting
Refining a Found Set with Omit Commands
Constraining and Extending the Found Set
Constraining the Found Set
Extending the Found Set
Refining Searches with Find Operators
Match Whole Word (=), Match Phrase (“”), and Match Entire Field (= =)
Find Duplicate Values (!)
Less than (<), Less than or Equal (≤), Greater than (>), and Greater than or Equal (≥)
Range (…)
Today’s Date (//) and Invalid Date or Time (?)
Any One Character (@) and Any One Digit (#)
Zero or More Characters, A.K.A. “Wildcard” (*)
Relaxed Search (~)
Modify Last Find
Using the Find Pop-Up Menu
Saving Finds
Editing Saved Finds
Editing What’s in Your Fields
Drag-and-Drop Editing
Using the Replace Command
Find and Replace
Changing Text Appearance
Text Formatting
Paragraph Formatting
The Text Ruler
Formatting Tabs
Setting Tabs in the Text Ruler
Setting Tabs in the Tabs Dialog Box
Checking Spelling
Spell Checking with Menu Commands
Spell Checking as You Type
Managing Spelling Dictionaries
User Dictionaries
Preview Mode and Printing
2. Building Your First Database
3. Creating a Custom Database
Creating a New Database
Creating and Managing Fields in Form View
Resizing the Layout
Resizing the Layout
Creating Fields
Managing Field Types
Creating Records in Table View
Embedding a File in a Container Field
Inserting a File with a Reference
Exporting Field Contents
Understanding Layouts
The Layout Bar
The Layout Status Toolbar and Layout Mode
Layout Objects
Customizing a Layout
Editing Text Objects and Fields
Moving and Resizing Fields
Using Object Grids
Viewing Sample Data
Editing Text Objects
Deleting Objects
Adding Text Objects
Creating a New Style
Using the Inspector
Inserting a Picture on a Layout
Adding a Gradient
Formatting Fields
Formatting a Number Field
Adding a Field Control Style and a Value List
Editing a Value List
Adding a Drop-Down Calendar to a Field
Creating a New Layout
Making Two Layouts Match
Applying Styles
Copying Object and Part Styles
Arrange and Align Tools
Aligning and Distributing Objects
Resizing Objects
Grouping and Locking Objects
Arranging Objects
Rotating Objects
4. Adding Power to Your Database
Creating a Simple Calculation
Creating Fields with Manage→Database
Adding New Fields to a Layout
Deleting a Field
Discard Changes
Creating a Related Table
Understanding the Elements of a Relationship
Creating a Key Field with an Auto-Enter Serial Number
Using Replace Field Contents to Add Serial Numbers
Creating a New Table
Viewing the New Table’s Layout
Creating a Relationship Between Two Table Occurrences
Creating and Using Portals
Adding a Portal to a Layout
Resizing and Moving a Portal
Adding Rows to a Portal
Context
Creating Records Through a Portal
Editing Records Through a Portal
Deleting Records Through a Portal
Performing Finds with Related Data
Using Tab Controls
Creating a Tab Control
Editing Tab Controls
Adding, Removing, and Reordering Tabs
Default Front Tab
Tab Justification
Tab Width
Formatting a Tab Control
Deleting a Tab Control
Adding a Popover Button
Adding Merge Fields
Using Symbols to Show Important Info
Writing a Basic Script
Creating a Sort Script
Creating a Button
Applying a Script Trigger
Creating a Dynamic Report with the Assistant
Creating a Trailing Group Report
Creating Charts in Table View
Changing the Default Account
Summing Up
3. Thinking Like a Developer
5. Creating and Managing a Relational Database
Understanding Relational Databases
Modeling Your Database
Choosing Entities
Finding Relationships
One-to-Many Relationships
One-to-One Relationships
Many-to-Many Relationships
The Entity-Relationship Diagram
Creating an ER Diagram
Keys
Primary and Foreign Keys
Choosing a Good Primary Key Field
Join Tables
Creating a Relational Database
Creating Relationships
Sorting a Relationship
Managing the Relationships Graph
Managing Table Occurrences Using the Tables/Relationships Tools
Arrange the Graph
Selection Tools
Color Your Table Occurrences
Adding Notes
Printing the Graph
Using Relational Database Power
One Table Occurrence, One Found Set
Viewing Related Fields on a Layout
Creating a Value List Based on a Related Field
Lookups
Creating Lookups
Using a Relookup
Navigating Between Related Records
Go to Related Record
Using GTRR to Create a Found Set
Reviewing Relationship Concepts
Bidirectionality
Implicit Relationships in Action
6. Field Options
Understanding Field Types
Text
Number
Date
Time
Timestamp
Container
Calculation
Summary
Auto-Enter Field Options
Creation Values
Using Creation Name and Account Name
Modification Values
Serial Number
Value from Last Visited Record
Data
Calculated Value
Looked-Up Value
Validation Options
Making Validation Stricter
Validation Requirements
Strict Data Type
Not Empty
Unique
Existing Value
Member of Value List
In Range
Maximum Number of Characters
Storage Options
Global Storage
Repeating Fields
Indexing
Automatic Indexing
Manual Indexing
Indexing Language
Seeing the Index
Container Field Options
Printing Field Definitions
Beyond Text: Container Fields
Pictures
Copy and Paste
Insert→Picture
QuickTime
Sound
File
Exporting Data from Container Fields
7. Layout Mechanics
Layout Types
Selecting a Device
Computer and Touch Device Layout Types
Form
List
Table
Report
Printer Layout Types
Labels or Vertical Labels
Envelope
Report
Layout Parts
Parts in Form View
Parts in List View
Parts in Preview Mode
Part Setup Dialog Box
When to Use Each Type
Arranging Parts
Renaming a Layout
Setting Layout View
Found Sets and Layouts
Layout Setup
Manage Layouts
Formatting Fields
Field Controls
Advanced Field Controls
Edit Box
Drop-Down List
Pop-Up Menu
Checkbox Set
Radio Button Set
Drop-Down Calendar
Auto-Complete
Auto-Complete in Edit Boxes
Auto-Complete in Drop-Down Lists
Repetitions
Display Data From
Field Behavior
Tab Order
Customizing the Tab Order
Fixing the Tab Order in the People Database
Preserving the Automatic Order
Creating Layouts for Printing
Visualize the Result in Preview Mode
Print Margins
Columns
8. Layout Design
Layout Themes
Touch Themes
Styles
Customizing Styles
Saving Style Changes to the Theme
Reusing and Sharing Themes
Grids, Guides, and Dynamic Guides
Grids
Guides
Dynamic Guides
Screen Stencils
Data Formatting
Number Formatting
General
As Entered
Boolean
Decimal
Currency
Percent
Date Formatting
As Entered
Preset Styles
Custom
Short System Date and Long System Date
Time Formatting
As Entered
Other Formats
Timestamp Formatting
Graphic Formatting
Format
Alignment
Optimize For
Merge Fields
Merge Variables
Tooltips
Autosizing
Autosizing in List Layouts
Sliding Layout Objects
When to Use Sliding
Choosing the Objects to Slide
Setting Sliding Options
More Sliding & Visibility Options
Buttons
Creating Buttons with the Button Tool
Turning an Existing Layout Object into a Button
Making a Button Not a Button
Button Actions
Popover Buttons
Slide Controls
9. Understanding Calculations
Understanding Calculations
Creating a Calculation
The Structure of a Calculation
Using Fields in Calculations
Using Constants in Calculations
Number Constants
Text Constants
Using Operators in Calculations
Mathematical Operators
The Concatenation Operator
Comparison Operators
Logical Operators
Parentheses
Functions
Text Functions
Text Formatting Functions
Number Functions
Date Functions
Time Functions
Timestamp Functions
Container Functions
Aggregate Functions
Summary Functions
Repeating Functions
Financial Functions
Trigonometric Functions
Logical Functions
Get Functions
Design Functions
Mobile Functions
Custom Functions
External Functions
Expressions
Using a Related Field in a Calculation
Understanding the Specify Calculation Dialog Box
Table Occurrence Context
Field List
Operators
Function List
Result Type
Calculation Box
Changing the Standard Evaluation Behavior
Auto-Enter Calculations
Validate Data Entry with a Calculation
Commenting in Calculations
10. More Calculations and Data Types
Number Crunching Calculations
Number Function Types
Using Number Functions
Abs()
Average()
Count()
Floor() and Ceiling()
List()
Round()
Setprecision()
Going Beyond Basic Calculations
Reviewing the Data and New Business Rules
Planning the Calculations
Constructing the Calculation
Text Calculations
Text Functions
Text Function Types
Character Functions
Word Functions
Text Editing Functions
Text Value Functions
Text Counting Functions
Text Formatting Functions
TextColor() and RGB()
TextFont()
TextSize()
Textstyleadd() and Textstyleremove()
Date and Time Calculations
Math with Dates and Times
Dates
Times
Parsing Dates and Times
Calculations that Create Dates and Times
The Secret Powers of Date()
Containers in Calculations
Calculations with Pictures, Sounds, Movies, and Files
Calculations with References
11. Understanding Scripts
Understanding Scripts
Your First Script
Preparing the Database
Planning Your Script
Creating Your Script
Improving Your Script
Shortcuts to the Edit Script Window
The Importance of the Layout
When to Switch
Running Scripts
The Scripts Menu
Buttons
Script Triggers
Timer Scripts
Running Scripts on the Server
Waiting for Script Results
Organizing Your Scripts
Creating Script Folders
Creating Menu Separators
Branching and Looping in Scripts
Branching with If, Else If, Else, and End If
Testing Multiple Conditions
Looping
Using Go to Record/Request/Page to Exit a Loop
Using Exit Loop If to Exit a Loop
Understanding Types of Loops
Script Triggers
Creating a Simple Script Trigger
File Option Triggers
OnFirstWindowOpen
OnLastWindowClose
OnWindowOpen
OnWindowClose
Layout Triggers
OnRecordLoad
OnRecordCommit
OnRecordRevert
OnLayoutKeystroke
OnLayoutEnter
OnLayoutExit
OnLayoutSizeChange
OnModeEnter
OnModeExit
OnViewChange
OnGestureTap
Object Triggers
OnObjectEnter
OnObjectKeystroke
OnObjectModify
OnObjectValidate
OnObjectSave
OnObjectExit
OnPanelSwitch
12. Exploring Script Steps
Go to Layout
Go to Object
Naming Objects
Using Go to Object in a Script
Scripting for Fields
Navigating Fields
Go to Field
Go to Next Field and go to Previous Field
Editing Commands
Selecting Text
Editing Field Data
Avoiding Cut, Copy, and Paste
Set Field
Set Field by Name
Insert Calculated Result
Inserting Other Values
Putting Data in Container Fields
Using the Insert File Script Step
Creating a Field Script
Building the Script
Other Steps that Work with Fields
Replace Field Contents and Relookup Field Contents
Set Next Serial Value
Perform Find/Replace
Working with Records
Creating, Duplicating, and Deleting Records
Navigating Through Records
Go to Record/Request/Page
Go to Related Record
Opening, Reverting, and Committing Records
Open Record/Request
Commit Records/Requests
Revert Record/Request
Copying Records
Copy Record/Request
Copy All Records/Requests
Working with Portals
Go to Portal Row
Finding Records
Pausing for Users’ Find Requests
Triggering A Find Script
Static Find Requests
Using a Variable to Create Dynamic Find Requests
Constraining and Extending the Found Set
Omitting Records
Modify Last Find
Sorting Records
Sorting Records with an OnRecordLoad Trigger
Working with Windows
Creating Windows
Specifying Advanced Style Options
Bringing a Window to the Front
Closing a Window
Moving and Resizing Existing Windows
Adjust Window
Arrange All Windows
Move/Resize Window
Other Window-Related Script Steps
Working with Files
Opening and Closing Files
Save a Copy As
Other File-Related Script Steps
Printing
Other Script Steps
Upload to FileMaker Server
Set Script Animation
Refresh Object
Open URL
Dial Phone
Set Web Viewer
Execute SQL
Flush Cache to Disk
Exit Application
4. Becoming a Power Developer
13. Applying Developer Utilities
Copying and Pasting Database Structure
Importing Tables and Fields
Copying and Pasting Tables
Copying and Pasting Fields
Copying Scripts and Script Steps
Script Debugger
Controlling Script Execution
Breakpoints
Examining Errors
Pause on Error
The Call Stack
Working with the Debugger Window
The Data Viewer
The Current Tab
The Watch Tab
Disable Script Steps
The Database Design Report
Generating the DDR
Using the DDR
Finding Broken Elements with the DDR
Custom Functions
Defining a Custom Function
Editing Custom Functions
Sharing Custom Functions
Custom Menus
Editing a Menu
Creating a New Menu
Using Existing Commands
Submenus
Removing a Menu
Hiding or Showing a Menu by Calculation
Installing Custom Menu Sets
Developer Utilities
Using the Developer Utilities
Renaming Files
Create Runtime Solution Application(s)
Remove Admin Access from Files Permanently
Turn on Kiosk Mode for Non-Admin Accounts
Databases Must have a Filemaker File Extension
Create Error Log for any Processing Errors
Loading and Saving Settings
File Maintenance
14. Advanced Relationship Techniques
Advanced Relationships
Self-Join Relationships
Avoiding Ambiguity
Multiple Criteria Relationships
Editing a Relationship
Relationship Operators
Portal Filtering
Understanding Table Occurrences
Deciding Which Table Needs a New Occurrence
Adding a Table Occurrence
Adding Fields for New Table Occurrences
Table Occurrence Groups
Planning the New Elements You’ll Need
Understanding Table Occurrence Groups
Creating a New Table Occurrence Group
Building the Assign Expenses Layout
Duplicating and Editing an Existing Layout
Copying a Portal from Another Layout
Using GTRR to Switch TOGS
Understanding Graph Arrangements
Connecting Database Files
Adding a Table Occurrence from Another File
Defining Data Sources Using a Path
Using Multiple Relationship Techniques
Creating the Invoice Finder Layout
15. Reporting and Analysis
Summary Fields and Subsummary Parts
Summary Fields
Other Summary Field Types
Total of
Average of
Count of
Minimum and Maximum
Standard Deviation of
Fraction of Total of
List of
Summarizing Repetitions
Advanced Subsummary Parts
Multiple Subsummary Parts on One Layout
Leading and Trailing Grand Summaries
Subsummary Parts and Printing
Web Viewer Objects
Putting a Web Viewer on a Layout
Web Viewer Options
Allow Interaction with Web Viewer Content
Display Content in Find Mode
Display Progress Bar
Display Status Messages
Automatically Encode URL
Conditional Formatting
Conditional Formatting of Fields
Conditional Formatting of Text Objects
Advanced Conditional Formatting
Removing Conditional Formatting
Basic Charting (Found Set Charts)
Column
Stacked Column
Positive/Negative Column
Bar
Stacked Bar
Pie
Line
Area
Scatter
Bubble
Chart Formatting
Charting and Reports
Picking the Right Fields
Choosing an Appropriate Layout Part
Using the Correct Sort Order
Advanced Charting
Delimited Data
Related Records
16. Advanced Calculations
Understanding Boolean Functions
Using the Self() Function
Using Storage Options
Understanding Stored and Unstored Calculation Fields
Field Dependencies
Deciding when to Store
Indexing Calculations
Global Calculation Fields
Logical Functions
Conditional Functions
The If() Function
The Case() Function
The Choose() Function
Constructing a Conditional Calculation
Total Due Calculation #1: Using the If() Function
Total Due Calculation #2: Using the Case() Function
The Let() Function and Variables
Defining Calculation Variables
Total Due Calculation #3: Using the Let() Function
The Life of a Variable
Local Variables
Global Variables
Nesting Let() Functions
Recursion
Step 1: Solve the First Case
Step 2: Assume Your Function Already Works, and Use It
Step 3: Find a Stopping Point
Plug-Ins
Installing Plug-ins
Finding Plug-ins
Creating Your Own Plug-ins
17. Advanced Scripting
Commenting Scripts
Importing Scripts
Communicating with Database Users
Showing Custom Dialog Box
Showing a Simple Message
Asking a Simple Question
Providing Input Fields
Understanding the Commit Data Option
Building a Custom Layout
Showing Feedback with a Merge Variable
Creating Modular Scripts
The Perform Script Script Step
Halt Script and Exit Script
Exit Script (Result)
Organizing Scripts that Use Halt Script or Exit Script
Script Parameters
Script Variables
Handling Errors
The Set Error Capture Script Step
The Get(LastError) Function
The Allow User Abort Script Step
Advanced Script Triggers
Install OnTimer Script
Keystroke Triggers
Detecting Keystrokes
Creating a Simple Keystroke Trigger
Creating a Keystroke Filter
Putting a Complex Script Together
Planning the Script
Considering Exceptions
Creating Subscripts
Creating Subscript Placeholders
Creating Skeleton Subscripts
Finishing the Subscripts
The Find Unbilled Activity Subscript
Copying and Editing Existing Script Steps
Adding a Script Parameter to a Perform Script Step
Checking a Subscript’s Result
Finishing the Create Invoice Record Subscript
Finishing the Process Timeslips Subscript
Creating a Looping Script
The Process Expenses Subscript
Testing Scripts
5. Security and Integration
18. Adding Security
How Security Works
Who Gets Access
What They Can Do
Privilege Sets
Understanding Privilege Sets
The Built-In Privilege Sets
Custom Privilege Sets
Understanding Individual Privileges
Other Privileges
Creating a Privilege Set
Data Access and Design Privileges
Creating Record-Level Access
Field-Level Access
Editing a Privilege Set
Managing Accounts
Adding a New Account
Testing Your New Account
External Authentication
Extended Privileges
Creating an Extended Privilege
Checking for an Extended Privilege
Assigning Extended Privileges
Managing External File Access
Scripts and Security
Detecting Privileges in a Script
Handling Security Errors
Running Scripts with Full Access Privileges
Managing Security with Scripts
Add Account
Delete Account
Reset Account Password
Change Password
Enable Account
Re-Login
19. Sharing Data Using FileMaker Server
Understanding Database Sharing
Reviewing Record Locking
FileMaker Network Sharing
Setting Up a Host Computer
Working with a Shared File
Opening a Shared File
Understanding the Open Remote File Dialog Box
Choosing a Host Computer
Choosing a File
FileMaker Server
Preparing to Install FileMaker Server
Understanding Installation Types
Installing FileMaker Server
Configuring Your Server
Using the Technology Tests Page
Administering FileMaker Server
Uploading a Database from FileMaker Pro
Uploading a Database Manually
Understanding Backups
Scheduling a Backup
Enabling Progressive Backups
Server Hardware
20. Sharing Data with WebDirect and FileMaker Go
Understanding WebDirect
Understanding WebDirect Compatibility
Enabling WebDirect in a Database
Setting Up WebDirect on FileMaker Server
Opening a Database in FileMaker Go
Logging In After Hibernation
Connecting with a Web Browser
Disconnecting from WebDirect
Designing for WebDirect and FileMaker Go
Reducing Overhead
Creating a Good User Experience
Choosing Layouts for WebDirect and FileMaker Go
Designing for Go
Designing for WebDirect
Understanding WebDirect Menus
Customizing the WebDirect Home Page
Sharing Databases on the Internet Without WebDirect
Custom Web Publishing
Static Web Publishing
21. Sharing Data with Other Systems
Sharing Your Data with Others
Save/Send Records As
Saving as Microsoft Excel
Saving as Portable Document Format (PDF)
Snapshot Link
External SQL Sources
Setting Up ODBC
The SQL Database Server
Installing the ODBC Driver
Configuring the Data Source in Windows
Configuring Data Sources on Mac OS X
Finishing ODBC Data Source Configuration
Connecting FileMaker to a SQL Data Source
Adding SQL Tables to a FileMaker Database
Creating Relationships
Shadow Tables
Using SQL Tables
Exporting Data
Grouped Exports
Export Formats
Importing Data
Recurring Import
Importing over Existing Data
Update Existing Records in Found Set
Update Matching Records in Found Set
Creating Tables on Import
Creating a New Database from an Import File
Import Data Sources
File
Folder
Importing a Folder of Pictures or Movies
Importing a Folder of Text Files
XML Data Source
XML Export
Bento Data Source
ODBC Data Sources
Importing and Exporting in a Script
Save Records Script Steps
The Import Records Script Step
The Export Records Script Step
6. Appendixes
A. Getting Help
Getting Help from FileMaker Pro
FileMaker’s Installed Extras
Starter Solutions
The Quick Start Screen
Getting Help from FileMaker, Inc.
The Resource Center
Updates and Downloads
FileMaker Training
Filemaker Training Series
Webinars
Video and Tutorials
Third Party Training Materials
Technical Support
Free Support
Paid Support
Developer Programs
The FileMaker Technical Network
FileMaker Business Alliance (FBA)
DevCon
Getting Help from the Community
Local User Groups
Mail Lists and Newsgroups
Training/Consultants
B. File Conversion
Compatibility
Understanding Conversion
Sharing Converted Files With WebDirect
Start with a Test Run
Converting a Single File
Troubleshooting Conversion Problems
Converting Multiple Files
C. Using the Insert Commands with Container Fields
Notes
Notes
Notes
D. FileMaker Error Codes
Index
About the Authors
Copyright
← Prev
Back
Next →
← Prev
Back
Next →