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

Book Description

FileMaker Pro 10 Bible provides information that grows with you no matter what your level when you start. Topics range from the general (how databases fit into real life and your business needs; comparisons with other database development tools; a broad discussion of integration with existing systems and software) through the building-blocks of FileMaker solutions, and on up to state-of-the-art advice on concepts of optimization, modularization, innovative and sophisticated user-interface design, dynamic elements, logic, cutting-edge calculations, publishing your database to the Web, and integrating tightly with SQL databases via "ESS". The book also includes the special developer tools available in FileMaker Pro Advanced, and covers topics too oft skipped over in other books on the market -- backup how-to's and strategies, must-read information about good database husbandry and file recovery, and so on. Much of the information in the book is documented nowhere else. This is the one must-have FileMaker book!

Table of Contents

  1. Copyright
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. Introduction
    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. 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. What FileMaker Pro calls things
        2. 1.4.2. Familiar ideas from the real world
        3. 1.4.3. Integrating processes and information
        4. 1.4.4. 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 10
      1. 4.1. Embracing Change
      2. 4.2. Status Area Redesign
      3. 4.3. Live Reports/Sub-summaries
      4. 4.4. Maintain Record Sort Order
      5. 4.5. Saved Find Requests
      6. 4.6. Set Field by Name
      7. 4.7. Script Events Triggers
        1. 4.7.1. Layout object triggers
        2. 4.7.2. Layout script triggers
        3. 4.7.3. Timed interval script triggers
        4. 4.7.4. File-based script triggers
        5. 4.7.5. Avoiding trigger tangles
      8. 4.8. New Calculation Functions
        1. 4.8.1. Get(TriggerKeystroke)
        2. 4.8.2. Get(TriggerModifierKeys)
        3. 4.8.3. Code(text)
        4. 4.8.4. Char(code)
        5. 4.8.5. GetFieldName(field)
        6. 4.8.6. Get(DocumentsPathListing)
      9. 4.9. External SQL Data Sources (ESS) Enhancements
        1. 4.9.1. Additional SQL database support
        2. 4.9.2. Value lists based on external SQL data
        3. 4.9.3. Single Sign-On for remote Windows clients
        4. 4.9.4. Handling of DATETIME values — MS SQL Server
      10. 4.10. Bento Integration
      11. 4.11. File Recovery Improvements
      12. 4.12. Layout Mode Enhancements
        1. 4.12.1. Inserting an object into the tab order
        2. 4.12.2. Defining tooltips in Pro
        3. 4.12.3. Additional font sizes in the format menu
      13. 4.13. Send Mail by SMTP
      14. 4.14. Quick Start Screen Enhancements
      15. 4.15. Import/Export Enhancements
      16. 4.16. Save Target Printer
      17. 4.17. The Manage Scripts Interface
      18. 4.18. Other Useful Enhancements
        1. 4.18.1. IPv6 Support
        2. 4.18.2. Format changes for automatically generated log files
        3. 4.18.3. Updated templates and themes
        4. 4.18.4. FileMaker Pro Advanced Script Debugger enhancements
        5. 4.18.5. Relookup Replace and Field Contents no longer commit
        6. 4.18.6. Only a single sharing error for multiple files
        7. 4.18.7. Script error codes and control commands
  7. II. Introduction to Database Design
    1. 5. Creating a Database
      1. 5.1. Before Getting Started
      2. 5.2. Creating a New Database File
        1. 5.2.1. Adding tables and fields
        2. 5.2.2. Working with the Field Options dialog: Validation and Auto-Entry
        3. 5.2.3. Setting up simple calculations
        4. 5.2.4. Capturing simple metadata
        5. 5.2.5. Creating relationships between tables
        6. 5.2.6. Adding aggregating calcs
      3. 5.3. Viewing and Interacting with Data
        1. 5.3.1. Looking at the multiple uses of layouts
        2. 5.3.2. Creating records and entering data
        3. 5.3.3. Editing or deleting data
        4. 5.3.4. Finding and sorting data you've already entered
        5. 5.3.5. Using special find symbols
        6. 5.3.6. Searching with the range and wild card operators
      4. 5.4. Avoiding the Need for Data Duplication
        1. 5.4.1. Recognizing the visual cues to data relationships
        2. 5.4.2. Information has a logical flow
        3. 5.4.3. Anticipating the user
        4. 5.4.4. Making complex things simple
      5. 5.5. Getting Started with File Security
        1. 5.5.1. Working with accounts and privilege sets
        2. 5.5.2. Setting a default account and password
      6. 5.6. Thinking about Usability
        1. 5.6.1. Moving between records
        2. 5.6.2. Managing context
        3. 5.6.3. Moving between tables
        4. 5.6.4. Using and changing views
        5. 5.6.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 and 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. Defining Tooltips
        1. 6.3.1. Using conditional tooltips
        2. 6.3.2. Keeping track of tooltips
      4. 6.4. Different Kinds of Layout Objects
        1. 6.4.1. Static and dynamic objects
        2. 6.4.2. Inherent object properties
        3. 6.4.3. Conditional format attributes
      5. 6.5. FileMaker as a Graphical Environment
        1. 6.5.1. Building graphic objects in FileMaker
        2. 6.5.2. Default object formats and attributes
        3. 6.5.3. Controlling stacking and alignment
        4. 6.5.4. Bringing in graphics from other applications
      6. 6.6. Interacting with Layout Objects
        1. 6.6.1. Keyboard control of a layout
        2. 6.6.2. Setting the tab order
        3. 6.6.3. Assigning names to layout objects
        4. 6.6.4. Controlling visual spell-checking
      7. 6.7. The Tab Control and Its Uses
        1. 6.7.1. Defining and creating a tab panel
        2. 6.7.2. Navigating between tab panels
        3. 6.7.3. Tab panel limitations
      8. 6.8. Displaying Related Data
        1. 6.8.1. Working within layout context
        2. 6.8.2. Setting up a portal
      9. 6.9. The Magic of Buttons
        1. 6.9.1. Defining buttons
        2. 6.9.2. Button scope and button commands
        3. 6.9.3. The button as an object
      10. 6.10. The Web Viewer: Inviting in the World
        1. 6.10.1. Setting up a Web viewer
        2. 6.10.2. Controlling a Web viewer
        3. 6.10.3. Complementary data concepts
      11. 6.11. Reports and Data Output
        1. 6.11.1. Considerations for printed output
        2. 6.11.2. Using fonts
        3. 6.11.3. Page sizes and page setup
        4. 6.11.4. Paper output versus PDF or Excel output
        5. 6.11.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
          2. 7.3.5.2. The Count( ) function
          3. 7.3.5.3. The Date( ) function
          4. 7.3.5.4. The Round( ) function
          5. 7.3.5.5. The Length( ) function
        6. 7.3.6. Doing some simple calculations
          1. 7.3.6.1. Commission on earnings above a threshold
          2. 7.3.6.2. Calculating initials from a person's name
          3. 7.3.6.3. Compound interest at a known rate over a given period
          4. 7.3.6.4. Current quarter of the calendar year
          5. 7.3.6.5. Changing ampersands to "and" in a block of text
          6. 7.3.6.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 by using relationships
        4. 7.6.4. Deciding what goes where
        5. 7.6.5. The FileMaker relational model
    4. 8. The Processes: FileMaker Scripting
      1. 8.1. Scripting: 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 Scripts 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 FileMaker Scripting
        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 folders
        4. 8.7.4. Reordering and grouping scripts
        5. 8.7.5. Filtering scripts by folder
        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
      9. 8.9. Calling Your Scripts
        1. 8.9.1. The Scripts menu
        2. 8.9.2. Script hotkeys
        3. 8.9.3. Scripts assigned to custom menu commands
        4. 8.9.4. Layout buttons
        5. 8.9.5. Calling scripts from other scripts
        6. 8.9.6. On Timer Script Triggers
        7. 8.9.7. File Open and File Close scripts
        8. 8.9.8. Layout event Script Triggers
        9. 8.9.9. Object event Script Triggers
        10. 8.9.10. External script calls
  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. Nonprinting 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
          6. 11.1.3.6. 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)
        3. 11.3.3. Cartesian joins
        4. 11.3.4. Multi-predicate relationships
      4. 11.4. Alternative Relationship Techniques
        1. 11.4.1. Multi-Key fields
        2. 11.4.2. Compound keys
        3. 11.4.3. One-way relationships
        4. 11.4.4. Join tables
        5. 11.4.5. Naturally occurring joins
      5. 11.5. Working with Data Arrays
        1. 11.5.1. Repeating fields as an array handler
        2. 11.5.2. Collapsing and expanding arrays
        3. 11.5.3. Relationship-based techniques for managing data
          1. 11.5.3.1. Allowing creation via relationship
          2. 11.5.3.2. Using self joins
          3. 11.5.3.3. The isolating relationship
      6. 11.6. Graph Techniques — Spiders, Squids, and Anchor-Buoy
        1. 11.6.1. Constellations and modular centers
        2. 11.6.2. A satellite-based graph solution
        3. 11.6.3. Segmentation on functional lines
      7. 11.7. Documenting the Database Structure
        1. 11.7.1. Graph annotations
        2. 11.7.2. Naming conventions
        3. 11.7.3. Field commenting
        4. 11.7.4. Ancillary notes and documentation
      8. 11.8. The Concept of Layers
        1. 11.8.1. "Back end" and "front end"
        2. 11.8.2. The business or procedural layer
        3. 11.8.3. FileMaker as an integrated environment
        4. 11.8.4. Separation anxiety
      9. 11.9. File Architecture versus Data Structure
        1. 11.9.1. Multi-file solutions
        2. 11.9.2. The modular approach
        3. 11.9.3. Interface files
        4. 11.9.4. Approaches to separation of data
        5. 11.9.5. Costs and benefits of separation
      10. 11.10. Separation and External SQL Sources
        1. 11.10.1. Understanding the rules
        2. 11.10.2. Working within constraints
        3. 11.10.3. Supporting the user
      11. 11.11. Implementing Separation in an Existing Solution
        1. 11.11.1. Establishing data source(s)
        2. 11.11.2. Re-pointing Table Occurrences
        3. 11.11.3. Creating separate graphs
      12. 11.12. Deployment Considerations
        1. 11.12.1. Your remotest dreams
        2. 11.12.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
          1. 12.1.2.1. Making context explicit
          2. 12.1.2.2. Avoiding circular 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 2009 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. Completing the circuit with GetFieldName( )
        3. 12.10.3. 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
        1. 12.12.1. The user over-ride capability
        2. 12.12.2. Auto-enter calculations and storage
        3. 12.12.3. The Do Not Replace option
      13. 12.13. Global Calculations
        1. 12.13.1. The moon follows you everywhere
        2. 12.13.2. Managing global dependencies
        3. 12.13.3. The freedom and efficiency of global calculations
      14. 12.14. Environment and Metadata
        1. 12.14.1. The Get( ) functions
        2. 12.14.2. Design functions
      15. 12.15. Calculations Using Custom Functions
      16. 12.16. Documenting Your Code
        1. 12.16.1. Code formatting
        2. 12.16.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
        4. 13.4.4. Script Triggers
      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 Scripts
        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)
        2. 13.9.2. Moving and resizing windows
          1. 13.9.2.1. Determining window dimensions
        3. 13.9.3. Creating windows off-screen
        4. 13.9.4. Freezing and refreshing the screen
      10. 13.10. Scripting Data Import and Export
        1. 13.10.1. Exporting field contents
        2. 13.10.2. Exporting table data
        3. 13.10.3. Selecting fields for export
        4. 13.10.4. Import options
        5. 13.10.5. Data matching for import
          1. 13.10.5.1. Synchronizing and updating data
          2. 13.10.5.2. Other import options
        6. 13.10.6. Loading and unloading container objects
      11. 13.11. Pivoting Data between Tables
        1. 13.11.1. Using utility relationships
        2. 13.11.2. Managing related data (walking through related records)
      12. 13.12. Going over Some Practical Examples
        1. 13.12.1. Locating unique records
        2. 13.12.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
      4. 16.4. Temporary Edit Interface Techniques
        1. 16.4.1. The Data Viewer concept
        2. 16.4.2. The legitimate purpose of record locking
        3. 16.4.3. Creating double-blind entry systems
      5. 16.5. Field Masking, Filtering, and Error Rejection
        1. 16.5.1. Applying standard data formations
        2. 16.5.2. Dealing with trailing spaces and carriage returns
        3. 16.5.3. Rejecting out-of-scope characters
        4. 16.5.4. Handling styled source text
      6. 16.6. Built-In Logging Capabilities
        1. 16.6.1. Making use of auto-enter options
        2. 16.6.2. Capturing and extending standard metadata
      7. 16.7. Script Logging
        1. 16.7.1. Infrastructure for script logging
        2. 16.7.2. Tracking script execution
          1. 16.7.2.1. Script-specific context variables
          2. 16.7.2.2. Script diagnostics
      8. 16.8. Capturing User Edits in Detail
        1. 16.8.1. Trapping edits, field-by-field
        2. 16.8.2. Incorporating ancillary data
        3. 16.8.3. Logging record deletions
      9. 16.9. Managing the Accumulation of Log Data
        1. 16.9.1. Archiving options
        2. 16.9.2. Generating secondary output
      10. 16.10. Implementing Roll-Back Capabilities
        1. 16.10.1. Chronological roll-back
        2. 16.10.2. Alternative undo and roll-back capabilities
        3. 16.10.3. Using logs to roll forward
      11. 16.11. Alternative Logging Approaches
        1. 16.11.1. Logs as Data
        2. 16.11.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 box
        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. Creating Custom Menus
        1. 18.4.1. Defining menus
        2. 18.4.2. Editing individual menus
          1. 18.4.2.1. Benefits of the Script Step action
          2. 18.4.2.2. Benefits of window widgets
        3. 18.4.3. Adding menus to sets
        4. 18.4.4. Assigning menu sets throughout your file
          1. 18.4.4.1. Setting the default menu set for a file
          2. 18.4.4.2. Determining a menu set for each layout
          3. 18.4.4.3. Controlling menu sets via script
      5. 18.5. Custom Functions
        1. 18.5.1. Defining custom functions
        2. 18.5.2. Custom functions as an aid to syntax readability
        3. 18.5.3. Maximizing efficiency and ease of use
      6. 18.6. Custom Functions and Recursion
        1. 18.6.1. Things that only custom functions can do
        2. 18.6.2. The stack and the limits of recursion
        3. 18.6.3. Tail recursion in practice
        4. 18.6.4. Some useful examples
          1. 18.6.4.1. Creating an acronym from a supplied phrase
          2. 18.6.4.2. Extracting a character set from a supplied block of text
          3. 18.6.4.3. Removing an unspecified number of leading carriage returns
      7. 18.7. Creating Runtime Applications
        1. 18.7.1. Generating a stand-alone solution
        2. 18.7.2. Binding for each platform
        3. 18.7.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.1.2. Calling Windows Command-Line scripts
        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. Expanding Your Knowledge with Additional Resources
      1. A.1. From the Horse's Mouth
      2. A.2. Professional Consulting and Development Services
      3. A.3. Online Design and Development Tips and Tricks
      4. A.4. Online Forums and Mailing Lists
      5. A.5. Books and Periodicals
    2. B. About the Web Site
      1. B.1. What's on the Web Site
      2. B.2. Troubleshooting