Cover image for FileMaker Pro 9: The Missing Manual

Book description

FileMaker Pro 9: The Missing Manual is the clear, thorough and accessible guide to the latest version of this popular desktop database program. FileMaker Pro lets you do almost anything with the information you give it. You can print corporate reports, plan your retirement, or run a small country -- if you know what you're doing. This book helps non-technical folks like you get in, get your database built, and get the results you need. Pronto. The new edition gives novices and experienced users the scoop on versions 8.5 and 9. It offers complete coverage of timesaving new features such as the Quick Start screen that lets you open or a create a database in a snap, the handy "save to" buttons for making Excel documents or PDFs, the multiple level Undo and Redo commands let you step backwards through your typing tasks, and much more. With FileMaker Pro 9: The Missing Manual, you can:

  • Get your first database running in minutes and perform basic tasks right away.

  • Catalog people, processes and things with streamlined data entry and sorting tools.

  • Learn to use layout tools to organize the appearance of your database.

  • Use your data to generate reports, correspondence and other documents with ease.

  • Create, connect, and manage multiple tables and set up complex relationships that show you just the data you need.

  • Crunch numbers, search text, or pin down dates and times with dozens of built-in formulas.

  • Automate repetitive tasks with FileMaker Pro 9's easy-to-learn scripting language.

  • Protect your database with passwords and set up privileges to determine what others can do once they gain entry.

  • Outfit your database for the Web and import and export data to other formats.

Each chapter in the book contains "living examples" -- downloadable tutorials that help you learn how to build a database by actually doing it. You also get plenty of sound, objective advice that lets you know which features are really useful, and which ones you'll barely touch. To make the most of FileMaker Pro 9, you need the book that should have been in the box.

