You are previewing FileMaker® Pro 9 Bible.
O'Reilly logo
FileMaker® Pro 9 Bible

Book Description

Refocused and completely rewritten with the needs of business users and FileMaker Pro developers in mind, FileMaker Pro 9 Bible shows you how to design an industrial-strength database, make it intuitive, and automate it with scripts and programming. From the fundamentals to data modeling to writing no-fail code, you'll find the clear, step-by-step procedures, solid techniques, and detailed explanations you need to master this powerful software and build the right solutions for your needs.

Table of Contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Preface
    1. About This Book
    2. About This Book's Target Audience
    3. How This Book Is Organized
      1. Part I: The Fundamentals
      2. Part II: Introduction to Database Design
      3. Part III: Beyond the Basics
      4. Part IV: Integrity and Security
      5. Part V: Raising the Bar
      6. Part VI: Appendixes
    4. Icons Used in This Book
    5. Where to Go from Here
  6. I. The Fundamentals
    1. 1. Databases: The What, Why, and How
      1. 1.1. The Many Faces of Databases: Lists, Tables, and Forms
        1. 1.1.1. Understanding the limitations of paper-based databases
        2. 1.1.2. Entering the digital age
        3. 1.1.3. Preparing to get organized
      2. 1.2. The Concept of a Relational Database
        1. 1.2.1. Flat-file databases and data redundancy
        2. 1.2.2. Opportunities for making connections
      3. 1.3. The Anatomy of a Database Solution
        1. 1.3.1. The data: Foundation and substance
        2. 1.3.2. The interface: Screens, letters, forms, and reports
        3. 1.3.3. The hidden helper: Process management
      4. 1.4. How FileMaker Fits In
        1. 1.4.1. Knowing what FileMaker Pro calls things
        2. 1.4.2. Using familiar ideas from the real world
        3. 1.4.3. Integrating processes and information
        4. 1.4.4. Recognizing that knowledge is power — personal and professional
    2. 2. Putting FileMaker Pro in Perspective
      1. 2.1. What Makes FileMaker Pro Different from Other Database Development Tools?
        1. 2.1.1. Some common misperceptions
        2. 2.1.2. A unique approach to design
      2. 2.2. The FileMaker Product Family
        1. 2.2.1. Desktop and server
        2. 2.2.2. Scalability and flexibility
      3. 2.3. FileMaker's Hidden Talents
        1. 2.3.1. The cross-platform chameleon
        2. 2.3.2. Multiple technologies and formats
        3. 2.3.3. Plug-ins and extensibility
        4. 2.3.4. The FileMaker calculation engine: Simplicity and power
      4. 2.4. Resources and Exemplars
        1. 2.4.1. Examples and brainteasers
        2. 2.4.2. Other resources and opportunities
    3. 3. Getting Acquainted with FileMaker
      1. 3.1. Getting FileMaker Working for You
        1. 3.1.1. Starting and exiting from FileMaker
        2. 3.1.2. Creating, saving, and closing files
        3. 3.1.3. Handling files and data safely
        4. 3.1.4. Earlier file formats and conversion issues
      2. 3.2. Finding Your Way Around
        1. 3.2.1. The modes and their uses
        2. 3.2.2. Navigating and viewing data
        3. 3.2.3. Searching and the FileMaker Find/Omit puzzle
        4. 3.2.4. Screen elements and what they're for
      3. 3.3. Entering and Editing Data
        1. 3.3.1. Creating and duplicating records
        2. 3.3.2. Field definitions: Validation and dependencies
        3. 3.3.3. The significance of commitment
      4. 3.4. The Ins and Outs
        1. 3.4.1. Importing and exporting data
        2. 3.4.2. Previewing and printing options
        3. 3.4.3. Send/Save as PDF and Excel
      5. 3.5. Getting to Know the Relatives
        1. 3.5.1. Ways to view and edit related data
        2. 3.5.2. The importance of context
        3. 3.5.3. Making complexity simple in practice
      6. 3.6. Optimizing the Application
        1. 3.6.1. Preference settings for your workstation
        2. 3.6.2. File options for the current database
    4. 4. What's New in FileMaker 9
      1. 4.1. The FileMaker Quick Start Screen
      2. 4.2. The Help Menu
      3. 4.3. Learn More Links on Critical Dialogs
      4. 4.4. Scheduled Software Update Notification
      5. 4.5. New Calculation Functions
        1. 4.5.1. Self
        2. 4.5.2. Get (TemporaryPath)
        3. 4.5.3. Get (HostApplicationVersion)
        4. 4.5.4. Asin (number)
        5. 4.5.5. Acos (number)
      6. 4.6. Conditional Formatting
      7. 4.7. Append to PDF
      8. 4.8. The Send E-Mail Link for Database Sharing
      9. 4.9. Script Grouping and Editing Tools
      10. 4.10. Automatic Resizing of Layout Objects
      11. 4.11. Enhanced Tab Control
        1. 4.11.1. Default tab selection
        2. 4.11.2. Tab width settings
      12. 4.12. Web Viewer Feature Enhancements
        1. 4.12.1. Progress indicator, location, lock icon
        2. 4.12.2. Support for data URLs
      13. 4.13. Field-Specific Enabling of Visual Spell-Checking
      14. 4.14. Multiple Levels of Text Undo and Redo in Fields and ScriptMaker
      15. 4.15. Additional Toolbar Controls
        1. 4.15.1. Save/Send as PDF or Excel, Undo and Redo
        2. 4.15.2. Layout alignment toolbar
      16. 4.16. Additional Avery Label Formats
      17. 4.17. Enhanced External SQL Data Source Support
      18. 4.18. Supplemental Fields for ESS Tables
        1. 4.18.1. Creating supplemental fields
        2. 4.18.2. Using supplemental fields
        3. 4.18.3. Considering design
      19. 4.19. FileMaker Pro 9 Advanced
        1. 4.19.1. Script Debugger enhancements
          1. 4.19.1.1. Launching scripts from within the Script Debugger
          2. 4.19.1.2. Working with restricted-access scripts
          3. 4.19.1.3. Making multiple selections in the Script Debugger window
          4. 4.19.1.4. Understanding the Pause on Error and Last Error code
          5. 4.19.1.5. Using step buttons when a script is paused
        2. 4.19.2. Data Viewer enhancements
          1. 4.19.2.1. The Current and Watch tabs
          2. 4.19.2.2. Authenticating to view-restricted data
          3. 4.19.2.3. Identifying other usability improvements
        3. 4.19.3. The PHP Site Assistant
        4. 4.19.4. Database Design Report enhancements
  7. II. Introduction to Database Design
    1. 5. Creating a Database
      1. 5.1. Creating a New Database File
        1. 5.1.1. Adding tables and fields
        2. 5.1.2. Working with the Field Options dialog: Validation and auto-entry
        3. 5.1.3. Setting up simple calculations
        4. 5.1.4. Capturing simple metadata
        5. 5.1.5. Creating relationships between tables
        6. 5.1.6. Adding aggregating calcs
      2. 5.2. Viewing and Interacting with Data
        1. 5.2.1. Looking at the multiple uses of layouts
        2. 5.2.2. Creating records and entering data
        3. 5.2.3. Editing or deleting data
        4. 5.2.4. Finding and sorting data you've already entered
        5. 5.2.5. Using special find symbols
        6. 5.2.6. Searching with the range and wildcard operators
      3. 5.3. Avoiding the Need for Data Duplication
        1. 5.3.1. Recognizing the visual cues to data relationships
        2. 5.3.2. Information has a logical flow
        3. 5.3.3. Anticipating the user
        4. 5.3.4. Making complex things simple
      4. 5.4. Getting Started with File Security
        1. 5.4.1. Working with accounts and privilege sets
        2. 5.4.2. Setting a default account and password
      5. 5.5. Thinking About Usability
        1. 5.5.1. Moving between records
        2. 5.5.2. Managing context
        3. 5.5.3. Moving between tables
        4. 5.5.4. Using and changing views
        5. 5.5.5. Using buttons for static and dynamic actions
    2. 6. The Interface: Layout Mode
      1. 6.1. Initial Layouts
        1. 6.1.1. A map of Layout mode
          1. 6.1.1.1. Selection then Action tools
          2. 6.1.1.2. Drag-to-Layout tools
          3. 6.1.1.3. Palette and Menu controls
        2. 6.1.2. Organizing the presentation of information
        3. 6.1.3. Applying formats to field and text objects
        4. 6.1.4. Setting up layouts for printing
        5. 6.1.5. Understanding lists and forms
        6. 6.1.6. Layout parts and their purposes
      2. 6.2. The Importance of Visual Structure
        1. 6.2.1. Adding visual pointers and aids
        2. 6.2.2. Using white space
        3. 6.2.3. Ergonomics and avoiding visual fatigue
        4. 6.2.4. Giving information meaning
      3. 6.3. Different Kinds of Layout Objects
        1. 6.3.1. Static and dynamic objects
        2. 6.3.2. Inherent object properties
        3. 6.3.3. Conditional format attributes
      4. 6.4. FileMaker as a Graphical Environment
        1. 6.4.1. Building graphic objects in FileMaker
        2. 6.4.2. Default object formats and attributes
        3. 6.4.3. Controlling stacking and alignment
        4. 6.4.4. Bringing in graphics from other applications
      5. 6.5. Interacting with Layout Objects
        1. 6.5.1. Keyboard control of a layout
        2. 6.5.2. Setting the tab order
        3. 6.5.3. Assigning names to layout objects
        4. 6.5.4. Controlling visual spell-checking
      6. 6.6. The Tab Control and Its Uses
        1. 6.6.1. Defining and creating a tab panel
        2. 6.6.2. Navigating between tab panels
        3. 6.6.3. Tab panel limitations
      7. 6.7. Displaying Related Data
        1. 6.7.1. Working within layout context
        2. 6.7.2. Setting up a portal
      8. 6.8. The Magic of Buttons
        1. 6.8.1. Defining buttons
        2. 6.8.2. Button scope and button commands
        3. 6.8.3. The button as an object
      9. 6.9. The Web Viewer: Inviting in the World
        1. 6.9.1. Setting up a Web viewer
        2. 6.9.2. Controlling a Web viewer
        3. 6.9.3. Complementary data concepts
      10. 6.10. Reports and Data Output
        1. 6.10.1. Considerations for printed output
        2. 6.10.2. Using fonts
        3. 6.10.3. Page sizes and page setup
        4. 6.10.4. Paper output versus PDF or Excel output
        5. 6.10.5. Composite PDFs from multiple layouts
    3. 7. The Structure: The Manage Database Dialog
      1. 7.1. Working with Tables
        1. 7.1.1. Table concepts: A room with a view
        2. 7.1.2. Adding, deleting, and renaming tables
        3. 7.1.3. Moving tables between files
        4. 7.1.4. Importing tables
      2. 7.2. Specifying Fields
        1. 7.2.1. Adding, deleting, and renaming fields
        2. 7.2.2. Understanding field/data types and their significance
        3. 7.2.3. Auto-entry options
        4. 7.2.4. Field validation options
        5. 7.2.5. Storage and indexing options
        6. 7.2.6. Summary and Calculation fields
        7. 7.2.7. Working with global fields
      3. 7.3. Basic Calculations
        1. 7.3.1. Creating a Calculation field
        2. 7.3.2. Defining a calculation formula
        3. 7.3.3. Entering literal text
        4. 7.3.4. Referencing fields
        5. 7.3.5. Understanding calculation functions and their syntax
          1. 7.3.5.1. The List( ) function
        6. 7.3.6. The Count( ) function
          1. 7.3.6.1. The Date( ) function
          2. 7.3.6.2. The Round( ) function
          3. 7.3.6.3. The Length( ) function
        7. 7.3.7. Doing some simple calculations
          1. 7.3.7.1. Number of days an item is overdue
          2. 7.3.7.2. Calculating initials from a person's name
          3. 7.3.7.3. Compound interest at a known rate over a given period
          4. 7.3.7.4. Current quarter of the calendar year
          5. 7.3.7.5. Changing ampersands to "and" in a block of text
          6. 7.3.7.6. Record navigation text (record n of nn)
      4. 7.4. The Relationships Graph
        1. 7.4.1. Common misconceptions about the Relationships Graph
        2. 7.4.2. Tables versus table occurrences
        3. 7.4.3. Avoiding circular references
        4. 7.4.4. Named and unnamed data sources
        5. 7.4.5. Creating references to other FileMaker files
      5. 7.5. Working with External SQL Data Sources
        1. 7.5.1. Configuring ODBC drivers: Setting up a DSN
        2. 7.5.2. Integrating SQL tables with FileMaker data
        3. 7.5.3. Adding supplemental fields
      6. 7.6. The Concept of Data Relationships
        1. 7.6.1. Why bother with relationships anyway?
        2. 7.6.2. How relationships work
        3. 7.6.3. Solving problems using relationships
        4. 7.6.4. Deciding what goes where
        5. 7.6.5. The FileMaker relational model
    4. 8. The Processes: ScriptMaker
      1. 8.1. ScriptMaker: What It Is and What It Offers You
        1. 8.1.1. Building blocks of automation
        2. 8.1.2. Context is everything
        3. 8.1.3. Doing things in sequence
        4. 8.1.4. Addressing objects by name
      2. 8.2. Defining and Editing Scripts
        1. 8.2.1. Script Editor windows
        2. 8.2.2. Setting up a basic script
        3. 8.2.3. How script commands function
        4. 8.2.4. Changing the order of commands
        5. 8.2.5. Assigning attributes to a command
      3. 8.3. Using the Script Menu
        1. 8.3.1. Managing the Scripts menu
        2. 8.3.2. Other ways to trigger a script
        3. 8.3.3. Using the single-threaded script engine
        4. 8.3.4. Working with the script stack and paused scripts
      4. 8.4. Controlling Script Execution
        1. 8.4.1. Using conditional statements
        2. 8.4.2. Using repetition
        3. 8.4.3. Pausing for user input
      5. 8.5. Some Notable Script Uses
        1. 8.5.1. Navigation and view controls
        2. 8.5.2. Editing information via scripts
        3. 8.5.3. Printing and managing files
      6. 8.6. Ease of Editing in ScriptMaker
        1. 8.6.1. Selecting and duplicating multiple commands
        2. 8.6.2. Copying and pasting scripts
        3. 8.6.3. Copying and pasting script steps
      7. 8.7. Organizing Scripts
        1. 8.7.1. Creating list separators
        2. 8.7.2. Script commenting
        3. 8.7.3. Creating script groups
        4. 8.7.4. Reordering and grouping scripts
        5. 8.7.5. Filtering scripts by group
        6. 8.7.6. Searching for scripts by name
      8. 8.8. Some Examples to Start With
        1. 8.8.1. Performing a Find
        2. 8.8.2. Printing a report
        3. 8.8.3. Acting on user input
  8. III. Beyond the Basics
    1. 9. The FileMaker Power User
      1. 9.1. Making Browse Mode Work for You
        1. 9.1.1. Using multiple windows and views
        2. 9.1.2. Filtering portals and creating pick lists
        3. 9.1.3. Jump buttons: Shortcut navigation
        4. 9.1.4. Controlling one window from another
      2. 9.2. Performing Complex Search Operations
        1. 9.2.1. Compound Find criteria: The AND Find
        2. 9.2.2. Stacking Find requests: The OR Find
        3. 9.2.3. Constraining and extending the found set
        4. 9.2.4. Saving Finds and found sets
      3. 9.3. Sorting Records
        1. 9.3.1. Multiple sort keys
        2. 9.3.2. Dynamic sort techniques
        3. 9.3.3. Creating click-sort columns
        4. 9.3.4. Sorting related data
      4. 9.4. Understanding Formatting
        1. 9.4.1. The management of formatting: A three-tiered approach
        2. 9.4.2. Character-level formatting
        3. 9.4.3. Paragraph-level formatting
        4. 9.4.4. Layout format filters
        5. 9.4.5. Precedence of number, date, and time formats
        6. 9.4.6. Controlling formatting programmatically
        7. 9.4.7. Creating style buttons
      5. 9.5. Some Notes on Variables
        1. 9.5.1. The three kinds of variables
        2. 9.5.2. Variables and memory usage
        3. 9.5.3. Instantiating and destroying variables
        4. 9.5.4. Keeping track of variables
      6. 9.6. Understanding Indexing
        1. 9.6.1. Text index types
          1. 9.6.1.1. The word index
          2. 9.6.1.2. The value index
        2. 9.6.2. Indexing myths exploded
        3. 9.6.3. Differences between numeric and text indexing
        4. 9.6.4. Unicode and alternate language indexes
        5. 9.6.5. Optimizing field index configurations
      7. 9.7. The Table of Dependencies
        1. 9.7.1. Cascading calculation operations
        2. 9.7.2. The limits of dependency
        3. 9.7.3. Tiers of dependency
      8. 9.8. Caching Join Results
        1. 9.8.1. What caching does for you
        2. 9.8.2. Solving caching problems
        3. 9.8.3. Gaining control of the cache
      9. 9.9. Understanding Global Fields
        1. 9.9.1. The behavior of global fields
        2. 9.9.2. Uses for global fields
        3. 9.9.3. When to avoid global fields
        4. 9.9.4. Using global calculation fields
    2. 10. Building Advanced Interfaces
      1. 10.1. Developing for Mac and Windows Users
        1. 10.1.1. Selecting fonts
        2. 10.1.2. Paying attention to differences in screen rendering
        3. 10.1.3. Considering platform-specific window behavior
      2. 10.2. Using Dynamic Screen Elements
        1. 10.2.1. Disappearing/reappearing objects
          1. 10.2.1.1. The portal invisibility trick
          2. 10.2.1.2. Concealed and remotely operated tab control
          3. 10.2.1.3. Using conditional formatting as a visibility control
        2. 10.2.2. The hidden power of conditional formatting
        3. 10.2.3. Multi-state buttons and objects
      3. 10.3. Working with Sub-Summary Parts and Part Controls
        1. 10.3.1. Building adaptable screens
        2. 10.3.2. Stacking up multiple Sub-summary parts
        3. 10.3.3. Using multiple break fields
        4. 10.3.4. Controlling pagination and page breaks
      4. 10.4. Designing for Print
        1. 10.4.1. Non-printing objects
        2. 10.4.2. Sliding objects and reducing parts
        3. 10.4.3. Using Merge fields
        4. 10.4.4. Creating a letter generator
      5. 10.5. Using Multiple Windows and Views
        1. 10.5.1. Managing window placement and size
        2. 10.5.2. Windows as pop-ups and drill-downs
        3. 10.5.3. Simulating modal window behavior
      6. 10.6. Employing Custom Dialogs as an Interface Tool
        1. 10.6.1. Dialogs as a data-entry device
        2. 10.6.2. Dynamic dialog attributes
      7. 10.7. Looking at Anchors and Resizable Layout Objects
        1. 10.7.1. Objects that move according to window size
        2. 10.7.2. Objects that grow and shrink
        3. 10.7.3. Managing complex layout resizing
        4. 10.7.4. Resizing behavior of enclosing objects
        5. 10.7.5. Centering objects within the viewable area
      8. 10.8. Implementing Shortcut Navigation
        1. 10.8.1. The power of the Go to Related Record command
        2. 10.8.2. One interface, many paths
        3. 10.8.3. Building Back button functionality
      9. 10.9. Building Depth and Dimensionality
        1. 10.9.1. Using embossing and engraving effects
        2. 10.9.2. Spatial cues for added meaning
        3. 10.9.3. Delineation of element groups
        4. 10.9.4. Color
        5. 10.9.5. Transparency and translucency
      10. 10.10. Working with Tab Controls
        1. 10.10.1. Organizers and space savers
        2. 10.10.2. Tab navigation via keyboard
        3. 10.10.3. Scripting tab operations
      11. 10.11. Recognizing the Flexibility of Portals
        1. 10.11.1. Lists in many guises
        2. 10.11.2. Portals as a navigation device
        3. 10.11.3. Dynamically sorted portals
        4. 10.11.4. Innovative portal implementations
      12. 10.12. Using Advanced Web Viewer Techniques
        1. 10.12.1. Access to advanced functionality
        2. 10.12.2. Rendering internally calculated content
        3. 10.12.3. Scraping data from Web pages
      13. 10.13. Progress Bars and Native Charting Techniques
        1. 10.13.1. Creating script progress monitors
        2. 10.13.2. Native indicators and graphical displays
      14. 10.14. Using Interface Elements
        1. 10.14.1. Splash screens
        2. 10.14.2. Main menus
        3. 10.14.3. About and version info
        4. 10.14.4. Online Help for your users
      15. 10.15. Handling User Preferences
        1. 10.15.1. A user-centric development philosophy
        2. 10.15.2. Capturing state by user
        3. 10.15.3. Example—a multi-lingual solution interface
    3. 11. Data Modeling in FileMaker
      1. 11.1. Background in Relational Theory
        1. 11.1.1. Set Theory in the management of data
        2. 11.1.2. Modeling the real world
          1. 11.1.2.1. Think about clarity of organization
          2. 11.1.2.2. Keep the big picture in view
        3. 11.1.3. Remembering some guiding principles
          1. 11.1.3.1. Separate entities by type
          2. 11.1.3.2. Delineate fields clearly
          3. 11.1.3.3. Place multiples in a separate table
          4. 11.1.3.4. Store everything once only
          5. 11.1.3.5. Identify the major players
        4. 11.1.4. Put it into practice
      2. 11.2. FileMaker Relationships Graph Symbols
        1. 11.2.1. Visual cues and clues
        2. 11.2.2. The TO as a pointer
        3. 11.2.3. Understanding the graph metaphor
      3. 11.3. Relationship Operators
        1. 11.3.1. Equi-joins and non-equal joins
        2. 11.3.2. Comparative operators (theta joins)
      4. 11.4. Note regarding theta joins in relational algebra
        1. 11.4.1. Cartesian joins
        2. 11.4.2. Multi-predicate relationships
      5. 11.5. Alternative Relationship Techniques
        1. 11.5.1. Multi-key fields
        2. 11.5.2. Compound keys
        3. 11.5.3. One-way relationships
        4. 11.5.4. Join tables
        5. 11.5.5. Naturally occurring joins
      6. 11.6. Working with Data Arrays
        1. 11.6.1. Repeating fields as an array handler
        2. 11.6.2. Collapsing and expanding arrays
        3. 11.6.3. Relationship-based techniques for managing data
          1. 11.6.3.1. Allowing creation via relationship
          2. 11.6.3.2. Using self joins
          3. 11.6.3.3. The isolating relationship
      7. 11.7. Graph Techniques — Spiders, Squids, and Anchor-Buoy
        1. 11.7.1. Constellations and modular centers
        2. 11.7.2. A satellite-based graph solution
        3. 11.7.3. Segmentation on functional lines
      8. 11.8. Documenting the Database Structure
        1. 11.8.1. Graph annotations
        2. 11.8.2. Naming conventions
        3. 11.8.3. Field commenting
        4. 11.8.4. Ancillary notes and documentation
      9. 11.9. The Concept of Layers
        1. 11.9.1. "Back end" and "front end"
        2. 11.9.2. The business or procedural layer
        3. 11.9.3. FileMaker as an integrated environment
        4. 11.9.4. Separation anxiety
      10. 11.10. File Architecture versus Data Structure
        1. 11.10.1. Multi-file solutions
        2. 11.10.2. The modular approach
        3. 11.10.3. Interface files
        4. 11.10.4. Approaches to separation of data
        5. 11.10.5. Costs and benefits of separation
      11. 11.11. Separation and External SQL Sources
        1. 11.11.1. Understanding the rules
        2. 11.11.2. Working within constraints
        3. 11.11.3. Supporting the user
      12. 11.12. Implementing Separation in an Existing Solution
        1. 11.12.1. Establishing data source(s)
        2. 11.12.2. Re-pointing table occurrences
        3. 11.12.3. Creating separate graphs
      13. 11.13. Deployment Considerations
        1. 11.13.1. Your remotest dreams
        2. 11.13.2. The model of adaptability
    4. 12. Calculation Wizardry
      1. 12.1. Compound Calculation Expressions
        1. 12.1.1. The language of logic
        2. 12.1.2. Functions and schema references
        3. 12.1.3. Structured syntax and nesting
        4. 12.1.4. Putting it all together
      2. 12.2. Order of Operations
      3. 12.3. Boolean Operations
        1. 12.3.1. Zero, empty, and everything else
        2. 12.3.2. Implicit Boolean coding
        3. 12.3.3. Explicit Boolean coding
      4. 12.4. Variables—Calculation, Script, and Global
        1. 12.4.1. Declaring calculation variables—the Let( ) function
        2. 12.4.2. Understanding variables' scope
        3. 12.4.3. Benefiting from variables in a calculation
      5. 12.5. Text Processing and Parsing Functions
        1. 12.5.1. Substitute, Replace, and Trim
        2. 12.5.2. Left, Right, and Middle
        3. 12.5.3. Position and PatternCount
        4. 12.5.4. The xWords suite
        5. 12.5.5. Parsing in practice
      6. 12.6. Text Formatting Operations
        1. 12.6.1. Applying text formatting
        2. 12.6.2. Removing text formatting
        3. 12.6.3. Applying selective formatting
        4. 12.6.4. Creating a Format button
      7. 12.7. Dates, Times, and Timestamps
        1. 12.7.1. How FileMaker manages dates
        2. 12.7.2. Plotting time
        3. 12.7.3. The number of seconds in 2007 years
        4. 12.7.4. Juggling days, months, and years
      8. 12.8. Summary Data
        1. 12.8.1. Using aggregate functions
        2. 12.8.2. The ballad of Max and Min
        3. 12.8.3. Referencing summary fields
      9. 12.9. Lists and Arrays
        1. 12.9.1. Retrieving values as a list
        2. 12.9.2. Managing lists—the xValues functions
        3. 12.9.3. Extracting one value from a list
        4. 12.9.4. Adding or inserting a list value
        5. 12.9.5. Removing a value from a list
      10. 12.10. Layers of Abstraction
        1. 12.10.1. Building blocks with GetField( )
        2. 12.10.2. The value of Evaluate( )
      11. 12.11. Unstored Calculations
        1. 12.11.1. Why and when calculations are unstored
        2. 12.11.2. Understanding the benefits and trade-offs of unstored calculations
        3. 12.11.3. Discovering the hidden secrets of unstored calcs
      12. 12.12. Calculation Fields versus Auto-Enter Calculations
      13. 12.13. The user over-ride capability
        1. 12.13.1. Auto-enter calculations and storage
        2. 12.13.2. The Do Not Replace option
      14. 12.14. Global Calculations
        1. 12.14.1. The moon follows you everywhere
        2. 12.14.2. Managing global dependencies
        3. 12.14.3. The freedom and efficiency of global calculations
      15. 12.15. Environment and Metadata
        1. 12.15.1. The Get( ) functions
        2. 12.15.2. Design functions
      16. 12.16. Calculations Using Custom Functions
      17. 12.17. Your Code's Documentation
        1. 12.17.1. Code formatting
        2. 12.17.2. Code commenting
    5. 13. Scripting in Depth
      1. 13.1. Scripting the Control of Objects and Interface
        1. 13.1.1. Addressing objects by name
        2. 13.1.2. Locking down the interface
        3. 13.1.3. Managing user interaction
      2. 13.2. Trapping for Errors
        1. 13.2.1. Retrieving error codes appropriately
        2. 13.2.2. What the error codes mean
        3. 13.2.3. Why bother with error handling?
        4. 13.2.4. Handling errors
      3. 13.3. Scripts and Access Privileges
        1. 13.3.1. Privilege-based errors
        2. 13.3.2. Run script with full access privileges
        3. 13.3.3. Determining the substantive privileges
      4. 13.4. Automating the Automation
        1. 13.4.1. Defining a script to run on file open
        2. 13.4.2. Housekeeping practices for start-up scripts
        3. 13.4.3. Scripts that run on file close
      5. 13.5. Harnessing the Power of Parameters, Results, and Variables
        1. 13.5.1. Getting data into a script
        2. 13.5.2. Branching according to state
        3. 13.5.3. Declaring variables
        4. 13.5.4. Passing and retrieving multiple parameters
        5. 13.5.5. Specifying and retrieving a script result
        6. 13.5.6. Storing and accumulating data as you go
      6. 13.6. Dynamic and Indirect Controls in ScriptMaker
        1. 13.6.1. Example - Go to Layout by name or number
        2. 13.6.2. Dynamic file paths using variables
        3. 13.6.3. Dynamically building Find criteria
        4. 13.6.4. Editing field data on the fly (indirection)
      7. 13.7. Using Nonlinear Logic
        1. 13.7.1. Nested and sequential If/Else conditions
        2. 13.7.2. Looping constructs
        3. 13.7.3. Specifying exit conditions
      8. 13.8. Modular Script Code
        1. 13.8.1. Using sub-scripts
        2. 13.8.2. Script recursion
      9. 13.9. Scripted Window Management
        1. 13.9.1. Addressing windows by name (title)
      10. 13.10. Moving and resizing windows
        1. 13.10.1. Determining window dimensions
        2. 13.10.2. Creating windows off-screen
      11. 13.11. Freezing and refreshing the screen
      12. 13.12. Scripting Data Import and Export
        1. 13.12.1. Exporting field contents
        2. 13.12.2. Exporting table data
        3. 13.12.3. Selecting fields for export
        4. 13.12.4. Import options
        5. 13.12.5. Data matching for import
          1. 13.12.5.1. Synchronizing and updating data
          2. 13.12.5.2. Other import options
        6. 13.12.6. Loading and unloading container objects
      13. 13.13. Pivoting Data between Tables
        1. 13.13.1. Using utility relationships
        2. 13.13.2. Managing related data (walking through related records)
      14. 13.14. Going over Some Practical Examples
        1. 13.14.1. Locating unique records
        2. 13.14.2. Building a multi-part PDF report
  9. IV. Integrity and Security
    1. 14. In Control with FileMaker Security
      1. 14.1. Concepts of Security
        1. 14.1.1. Balance and perspective
          1. 14.1.1.1. Identifying threats
          2. 14.1.1.2. Assessing value
        2. 14.1.2. Protecting your investment
        3. 14.1.3. Interface vulnerabilities
          1. 14.1.3.1. Taking things at interface value
          2. 14.1.3.2. More than a semblance of security
          3. 14.1.3.3. File-based security
      2. 14.2. The Privilege Set
        1. 14.2.1. Concepts of role-based security
        2. 14.2.2. Defining and constraining access
        3. 14.2.3. Schema privilege controls
      3. 14.3. Granular Security
        1. 14.3.1. Access to value lists and scripts
        2. 14.3.2. The two dimensions of layout access
        3. 14.3.3. Privileges for table, record, and field access
        4. 14.3.4. Using and managing extended privileges
      4. 14.4. User Authentication
        1. 14.4.1. Creating user accounts
        2. 14.4.2. Internal and external authentication
      5. 14.5. Scripted Account Management
        1. 14.5.1. Provision for automation of database security
        2. 14.5.2. Working with multi-file solutions
        3. 14.5.3. Safe scripting implementations
      6. 14.6. Creating a Custom Logout Option
        1. 14.6.1. The locked-down database
        2. 14.6.2. Structuring a solution for logging out
        3. 14.6.3. Security logging
      7. 14.7. How Much Security Is Enough?
        1. 14.7.1. Ways to evaluate risk
        2. 14.7.2. A balanced view of threats
        3. 14.7.3. A strategic model for response
      8. 14.8. The Importance of Physical File Security
        1. 14.8.1. Layers of protection
        2. 14.8.2. Alternative forms of protection
        3. 14.8.3. A multi-faceted approach
      9. 14.9. Security in Deployment: FileMaker Server
        1. 14.9.1. Filtered display of files
        2. 14.9.2. Secure Socket Layer encryption
        3. 14.9.3. Server checks and logs
    2. 15. Maintaining Referential Integrity
      1. 15.1. Pinpointing Common Causes of Referential Integrity Problems
        1. 15.1.1. The potential impact on your solution
        2. 15.1.2. Costs and benefits
      2. 15.2. Using Unique Keys
        1. 15.2.1. Key safeguards
        2. 15.2.2. Keys and meaning (existence, persistence, uniqueness)
      3. 15.3. Generating Keys
        1. 15.3.1. Serial numbers
        2. 15.3.2. Record IDs
        3. 15.3.3. Unique identification (UID) values
      4. 15.4. Exploring Keys and Data Type
      5. 15.5. Retrofitting Keys
      6. 15.6. Deleting Redundant Records
        1. 15.6.1. The use of cascading deletion
        2. 15.6.2. Configuring relationships for referential integrity
        3. 15.6.3. Privilege requirements for cascade delete
        4. 15.6.4. Controlled cascading deletes at runtime
      7. 15.7. Considering Other Integrity Issues
        1. 15.7.1. Lookups and when to use them
        2. 15.7.2. Auto-entry lookups and references
        3. 15.7.3. Data design issues
      8. 15.8. Managing Dependencies
        1. 15.8.1. Literal text references
        2. 15.8.2. Indirect object/element references
        3. 15.8.3. Filename references
        4. 15.8.4. Structural anomalies
    3. 16. Making FileMaker Systems Fail-Safe
      1. 16.1. Expecting the Unexpected
        1. 16.1.1. Successful backup strategies
          1. 16.1.1.1. Backup frequency
          2. 16.1.1.2. An appropriate backup cycle
          3. 16.1.1.3. The integrity of backups
          4. 16.1.1.4. The location of backups
          5. 16.1.1.5. Back up the code, not just the data
        2. 16.1.2. The hazards of copying open files
        3. 16.1.3. Backing up local files
        4. 16.1.4. Backing up hosted files
      2. 16.2. A Comprehensive Approach to Error Trapping
        1. 16.2.1. Dealing with record locking
        2. 16.2.2. Techniques to avoid in multi-user or multi-window environments
          1. 16.2.2.1. Replace Field Contents
          2. 16.2.2.2. Record marking and flagging techniques
          3. 16.2.2.3. Uses of global fields
      3. 16.3. Opening Remote Files
        1. 16.3.1. Peer-to-peer hosting
        2. 16.3.2. File sharing risks
        3. 16.3.3. Network spaghetti
        4. 16.3.4. Opener files
        5. 16.3.5. Sending an e-mail link
        6. 16.3.6. Temporary Edit Interface Techniques
        7. 16.3.7. The data viewer concept
        8. 16.3.8. The legitimate purpose of record locking
        9. 16.3.9. Creating double-blind entry systems
      4. 16.4. Field Masking, Filtering, and Error Rejection
        1. 16.4.1. Applying standard data formations
        2. 16.4.2. Dealing with trailing spaces and carriage returns
        3. 16.4.3. Rejecting out-of-scope characters
        4. 16.4.4. Handling styled source text
      5. 16.5. Built-In Logging Capabilities
        1. 16.5.1. Making use of auto-enter options
        2. 16.5.2. Capturing and extending standard metadata
      6. 16.6. Script Logging
        1. 16.6.1. Infrastructure for script logging
        2. 16.6.2. Tracking script execution
          1. 16.6.2.1. Script-specific context variables
          2. 16.6.2.2. Script diagnostics
      7. 16.7. Capturing User Edits in Detail
        1. 16.7.1. Trapping edits, field-by-field
        2. 16.7.2. Incorporating ancillary data
        3. 16.7.3. Logging record deletions
      8. 16.8. Managing the Accumulation of Log Data
        1. 16.8.1. Archiving options
        2. 16.8.2. Generating secondary output
      9. 16.9. Implementing Roll-Back Capabilities
        1. 16.9.1. Chronological roll-back
        2. 16.9.2. Alternative undo and roll-back capabilities
        3. 16.9.3. Using logs to roll forward
      10. 16.10. Alternative Logging Approaches
        1. 16.10.1. Logs as Data
        2. 16.10.2. Scripted and triggered logging
    4. 17. Maintaining and Restoring Data
      1. 17.1. Some Notes on File Recovery
        1. 17.1.1. Debunking common myths and misconceptions
        2. 17.1.2. The Recover process
        3. 17.1.3. Salvaging data
        4. 17.1.4. Understanding file corruption
      2. 17.2. Exporting and Importing Data
        1. 17.2.1. File format considerations
        2. 17.2.2. Exporting to and importing from a folder
        3. 17.2.3. Delimiters and EOL markers
      3. 17.3. Data Cleansing Operations
        1. 17.3.1. Extract, transform, and load
        2. 17.3.2. Data format considerations
          1. 17.3.2.1. Data organization
          2. 17.3.2.2. Data presentation
          3. 17.3.2.3. Data domain
        3. 17.3.3. Filtering capabilities in FileMaker
      4. 17.4. Synchronizing Data Sets
        1. 17.4.1. Import matching
        2. 17.4.2. Importing selectively
      5. 17.5. Handling Embedded Images and Stored Files
        1. 17.5.1. Assigning and retrieving paths
        2. 17.5.2. Scripted field updates
      6. 17.6. Text-Handling Considerations
        1. 17.6.1. Export field contents
        2. 17.6.2. Designing a custom export process
  10. V. Raising the Bar
    1. 18. FileMaker Pro Advanced Features
      1. 18.1. Script Debugger
        1. 18.1.1. Watching code in action
        2. 18.1.2. Debugging restricted privilege scripts
        3. 18.1.3. Getting used to the Debugger controls
      2. 18.2. Data Viewer
        1. 18.2.1. Current and Watch panels
          1. 18.2.1.1. The Current panel
          2. 18.2.1.2. The Watch panel
        2. 18.2.2. Using the Viewer with the Debugger
        3. 18.2.3. The Data Viewer sand pit
        4. 18.2.4. The Data Viewer and variables
      3. 18.3. Database Design Report
        1. 18.3.1. DDR capabilities
        2. 18.3.2. Mining the DDR for information
        3. 18.3.3. Tools and techniques for interpreting DDR data
      4. 18.4. File Maintenance
        1. 18.4.1. Compact File
        2. 18.4.2. Optimize File
        3. 18.4.3. The Save a Copy As / Compacted alternative
      5. 18.5. Defining Tooltips
        1. 18.5.1. Using conditional tooltips
        2. 18.5.2. Keeping track of tooltips
      6. 18.6. Creating Custom Menus
        1. 18.6.1. Defining menus
        2. 18.6.2. Editing individual menus
          1. 18.6.2.1. Benefits of the Script Step action
          2. 18.6.2.2. Benefits of window widgets
        3. 18.6.3. Adding menus to sets
        4. 18.6.4. Assigning menu sets throughout your file
          1. 18.6.4.1. Setting the default menu set for a file
          2. 18.6.4.2. Determining a menu set for each layout
          3. 18.6.4.3. Controlling menu sets via script
      7. 18.7. Custom Functions
        1. 18.7.1. Defining custom functions
        2. 18.7.2. Custom functions as an aid to syntax readability
        3. 18.7.3. Maximizing efficiency and ease of use
      8. 18.8. Custom Functions and Recursion
        1. 18.8.1. Things that only custom functions can do
        2. 18.8.2. The stack and the limits of recursion
        3. 18.8.3. Tail recursion in practice
        4. 18.8.4. Some useful examples
          1. 18.8.4.1. Creating an acronym from a supplied phrase
          2. 18.8.4.2. Extracting a character set from a supplied block of text
          3. 18.8.4.3. Removing an unspecified number of leading carriage returns
      9. 18.9. Creating Runtime Applications
        1. 18.9.1. Generating a stand-alone solution
        2. 18.9.2. Binding for each platform
        3. 18.9.3. Hosting runtime files
    2. 19. Efficient Code, Efficient Solutions
      1. 19.1. Designing for Scale: Size Considerations
        1. 19.1.1. The elephant in the cherry tree
        2. 19.1.2. Predicting what will scale well
      2. 19.2. Eliminating Redundancy
        1. 19.2.1. Avoiding duplication of elements
        2. 19.2.2. Using portable and reusable code
          1. 19.2.2.1. Appropriate use of sub-scripts
          2. 19.2.2.2. Appropriate use of custom functions
      3. 19.3. Designing for Flexibility and Adaptability
        1. 19.3.1. Layouts and adaptable design
        2. 19.3.2. Concepts of reusability applied to the Relationships Graph
      4. 19.4. Traveling the Shortest Distance Between Two Points
        1. 19.4.1. Optimal calculation syntax
        2. 19.4.2. Alternative syntax examples
          1. 19.4.2.1. Working with modifier keys
          2. 19.4.2.2. Working with Boolean values
        3. 19.4.3. Avoiding dependency "spaghetti"
        4. 19.4.4. Applying simplicity principles
      5. 19.5. Transaction Modeling
        1. 19.5.1. Live versus batch data
        2. 19.5.2. Posting edits and propagating edits to related records
        3. 19.5.3. Offline updates and processing
        4. 19.5.4. Robots and batch automation
        5. 19.5.5. Host/server script execution
      6. 19.6. Managing File Size
        1. 19.6.1. Dealing with data in chunks
          1. 19.6.1.1. Modularization strategies
          2. 19.6.1.2. Considering segmentation
        2. 19.6.2. Data archiving
      7. 19.7. Images and Media in Databases
    3. 20. Extending FileMaker's Capabilities
      1. 20.1. External Scripting Calls
        1. 20.1.1. Using Send Event and VBScript
          1. 20.1.1.1. Using VBScript with FileMaker Pro
        2. 20.1.2. Perform AppleScript
        3. 20.1.3. Cross-platform solutions and external script calls
        4. 20.1.4. Third-party helpers and macros
      2. 20.2. Rendering HTML and JavaScript
        1. 20.2.1. Harnessing HTTP
        2. 20.2.2. Bringing services to your solution
        3. 20.2.3. Handling hypertext
      3. 20.3. Web Viewer Widgets
        1. 20.3.1. Charting with Flash
        2. 20.3.2. Applets and servlets
      4. 20.4. FileMaker Plug-Ins
        1. 20.4.1. Installing and enabling plug-ins
        2. 20.4.2. Using external functions
        3. 20.4.3. Script triggering
          1. 20.4.3.1. Robust triggering implementations
          2. 20.4.3.2. Available script triggering plug-ins
        4. 20.4.4. Dialog capabilities
        5. 20.4.5. File and media handling
        6. 20.4.6. E-mail, HTTP, and FTP
        7. 20.4.7. Charting and other functionality
      5. 20.5. Web Deployment Options
        1. 20.5.1. Instant Web publishing
        2. 20.5.2. Custom Web publishing
          1. 20.5.2.1. Working with XML and XSLT
          2. 20.5.2.2. The FileMaker PHP API
          3. 20.5.2.3. FileMaker's PHP Site Assistant
      6. 20.6. Finding Third-Party Tools
        1. 20.6.1. Developer tools
        2. 20.6.2. Analysis and documentation
        3. 20.6.3. Shared information
  11. VI. Appendixes
    1. A. Glossary
    2. B. Expanding Your Knowledge with Additional Resources
      1. B.1. From the Horse's Mouth
      2. B.2. Professional Consulting and Development Services
      3. B.3. Online Design and Development Tips and Tricks
      4. B.4. Online Forums and Mailing Lists
      5. B.5. Books and Periodicals
    3. C. About the Web Site
      1. C.1. What's on the Web Site
      2. C.2. Troubleshooting