Table of Contents

  1. FileMaker Pro 9: The Missing Manual
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    2. A Note Regarding Supplemental Files
    3. Introduction
      1. Why FileMaker Pro?
      2. What's New in FileMaker Pro 9
      3. The Very Basics
      4. About This Book
        1. About the Outline
        2. Living Examples
        3. Macintosh and Windows
        4. About → These → Arrows
        5. About Missing Manuals.com
        6. Safari® Enabled
    4. I. Introduction to FileMaker Pro
      1. 1. Your First Database
        1. 1.1. A Very Quick Database Tour
          1. 1.1.1. Content Area
          2. 1.1.2. Mode Pop-up Menu
            1. 1.1.2.1. Status area and the status area controls
            2. 1.1.2.2. Zoom control
            3. 1.1.2.3. Scroll bars
        2. 1.2. Creating a New Database
          1. 1.2.1. Choosing a Starter Solution
        3. 1.3. Opening and Closing Database Files
          1. 1.3.1. Opening a Database
          2. 1.3.2. Closing a Database
        4. 1.4. Saving Your Databases
          1. 1.4.1. Saving a Copy of Your Database
            1. 1.4.1.1. Saving a clone of your database
        5. 1.5. Adding Records to Your Database
          1. 1.5.1. Creating a Record
          2. 1.5.2. Editing a Record
            1. 1.5.2.1. Moving between fields
          3. 1.5.3. Adding More Records
            1. 1.5.3.1. Duplicating a record
            2. 1.5.3.2. Deleting records
            3. 1.5.3.3. Revert the record
        6. 1.6. Navigating Your Database
          1. 1.6.1. Navigating Record by Record
          2. 1.6.2. Keyboard Shortcuts
          3. 1.6.3. Finding Records
            1. 1.6.3.1. Performing a find
            2. 1.6.3.2. FastMatch
        7. 1.7. Same Database, Multiple Windows
          1. 1.7.1. Automatically Arranging Windows
          2. 1.7.2. Hiding Windows
            1. 1.7.2.1. Closing hidden windows
      2. 2. Organizing and Editing Records
        1. 2.1. Views
          1. 2.1.1. Form View
          2. 2.1.2. List View
          3. 2.1.3. Table View
        2. 2.2. Advanced Find Mode
          1. 2.2.1. Finding by Omitting
          2. 2.2.2. Constraining and Extending the Found Set
            1. 2.2.2.1. Constraining the found set
            2. 2.2.2.2. Extending the found set
          3. 2.2.3. Refining Searches with Find Symbols
            1. 2.2.3.1. The wildcard (*)
            2. 2.2.3.2. Less Than (<), Less Than or Equal To (<), Greater Than (>), and Greater Than or Equal To (>)
            3. 2.2.3.3. Exact Match (=), Literal Text (""), and Field Content Match (= =)
            4. 2.2.3.4. Range (…)
            5. 2.2.3.5. Duplicates (!)
            6. 2.2.3.6. Today's Date (//) and Invalid Date or Time (?)
            7. 2.2.3.7. One Character (@), One Digit (#), and Zero or More Characters (*)
            8. 2.2.3.8. Relaxed Search (~)
          4. 2.2.4. Refining a Found Set with Omit Commands
            1. 2.2.4.1. Omit Record
            2. 2.2.4.2. Omit Multiple
            3. 2.2.4.3. Show Omitted Only
        3. 2.3. Changing Multiple Records
        4. 2.4. Sorting Records
          1. 2.4.1. Multiple Sort Fields
        5. 2.5. Editing What's in Your Fields
          1. 2.5.1. Find and Replace
          2. 2.5.2. Fields for Lots of Text
          3. 2.5.3. Drag-and-drop Editing
          4. 2.5.4. Text Formatting
            1. 2.5.4.1. Font
            2. 2.5.4.2. Size
            3. 2.5.4.3. Style
            4. 2.5.4.4. Text Color
            5. 2.5.4.5. The Text Format dialog box
        6. 2.6. Paragraph Formatting
          1. 2.6.1. Align Text
          2. 2.6.2. Line Spacing
          3. 2.6.3. The Text Ruler
          4. 2.6.4. Detailed Formatting in the Paragraph Box
          5. 2.6.5. Tabs
            1. 2.6.5.1. Setting tabs in the Text Ruler
            2. 2.6.5.2. Setting tabs in the Tabs dialog box
            3. 2.6.5.3. Editing tab stops in the Tabs dialog box
        7. 2.7. Beyond Text: Container Fields
          1. 2.7.1. Pictures
            1. 2.7.1.1. Copy and paste
            2. 2.7.1.2. Insert → Picture
          2. 2.7.2. QuickTime
          3. 2.7.3. Sound
            1. 2.7.3.1. Recording sound
          4. 2.7.4. File
            1. 2.7.4.1. Exporting data from container fields
        8. 2.8. Checking Spelling
          1. 2.8.1. Spell Checking in One Pass
          2. 2.8.2. Spell Checking as You Type
          3. 2.8.3. Managing Spelling Dictionaries
            1. 2.8.3.1. User dictionaries
        9. 2.9. Printing and Preview Mode
      3. 3. Building a New Database
        1. 3.1. Tables and Fields
          1. 3.1.1. Tables: The Foundation of Your Database
          2. 3.1.2. Figuring Out What Fields You Need
        2. 3.2. Defining Fields
          1. 3.2.1. Changing a Field's Name
          2. 3.2.2. Duplicating a Field
          3. 3.2.3. Deleting a Field
          4. 3.2.4. Reordering the Field List
          5. 3.2.5. Printing Field Definitions
          6. 3.2.6. Field Types
            1. 3.2.6.1. Text
            2. 3.2.6.2. Number
            3. 3.2.6.3. Date
            4. 3.2.6.4. Time
            5. 3.2.6.5. Timestamp
            6. 3.2.6.6. Container
            7. 3.2.6.7. Calculation and summary
        3. 3.3. Advanced Field Options
          1. 3.3.1. Auto-Enter Options
            1. 3.3.1.1. Creation values
            2. 3.3.1.2. Modification values
            3. 3.3.1.3. Serial number
            4. 3.3.1.4. Value from last visited record
            5. 3.3.1.5. Data
            6. 3.3.1.6. Calculated value
            7. 3.3.1.7. Looked-up value
            8. 3.3.1.8. Prohibit modification of value during data entry
          2. 3.3.2. Validation Options
            1. 3.3.2.1. When to perform validation
            2. 3.3.2.2. Overriding validation
          3. 3.3.3. Validation Requirements
            1. 3.3.3.1. Strict data type
            2. 3.3.3.2. Not empty
            3. 3.3.3.3. Unique
            4. 3.3.3.4. Existing value
            5. 3.3.3.5. Member of value list
            6. 3.3.3.6. In range
            7. 3.3.3.7. Maximum number of characters
          4. 3.3.4. Storage Options
            1. 3.3.4.1. Global storage
            2. 3.3.4.2. Repeating fields
          5. 3.3.5. Indexing
            1. 3.3.5.1. Automatic indexing
            2. 3.3.5.2. Controlling indexing manually
            3. 3.3.5.3. Indexing language
        4. 3.4. Bringing It All Together
    5. II. Layout Basics
      1. 4. Layout Basics
        1. 4.1. What Is a Layout?
          1. 4.1.1. Types of Layouts
        2. 4.2. Switching Between Layouts
        3. 4.3. What Makes a Layout
          1. 4.3.1. Layout Parts
            1. 4.3.1.1. Parts in form view
            2. 4.3.1.2. Parts in list view
            3. 4.3.1.3. Parts in Preview mode
            4. 4.3.1.4. When to use 'em
          2. 4.3.2. Layout Objects
            1. 4.3.2.1. Text objects
            2. 4.3.2.2. Lines and shapes
            3. 4.3.2.3. Images
            4. 4.3.2.4. Fields
        4. 4.4. Layout Mode
          1. 4.4.1. The Status Area
          2. 4.4.2. View Options
            1. 4.4.2.1. Go to Layout
            2. 4.4.2.2. Page Margins
            3. 4.4.2.3. Graphic Rulers
            4. 4.4.2.4. Text Ruler
            5. 4.4.2.5. Ruler Lines
            6. 4.4.2.6. T-Squares
            7. 4.4.2.7. Object Info
            8. 4.4.2.8. Buttons
            9. 4.4.2.9. Sample Data
            10. 4.4.2.10. Text Boundaries
            11. 4.4.2.11. Field Boundaries
            12. 4.4.2.12. Sliding Objects
            13. 4.4.2.13. Non-Printing Objects
            14. 4.4.2.14. Tooltips
            15. 4.4.2.15. Toolbars
            16. 4.4.2.16. Status area
            17. 4.4.2.17. Zoom in and out
          3. 4.4.3. Layout Setup
            1. 4.4.3.1. Layout name
          4. 4.4.4. Layout Tools
            1. 4.4.4.1. The drawing tools
            2. 4.4.4.2. The control tools
            3. 4.4.4.3. The field and part tools
            4. 4.4.4.4. The line and fill tools
          5. 4.4.5. Working with Parts
            1. 4.4.5.1. Deleting a part
            2. 4.4.5.2. Resizing parts
            3. 4.4.5.3. Coloring a part
          6. 4.4.6. Adding and Editing Text
            1. 4.4.6.1. Adding new text
            2. 4.4.6.2. Changing text font, size, and style
            3. 4.4.6.3. Editing existing text
          7. 4.4.7. Formatting Fields
            1. 4.4.7.1. Text formats
            2. 4.4.7.2. Other field type formats
          8. 4.4.8. Field Controls
            1. 4.4.8.1. Field/Control setup
            2. 4.4.8.2. Field borders
          9. 4.4.9. Laying Out the Layout
            1. 4.4.9.1. Resizing objects
            2. 4.4.9.2. Aligning objects
            3. 4.4.9.3. Moving objects
            4. 4.4.9.4. Adding images
          10. 4.4.10. Adding a Dividing Line Between Layout Parts
          11. 4.4.11. Finishing Touches
          12. 4.4.12. Autoresize
            1. 4.4.12.1. Anchors
            2. 4.4.12.2. Making Autoresize work for your layout
            3. 4.4.12.3. Keeping it small
          13. 4.4.13. Wrapping up
      2. 5. Creating Layouts
        1. 5.1. The Lowdown on Layouts
          1. 5.1.1. Standard Form
          2. 5.1.2. Columnar List/Report
          3. 5.1.3. Table View
          4. 5.1.4. Labels or Vertical Labels
          5. 5.1.5. Envelope
          6. 5.1.6. Blank Layout
        2. 5.2. Creating a Layout from Scratch
          1. 5.2.1. Adding a New Layout
          2. 5.2.2. Set Up the Header
            1. 5.2.2.1. Matching part sizes with the Object Info palette
            2. 5.2.2.2. Matching part sizes with the T-squares
            3. 5.2.2.3. Adding the finishing touches
          3. 5.2.3. Set Up the Body
            1. 5.2.3.1. Making data flow with merge fields
            2. 5.2.3.2. Using symbols to show important info
            3. 5.2.3.3. Arranging the objects
            4. 5.2.3.4. Adding a field
            5. 5.2.3.5. Copying formatting with the Format Painter
            6. 5.2.3.6. Alternating the body color
          4. 5.2.4. Setting Layout View
          5. 5.2.5. Setting Anchors in List Layouts
        3. 5.3. Creating Layouts for Reports
          1. 5.3.1. Visualize the Result Preview Mode
          2. 5.3.2. Creating a Report Layout with an Assistant
          3. 5.3.3. Print Margins
          4. 5.3.4. Improving the Report Layout
            1. 5.3.4.1. Tidy up the header
            2. 5.3.4.2. Rearrange the body
            3. 5.3.4.3. Tweak the footer
          5. 5.3.5. Sliding Layout Objects
            1. 5.3.5.1. When to use sliding
            2. 5.3.5.2. Choosing the objects to slide
            3. 5.3.5.3. Setting sliding options
            4. 5.3.5.4. The Sliding/Printing dialog box
            5. 5.3.5.5. Using sliding on the report layout
          6. 5.3.6. Setting Layout Order
      3. 6. Advanced Layouts and Reports
        1. 6.1. Value Lists
          1. 6.1.1. Creating Value Lists
            1. 6.1.1.1. Values from a field
            2. 6.1.1.2. Value list from another file
          2. 6.1.2. Editing Value Lists
        2. 6.2. Advanced Field Controls
          1. 6.2.1. Edit Box
          2. 6.2.2. Drop-down List
          3. 6.2.3. Pop-up Menu
          4. 6.2.4. Checkbox Set
          5. 6.2.5. Radio Button Set
          6. 6.2.6. Drop-down Calendar
          7. 6.2.7. Auto-Complete
            1. 6.2.7.1. Auto-Complete in Edit Boxes
            2. 6.2.7.2. Auto-Complete in drop-down lists
          8. 6.2.8. Repetitions
          9. 6.2.9. Display Data From
        3. 6.3. Field Behavior
          1. 6.3.1. Field Entry
          2. 6.3.2. Select Entire Contents of Field on Entry
          3. 6.3.3. Input Method
          4. 6.3.4. Field-level Visual Spell Checking
          5. 6.3.5. Go To Next Field Using
        4. 6.4. Conditional Formatting
          1. 6.4.1. Conditional Formatting of Fields
          2. 6.4.2. Conditional Formatting of Text Objects
          3. 6.4.3. Multiple Conditions
          4. 6.4.4. More Formatting Options
          5. 6.4.5. Removing Conditional Formatting
        5. 6.5. Print-related Layout Options
          1. 6.5.1. Non-printing Objects
          2. 6.5.2. Columns
        6. 6.6. Format the Number/Date/Time/Graphic
          1. 6.6.1. Number Formatting
            1. 6.6.1.1. General format
            2. 6.6.1.2. Leave data formatted as entered
            3. 6.6.1.3. Format as Boolean
            4. 6.6.1.4. Format as decimal
          2. 6.6.2. Date Formatting
            1. 6.6.2.1. Leave data formatted as entered
            2. 6.6.2.2. Format as
            3. 6.6.2.3. Custom
          3. 6.6.3. Time Formatting
            1. 6.6.3.1. Leave data formatted as entered
            2. 6.6.3.2. Format as
          4. 6.6.4. Timestamp Formatting
          5. 6.6.5. Graphic Formatting
            1. 6.6.5.1. Scale
            2. 6.6.5.2. Alignment
        7. 6.7. Adding Buttons
          1. 6.7.1. Creating Buttons with the Button Tool
          2. 6.7.2. Turning an Existing Layout Object into a Button
          3. 6.7.3. Making a Button Not a Button
          4. 6.7.4. Button Actions
            1. 6.7.4.1. Go to Layout
            2. 6.7.4.2. Go to Record/Request/Page
            3. 6.7.4.3. Go to Field
            4. 6.7.4.4. Go to Next Field and Go to Previous Field
            5. 6.7.4.5. Enter Browse Mode, Enter Find Mode, and Enter Preview Mode
            6. 6.7.4.6. Editing actions
            7. 6.7.4.7. Field actions
            8. 6.7.4.8. Record actions
            9. 6.7.4.9. Found Set actions
            10. 6.7.4.10. Window actions
            11. 6.7.4.11. Print actions
            12. 6.7.4.12. Open actions
            13. 6.7.4.13. Exiting FileMaker
        8. 6.8. Tab Order
          1. 6.8.1. Buttons in the Tab Order
          2. 6.8.2. Customizing the Tab Order
          3. 6.8.3. Fixing the Tab Order in the People Database
          4. 6.8.4. Preserving the Automatic Order
        9. 6.9. Web Viewer Controls
          1. 6.9.1. Putting a Web Viewer on a Layout
          2. 6.9.2. Web Viewer Options
            1. 6.9.2.1. Allow interaction with Web viewer content
            2. 6.9.2.2. Display content in Find mode
            3. 6.9.2.3. Display progress bar
            4. 6.9.2.4. Display status message
        10. 6.10. Tab Controls
          1. 6.10.1. Creating a Tab Control
          2. 6.10.2. Editing Tab Controls
            1. 6.10.2.1. Adding, removing, and reordering tabs
            2. 6.10.2.2. Default front tab
            3. 6.10.2.3. Tab justification
            4. 6.10.2.4. Appearance
            5. 6.10.2.5. Tab Width
            6. 6.10.2.6. Dismissing the Tab Control Setup dialog box
            7. 6.10.2.7. Formatting a tab control
          3. 6.10.3. Deleting a Tab Control
        11. 6.11. Reports and Summary Fields
          1. 6.11.1. Summary Fields
          2. 6.11.2. Creating a Summary Report
            1. 6.11.2.1. Adding a summary part
            2. 6.11.2.2. Sub-summary parts
            3. 6.11.2.3. Creating a sub-summary part
          3. 6.11.3. Other Summary Field Types
            1. 6.11.3.1. Total of
            2. 6.11.3.2. Average of
            3. 6.11.3.3. Count of
            4. 6.11.3.4. Minimum and Maximum
            5. 6.11.3.5. Standard Deviation of
            6. 6.11.3.6. Fraction of Total
            7. 6.11.3.7. Summarizing repetitions
    6. III. Multiple Tables and Relationships
      1. 7. Multiple Tables and Relationships
        1. 7.1. Relational Databases Explained
        2. 7.2. Modeling Your Database
          1. 7.2.1. Choosing Entities
          2. 7.2.2. Finding Relationships
            1. 7.2.2.1. One-to-many relationships
            2. 7.2.2.2. One-to-one relationships
            3. 7.2.2.3. Many-to-many relationships
          3. 7.2.3. The Entity-relationship Diagram
            1. 7.2.3.1. Creating an ER diagram
          4. 7.2.4. Keys
            1. 7.2.4.1. Choosing a good key field
            2. 7.2.4.2. Primary and foreign keys
          5. 7.2.5. Join Tables
        3. 7.3. Creating a Relational Database
          1. 7.3.1. Creating the Tables
          2. 7.3.2. Creating the Fields
            1. 7.3.2.1. Adding primary keys
            2. 7.3.2.2. Adding foreign keys
          3. 7.3.3. Defining the Relationships
          4. 7.3.4. Cleaning Up the Relationship Graph
            1. 7.3.4.1. Selection tools
            2. 7.3.4.2. Arrange the graph
            3. 7.3.4.3. Color your table occurrences
            4. 7.3.4.4. Adding notes
            5. 7.3.4.5. Printing the graph
            6. 7.3.4.6. Deleting relationships
          5. 7.3.5. Finishing the Field Definitions
            1. 7.3.5.1. Customers table fields
            2. 7.3.5.2. Jobs table fields
            3. 7.3.5.3. Expenses table fields
            4. 7.3.5.4. Timeslips table fields
            5. 7.3.5.5. Invoices table fields
            6. 7.3.5.6. Line Items table fields
            7. 7.3.5.7. Payments table fields
        4. 7.4. Relational Databases
          1. 7.4.1. One Table Occurrence, One Found Set
          2. 7.4.2. Viewing Related Fields
          3. 7.4.3. Adding or Editing Data with Relationships
          4. 7.4.4. Editing Related Data
            1. 7.4.4.1. Portals
            2. 7.4.4.2. Editing fields through a portal
      2. 8. Advanced Relationship Techniques
        1. 8.1. Creating Related Records
          1. 8.1.1. Value Lists Based on Fields
          2. 8.1.2. Creating Related Records Through a Portal
            1. 8.1.2.1. Preparing the relationship
            2. 8.1.2.2. Adding a portal to a layout
            3. 8.1.2.3. Adding records in a portal
          3. 8.1.3. Navigating Between Related Records
            1. 8.1.3.1. Go to Related Record
            2. 8.1.3.2. GTRR with a found set
        2. 8.2. Table Occurrences
          1. 8.2.1. Understanding Table Occurrences
          2. 8.2.2. Relationships with Table Occurrences
            1. 8.2.2.1. Adding a new key field to a table
            2. 8.2.2.2. Adding a table occurrence
            3. 8.2.2.3. Adding fields for new table occurrences
        3. 8.3. Managing Data with Data Tunneling
          1. 8.3.1. Understanding Data Tunneling
            1. 8.3.1.1. What you need in your data tunneling interface
          2. 8.3.2. Understanding Table Occurrence Groups
          3. 8.3.3. Managing the Relationships Graph
        4. 8.4. Building a Data Tunneling Interface
          1. 8.4.1. Creating a Global Field
          2. 8.4.2. Creating a New Table Occurrence Group
          3. 8.4.3. Building a Global Field Layout
            1. 8.4.3.1. Using a global pop-up field to change the display in a value list
        5. 8.5. Connecting Databases
          1. 8.5.1. Adding a Table Occurrence from Another File
          2. 8.5.2. Defining Data Sources Using a Path
        6. 8.6. Lookups
          1. 8.6.1. Creating Lookups
          2. 8.6.2. Triggering a Lookup
        7. 8.7. Advanced Relationships
          1. 8.7.1. Relationships with More than One Criterion
          2. 8.7.2. Other Relationship Operators
          3. 8.7.3. Creating the Invoice Finder
    7. IV. Introduction to Calculations
      1. 9. Introduction to Calculations
        1. 9.1. Understanding Calculations
          1. 9.1.1. Creating a Calculation
        2. 9.2. The Structure of a Calculation
          1. 9.2.1. Fields
          2. 9.2.2. Constants
            1. 9.2.2.1. Number constants
            2. 9.2.2.2. Text constants
          3. 9.2.3. Operators
            1. 9.2.3.1. Mathematical operators
            2. 9.2.3.2. The concatenation operator
            3. 9.2.3.3. Comparison operators
            4. 9.2.3.4. Logical operators
            5. 9.2.3.5. The ^ Operator
            6. 9.2.3.6. Parentheses
          4. 9.2.4. Functions
            1. 9.2.4.1. Text functions
            2. 9.2.4.2. Text formatting functions
            3. 9.2.4.3. Number functions
            4. 9.2.4.4. Date functions
            5. 9.2.4.5. Time functions
            6. 9.2.4.6. Timestamp functions
            7. 9.2.4.7. Aggregate functions
            8. 9.2.4.8. Summary functions
            9. 9.2.4.9. Repeating functions
            10. 9.2.4.10. Financial functions
            11. 9.2.4.11. Trigonometric functions
            12. 9.2.4.12. Logical functions
            13. 9.2.4.13. Get functions
            14. 9.2.4.14. Design functions
            15. 9.2.4.15. Custom functions
            16. 9.2.4.16. External functions
          5. 9.2.5. Expressions
        3. 9.3. Creating a Calculation Field
          1. 9.3.1. The Specify Calculation Dialog Box
            1. 9.3.1.1. Table occurrence context
            2. 9.3.1.2. Field list
            3. 9.3.1.3. Operators
            4. 9.3.1.4. Function list
            5. 9.3.1.5. Result type
            6. 9.3.1.6. Calculation box
            7. 9.3.1.7. Repetitions
        4. 9.4. Auto-Enter Calculations
        5. 9.5. Validate Data Entry with a Calculation
        6. 9.6. Replacing Data Using a Calculation
        7. 9.7. Comments
      2. 10. Calculations and Data Types
        1. 10.1. Number Crunching Calculations
          1. 10.1.1. Number Operators
          2. 10.1.2. Number Function Types
          3. 10.1.3. Precision
        2. 10.2. Going Beyond Basic Calculations
          1. 10.2.1. Preparing the Data
          2. 10.2.2. Building the Calculation Fields
          3. 10.2.3. Constructing the Calculation
        3. 10.3. Text Parsing Calculations
          1. 10.3.1. The Concatenation Operator
          2. 10.3.2. Text Function Types
          3. 10.3.3. Text Functions
            1. 10.3.3.1. Character functions
            2. 10.3.3.2. Word functions
            3. 10.3.3.3. Text value functions
            4. 10.3.3.4. Text counting functions
            5. 10.3.3.5. Other text parsing functions
          4. 10.3.4. Text Formatting Functions
            1. 10.3.4.1. TextColor and RGB
            2. 10.3.4.2. TextFont
            3. 10.3.4.3. TextSize
            4. 10.3.4.4. TextStyleAdd and TextStyleRemove
        4. 10.4. Date and Time Calculations
          1. 10.4.1. How FileMaker Looks at Time
          2. 10.4.2. Math with Dates and Times
            1. 10.4.2.1. Dates
            2. 10.4.2.2. Times
          3. 10.4.3. Parsing Dates and Times
          4. 10.4.4. Calculations that Create Dates and Times
            1. 10.4.4.1. The secret powers of date
        5. 10.5. Aggregate Calculations
          1. 10.5.1. Numerical Aggregate Functions
          2. 10.5.2. The List Function
        6. 10.6. Containers in Calculations
          1. 10.6.1. Calculations with Pictures, Sounds, Movies, and Files
          2. 10.6.2. Calculations with References
      3. 11. Advanced Calculations
        1. 11.1. Conditional Formatting
          1. 11.1.1. Understanding Boolean Functions
        2. 11.2. Using the Self Function
        3. 11.3. Stored, Unstored, and Global Fields
          1. 11.3.1. Stored and Unstored Calculation Fields
            1. 11.3.1.1. Field dependencies
            2. 11.3.1.2. Deciding when to store
          2. 11.3.2. Global Calculation Fields
        4. 11.4. Logical Functions
          1. 11.4.1. Conditional Functions
            1. 11.4.1.1. The If function
            2. 11.4.1.2. The Case function
            3. 11.4.1.3. The Choose function
          2. 11.4.2. Constructing a Conditional Calculation
            1. 11.4.2.1. Total Due calculation #1: Using the If function
            2. 11.4.2.2. Total Due calculation #2: Using the Case function
        5. 11.5. The Let Function and Variables
          1. 11.5.1. Defining Calculation Variables
            1. 11.5.1.1. Total Due calculation #3: Using the Let function
          2. 11.5.2. The Life of a Variable
            1. 11.5.2.1. Local variables
            2. 11.5.2.2. Global variables
          3. 11.5.3. Nesting Let Functions
      4. 12. Extending Calculations
        1. 12.1. Custom Functions
          1. 12.1.1. Defining a Custom Function
          2. 12.1.2. Editing Custom Functions
          3. 12.1.3. Recursion
            1. 12.1.3.1. Step 1: Solve the first case
            2. 12.1.3.2. Step 2: Assume your function already works, and use it
            3. 12.1.3.3. Step 3: Find a stopping point
        2. 12.2. Plug-ins
          1. 12.2.1. Installing Plug-ins
          2. 12.2.2. Old and New Plug-ins
          3. 12.2.3. Finding Plug-ins
          4. 12.2.4. Creating Your Own Plug-ins
    8. V. Scripting
      1. 13. Scripting Basics
        1. 13.1. Your First Script
          1. 13.1.1. Preparing the Database
          2. 13.1.2. Planning Your Script
          3. 13.1.3. Creating Your Script
          4. 13.1.4. Improving Your Script
        2. 13.2. The Importance of the Layout
          1. 13.2.1. When to Switch
        3. 13.3. Running Scripts
          1. 13.3.1. The Scripts Menu
          2. 13.3.2. Defining Buttons
          3. 13.3.3. Opening and Closing Scripts
        4. 13.4. Understanding ScriptMaker's Windows
        5. 13.5. Organizing Your Scripts
          1. 13.5.1. Creating Script Groups
          2. 13.5.2. Creating Menu Separators
        6. 13.6. Branching and Looping in Scripts
          1. 13.6.1. Branching with If, Else If, Else, and End If
          2. 13.6.2. Testing Multiple Conditions
          3. 13.6.3. Looping
            1. 13.6.3.1. Using Go to Record/Request/Page to exit a loop
      2. 14. Script Steps
        1. 14.1. Go to Layout
        2. 14.2. Scripting for Fields
          1. 14.2.1. Navigating Fields
            1. 14.2.1.1. Go to Field
            2. 14.2.1.2. Go to Next Field and Go to Previous Field
          2. 14.2.2. Editing Commands
          3. 14.2.3. Selecting Text
          4. 14.2.4. Editing Field Data
            1. 14.2.4.1. Avoiding Cut, Copy, and Paste
            2. 14.2.4.2. Set Field
            3. 14.2.4.3. Insert Calculated Result
            4. 14.2.4.4. Inserting other values
            5. 14.2.4.5. Putting data in container fields
          5. 14.2.5. A Field Script in Action
            1. 14.2.5.1. Building the script
          6. 14.2.6. Other Steps That Work with Fields
            1. 14.2.6.1. Perform Find/Replace
            2. 14.2.6.2. Replace Field Contents and Relookup Field Contents
            3. 14.2.6.3. Set Next Serial Value
        3. 14.3. Working with Records
          1. 14.3.1. Creating, Duplicating, and Deleting Records
          2. 14.3.2. Navigating Among Records
            1. 14.3.2.1. Go to Record/Request/Page
            2. 14.3.2.2. Go to Related Record
          3. 14.3.3. Opening, Reverting, and Committing Records
            1. 14.3.3.1. Open Record/Request
            2. 14.3.3.2. Commit Record/Request
            3. 14.3.3.3. Revert Record/Request
          4. 14.3.4. Copying Records
            1. 14.3.4.1. Copy Record/Request
            2. 14.3.4.2. Copy All Records/Requests
          5. 14.3.5. Working with Portals
            1. 14.3.5.1. Go to Portal Row
        4. 14.4. Finding Records
          1. 14.4.1. Performing the User's Find Requests
          2. 14.4.2. Static Find Requests
          3. 14.4.3. Dynamic Find Requests
          4. 14.4.4. Constraining and Extending the Found Set
          5. 14.4.5. Omitting Records
          6. 14.4.6. Modify Last Find
          7. 14.4.7. Save Records as…
            1. 14.4.7.1. Save Records as Excel
            2. 14.4.7.2. Save Records as PDF
        5. 14.5. Sorting Records
        6. 14.6. Working with Windows
          1. 14.6.1. Creating Windows
          2. 14.6.2. Bringing a Window to the Front
          3. 14.6.3. Closing a Window
          4. 14.6.4. Moving and Resizing Existing Windows
            1. 14.6.4.1. Adjust Window
            2. 14.6.4.2. Arrange All Windows
            3. 14.6.4.3. Move/Resize Window
          5. 14.6.5. Other Window-related Script Steps
        7. 14.7. Working with Files
          1. 14.7.1. Opening and Closing Files
          2. 14.7.2. Save a Copy As
          3. 14.7.3. Other File-Related Script Steps
        8. 14.8. Printing
        9. 14.9. Other Script Steps
          1. 14.9.1. Open URL
          2. 14.9.2. Dial Phone
          3. 14.9.3. Set Web Viewer
          4. 14.9.4. Send Email
          5. 14.9.5. Execute SQL
          6. 14.9.6. Flush Cache to Disk
          7. 14.9.7. Exit Application
      3. 15. Advanced Scripting
        1. 15.1. Commenting Scripts
        2. 15.2. Asking and Telling the User
          1. 15.2.1. Showing a Simple Message
          2. 15.2.2. Asking a Simple Question
          3. 15.2.3. Asking a Not-so-simple Question
        3. 15.3. Organizing Complex Scripts
          1. 15.3.1. The Perform Script Script Step
          2. 15.3.2. Halt Script and Exit Script
            1. 15.3.2.1. Exit Script (Result)
            2. 15.3.2.2. Organizing scripts that use Halt or Exit Script
          3. 15.3.3. Script Parameters
          4. 15.3.4. Script Variables
        4. 15.4. Handling Errors
          1. 15.4.1. The Set Error Capture Script Step
          2. 15.4.2. The Get ( LastError ) Function
          3. 15.4.3. The Allow User Abort Script Step
        5. 15.5. Putting a Complex Script Together
          1. 15.5.1. Planning the Script
          2. 15.5.2. Considering Exceptions
          3. 15.5.3. Creating Subscripts
            1. 15.5.3.1. Creating subscript placeholders
            2. 15.5.3.2. Creating skeleton subscripts
          4. 15.5.4. Finishing the Subscripts
            1. 15.5.4.1. The "-find unbilled activity" subscript
            2. 15.5.4.2. Copying and editing existing script steps
          5. 15.5.5. Adding a Script Parameter to a Perform Script Step
            1. 15.5.5.1. The "-create invoice record" subscript
            2. 15.5.5.2. The "-process timeslips" subscript
            3. 15.5.5.3. The "-process expenses" subscript
            4. 15.5.5.4. Finishing the main script
          6. 15.5.6. Testing Scripts
    9. VI. Security and Integration
      1. 16. Security
        1. 16.1. How Security Works
          1. 16.1.1. Who Gets Access
          2. 16.1.2. What They Can Do
        2. 16.2. Privilege Sets
          1. 16.2.1. Managing Privilege Sets
            1. 16.2.1.1. Creating a new privilege set
          2. 16.2.2. Other Privileges
          3. 16.2.3. Data Access and Design Privileges
            1. 16.2.3.1. Record privileges
            2. 16.2.3.2. Creating record-level access
            3. 16.2.3.3. Field-level access
            4. 16.2.3.4. Layout privileges
            5. 16.2.3.5. Value list privileges
            6. 16.2.3.6. Script privileges
            7. 16.2.3.7. Editing a privilege set
        3. 16.3. Managing Accounts
          1. 16.3.1. Adding a New Account
          2. 16.3.2. External Authentication
        4. 16.4. Extended Privileges
          1. 16.4.1. Creating an Extended Privilege
          2. 16.4.2. Checking for an Extended Privilege
          3. 16.4.3. Assigning Extended Privileges
        5. 16.5. Scripts and Security
          1. 16.5.1. Detecting Privileges in a Script
          2. 16.5.2. Handling Security Errors
          3. 16.5.3. Running Scripts with Full Access Privileges
          4. 16.5.4. Managing Security with Scripts
            1. 16.5.4.1. Add Account
            2. 16.5.4.2. Delete Account
            3. 16.5.4.3. Reset Account Password
            4. 16.5.4.4. Change Password
            5. 16.5.4.5. Enable Account
            6. 16.5.4.6. Re-Login
      2. 17. Sharing Data with Other Systems
        1. 17.1. Sharing Your Data with Others
          1. 17.1.1. Save/Send Records As
            1. 17.1.1.1. Saving as Microsoft Excel
            2. 17.1.1.2. Saving as Portable Document Format (PDF)
        2. 17.2. External SQL Sources
          1. 17.2.1. Setting Up ODBC
            1. 17.2.1.1. The SQL database server
            2. 17.2.1.2. Installing the ODBC driver
            3. 17.2.1.3. Configuring the data source
          2. 17.2.2. Connecting FileMaker to a SQL Data Source
          3. 17.2.3. Adding SQL Tables to a FileMaker Database
            1. 17.2.3.1. Creating relationships
            2. 17.2.3.2. Shadow tables
          4. 17.2.4. Using SQL Tables
        3. 17.3. Exporting Data
          1. 17.3.1. Exporting Data
            1. 17.3.1.1. Grouped exports
            2. 17.3.1.2. Export formats
        4. 17.4. Importing Data
          1. 17.4.1. Importing Over Existing Data
            1. 17.4.1.1. Update existing records in found set
            2. 17.4.1.2. Update matching records in found set
          2. 17.4.2. Creating Tables on Import
          3. 17.4.3. Creating a New Database from an Import File
          4. 17.4.4. Converting Older FileMaker Files
        5. 17.5. Import Data Sources
          1. 17.5.1. File
          2. 17.5.2. Folder
            1. 17.5.2.1. Importing a folder of pictures or movies
            2. 17.5.2.2. Importing a folder of text files
          3. 17.5.3. Digital Camera
          4. 17.5.4. XML Data Source
            1. 17.5.4.1. XML export
          5. 17.5.5. ODBC Data Sources
        6. 17.6. Importing and Exporting in a Script
          1. 17.6.1. Save Records Script Steps
          2. 17.6.2. The Import Records Script Step
          3. 17.6.3. The Export Records Script Step
      3. 18. Sharing Your Database
        1. 18.1. FileMaker Network Sharing
          1. 18.1.1. Setting Up a Host Computer
          2. 18.1.2. Opening a Shared File
          3. 18.1.3. The Open Remote File Dialog Box
            1. 18.1.3.1. Choosing a host computer
            2. 18.1.3.2. Choosing a file
        2. 18.2. Sharing over the Internet
          1. 18.2.1. Turning on Web Sharing
            1. 18.2.1.1. Advanced Web Publishing Options
            2. 18.2.1.2. Configuring file access
          2. 18.2.2. Connecting from a Web Browser
          3. 18.2.3. Custom Home Page
        3. 18.3. FileMaker Server
          1. 18.3.1. File Compatibility
          2. 18.3.2. Installing FileMaker Server
            1. 18.3.2.1. Single Machine Installation
            2. 18.3.2.2. Multiple Machine Installation
            3. 18.3.2.3. Configuring Your Server
          3. 18.3.3. Administering FileMaker Server
            1. 18.3.3.1. Installing a Database
            2. 18.3.3.2. Scheduling a Backup
          4. 18.3.4. Testing Your Server
        4. 18.4. Server Hardware
      4. 19. Developer Utilities
        1. 19.1. Copying and Pasting Database Structure
          1. 19.1.1. Importing Tables and Fields
          2. 19.1.2. Copying and Pasting Tables
          3. 19.1.3. Copying and Pasting Fields
          4. 19.1.4. Copying Scripts and Script Steps
        2. 19.2. Script Debugger
          1. 19.2.1. Controlling Script Execution
          2. 19.2.2. Breakpoints
          3. 19.2.3. Examining Errors
          4. 19.2.4. Pause on Error
          5. 19.2.5. Active Scripts
          6. 19.2.6. Working with the Debugger Window
        3. 19.3. The Data Viewer
          1. 19.3.1. The Current Tab
          2. 19.3.2. The Watch Tab
        4. 19.4. Disable Script Steps
        5. 19.5. The Database Design Report
          1. 19.5.1. Generating the DDR
          2. 19.5.2. Using the DDR
          3. 19.5.3. Finding Broken Elements with the DDR
        6. 19.6. Tooltips
        7. 19.7. Custom Menus
          1. 19.7.1. Editing a Menu
          2. 19.7.2. Creating a New Menu
          3. 19.7.3. Removing a Menu
          4. 19.7.4. Installing Custom Menu Sets
        8. 19.8. Developer Utilities
          1. 19.8.1. Using the Developer Utilities
            1. 19.8.1.1. Renaming files
            2. 19.8.1.2. Create Runtime Solution application(s)
            3. 19.8.1.3. Remove admin access from files permanently
            4. 19.8.1.4. Enable Kiosk mode for non-admin accounts
            5. 19.8.1.5. Databases must have a FileMaker file extension
            6. 19.8.1.6. Create error log for any processing errors
            7. 19.8.1.7. Loading and saving settings
          2. 19.8.2. Delivering a Runtime Solution
        9. 19.9. File Maintenance
    10. VII. Appendixes
      1. A. Getting Help
        1. A.1. Getting Help from FileMaker Itself
          1. A.1.1. The Learning Center
          2. A.1.2. FileMaker Pro Help
          3. A.1.3. Templates
          4. A.1.4. XML Examples
        2. A.2. Getting Help from FileMaker, Inc.
          1. A.2.1. Updaters and Downloads
          2. A.2.2. Documentation and Publications
          3. A.2.3. Technical Support
          4. A.2.4. FileMaker Applications
          5. A.2.5. Developer Programs
        3. A.3. Getting Help from the Community
          1. A.3.1. Mail Lists and Newsgroups
          2. A.3.2. Training/Consultants
          3. A.3.3. DevCon
      2. B. Filemaker Error Codes
    11. Index
    12. About the Authors
    13. Colophon
    14. SPECIAL OFFER: Upgrade this ebook with O’Reilly