You are previewing Learn FileMaker Pro 10.
O'Reilly logo
Learn FileMaker Pro 10

Book Description

Learn FileMaker? Pro 10 provides an excellent reference to FileMaker Inc.’s award-winning database program for both beginners and advanced developers. From converting files created with previous versions of FileMaker Pro and sharing data on the web to creating reports and sorting data, this book offers a hands-on approach to getting the most out of your FileMaker Pro databases. Learn how to use the completely redesigned Status area, now known as the Status toolbar; send e-mail right from FileMaker with the SMTP-based Send Mail option; build reports quickly and easily with the Saved Finds feature; automate your database with scripts and activate those scripts with the new script trigger feature; integrate your Bento data into your FileMaker files; work with the enhanced Web viewer.

Table of Contents

  1. Cover
  2. Title
  3. Copyright
  4. Contents
  5. Preface
  6. About the Author
  7. Acknowledgments
  8. Introduction
  9. Part 1 Learning the Basics
    1. Chapter 1 Getting Acquainted with FileMaker Pro
      1. Introduction
      2. What Is a Database?
      3. Using FileMaker Pro
        1. Opening FileMaker Pro
        2. Opening a FileMaker Pro File
      4. Database Concepts
        1. Files
        2. Tables
        3. Records
        4. Fields
        5. Layouts
      5. Saving a Copy of a FileMaker Pro File
      6. Closing a FileMaker Pro File
      7. Quitting FileMaker Pro
      8. Summary
    2. Chapter 2 Menus and Modes
      1. Introduction
      2. Manage Database
        1. Tables Tab
        2. Fields Tab
        3. Relationships Tab
          1. Lookups
          2. Related Data
      3. Modes
        1. Browse Mode
        2. Layout Mode
          1. Layout Types
        3. Find Mode
        4. Preview Mode
      4. Menu Commands
        1. Sort Command
        2. Manage Scripts
        3. Accounts and Privileges
      5. Summary
    3. Chapter 3 Creating Your First Database
      1. Introduction
      2. Planning the Database
        1. Planning This Database
      3. Creating the File
        1. Adding Some Fields
          1. Adding Field Options
      4. Cleaning Up the Layout
        1. Format Painter
        2. Duplicate
        3. Tool Panel
        4. Layout Status Toolbar and Palettes
        5. Drawing Tools
        6. Object Grids
        7. T-Squares
        8. Arrow Keys
        9. Object Info Palette
        10. Alignment8
        11. Locking Objects
      5. Adding Some Data
      6. Adding a Script
        1. Simple New Record Script
        2. Simple Delete Record Script
        3. Simple Find Script
      7. Assigning Scripts to Buttons
      8. Special Situations
      9. Customizing the Status Toolbar10
        1. The Standard Sets
        2. The Customize Palette
        3. Limitations and Cautions
      10. Summary
  10. Part 2 Using FileMaker Pro
    1. Chapter 4 Creating a New Database
      1. Introduction
      2. Considerations
        1. Predefined Databases
        2. Creating Your Own Database
      3. What Are Fields?
        1. Creating a Field
          1. About Field Names
        2. The Eight Field Types
          1. Text Field
          2. Number Field
          3. Date Field
          4. Time Field
          5. Timestamp Field7
          6. Container Field
          7. Calculation Field
          8. Summary Field
          9. Global Storage Option
          10. Repeating Field
      4. Formatting Your Fields in Japanese7
      5. Field Formats on Layouts
        1. Date Format
        2. Number Format
        3. Text Format
        4. Time Format
      6. Summary
    2. Chapter 5 Entering and Modifying Your Data
      1. Introduction
      2. Creating a New Record
        1. Selecting the Record
      3. Adding Data
        1. Selecting the Field
        2. Using Tab Order
        3. Data Entry Shortcuts
          1. Insert
          2. Copy and Paste
          3. Drag-and-Drop
          4. Using Value Lists
          5. Auto-Complete8
          6. Calendar Drop-down8
        4. Using Container Fields
          1. Insert QuickTime
          2. Insert Picture
          3. Insert Sound
          4. Importing Data
        5. Changing Data
          1. Highlighting
          2. Spellchecking
          3. Visual Spell Checker8
          4. Replace Command
          5. Find/Replace
          6. Revert Command
          7. Multiple Level Undo/Redo of Text9
        6. Deleting Data
      4. Summary
    3. Chapter 6 Working with Related Tables—Part 1
      1. Introduction
      2. One or Many Tables?
        1. One Table—Flat File Design
        2. Many Tables—Relational Design
      3. Manage Your Tables
        1. Normalize Your Information
        2. Determine Your Match, or Key, Fields
        3. Introducing “Anchor-Buoy” Relationship Design
        4. Making the Relationship
          1. Relationship Tools7
          2. Tool Icons
          3. Allow Creation of Related Records
          4. Parent and Child
          5. Allow Deletion of Related Records
          6. Sort Related Records
        5. Making a Portal
          1. Drawing the Portal
        6. Portal Tool Behavior
          1. Placing the Fields in a Portal
          2. Adding Data to a Portal Field
          3. Sorting a Portal
        7. Investigating the Related Table
        8. Relating a Table Back to the Parent Table
      4. Performing a Find in a Portal
        1. Performance Considerations
        2. Deleting a Record from a Portal
        3. Dealing with “Portal Pop”
        4. Before We Go
      5. Summary
    4. Chapter 7 Working with Related Tables—Part 2
      1. Introduction
      2. Planning Your Database
      3. Tricky Terms and Catchy Phrases
        1. ER Diagram
        2. One-to-Many Relationship
        3. One-to-One Relationship
        4. Many-to-Many Relationship
        5. The Join, or Link, Table
        6. Self-join Relationship
          1. Multiple-Criteria Relationships7
          2. Unequal Relationships7
          3. The Relationships Graph
          4. Other Notes
      4. Invoice System ER Diagram
      5. Create the Tables
        1. Invoice Table
        2. InvoiceLineItems Table
        3. Importing a Table8
        4. Product Table
          1. Other Possibilities
          2. Example Data
      6. Create the Relationships
        1. Invoice and InvLI
        2. Invoice and Contact
        3. InvLI and Product
        4. Add the Lookups
      7. The Invoice Layout
        1. Adding Drop-downs to the Invoices
          1. Products Drop-down
          2. Getting the Invoice Total
          3. Customers Drop-down
          4. Placing the Customer Field on the Layout
          5. Other Considerations
      8. External Data Source9
      9. Summary
    5. Chapter 8 Finding and Sorting Your Data
      1. Introduction
      2. Finding Records
        1. Methods
        2. The Find Symbols
          1. Match Whole Word (Or Match Empty) (=)
          2. Match Entire Field (= =)
          3. Find Duplicate Values (!)
          4. Less Than (<)
          5. Less Than or Equal (≤ or < = on Macintosh, < = on Windows)
          6. Greater Than (>)
          7. Greater Than or Equal (≥ or > = on Macintosh, > = on Windows)
          8. Range (. . .)
          9. Today’s Date (//)
          10. Invalid Date or Time (?)
          11. Any One Character (@)
          12. Any One Digit (#)
          13. Zero or More Characters (*)
          14. Escape Next Character (\)10
          15. Match Phrase (From Word Start) (“”)
          16. Match Phrase (From Anywhere) (*“”)
          17. Relaxed Search (Japanese only) (~)
          18. AND Finds
          19. OR Finds
        3. Constrain and Extend Found Set
          1. Constrain Found Set
          2. Extend Found Set
        4. Quick Find8
        5. Auto-Complete8
        6. Other Find Tricks
        7. Within Scripts
        8. Find Strategies
        9. Limitations
      3. Saved Finds10
        1. Saved Finds Details
      4. Omitting Records
        1. Methods
          1. Omit Multiple
          2. Omit as Part of a Find
          3. Show Omitted
        2. Within Scripts
        3. Strategies
        4. Limitations
      5. Sorting Records
        1. Methods
        2. Within Scripts
        3. Strategies
          1. Sort By a Summary Field
        4. Dynamic Sub-Summaries10
        5. Modify Table View10
        6. Limitations
      6. Summary
    6. Chapter 9 Creating New Layouts with the Layout Assistant
      1. Introduction
      2. Create a New Layout
        1. Standard Form
          1. Specify Fields
          2. Select a Theme
        2. List/Report
          1. Choose Report Layout
          2. Organize Records by Category
          3. Sort Records
          4. Specify Subtotals
          5. Specify Grand Totals
          6. Header and Footer Information
          7. Create a Script for This Report
          8. Cleaning Up
          9. Sub-summary Part Definition
        3. Table View
        4. Labels
        5. Envelope
        6. Blank Layout
      3. Summary
  11. Part 3 Turning Your Data into Information
    1. Chapter 10 Keeping Your Data Clean and Neat
      1. Introduction
      2. Field Data Entry Options
      3. Auto-Enter Tab (Automatically Entering Values)
        1. Creation
        2. Modification
        3. Serial Number
        4. Value from Last Visited Record
        5. Data
        6. Calculated Value
        7. Looked-up Value
        8. Prohibit Modification
      4. Validation Tab (Checking Data for Accuracy)
        1. Validate Data in This Field7
        2. Strict Data Type
        3. Not Empty
        4. Unique Value
        5. Existing Value
        6. Member of Value List
        7. In Range
        8. Validated By Calculation
        9. Maximum Number of Characters
        10. Display Custom Message if Validation Fails
      5. Storage Tab (Global Storage, Repeating Fields, and Indexing)
        1. Global Storage
        2. Repeating Field Options
        3. FileMaker’s Indexing System
          1. Storage Options
          2. Field Indexing Pros and Cons
        4. Limitations
      6. Furigana Tab7
      7. Summary
    2. Chapter 11 Putting Your Data to Work for You
      1. Introduction
      2. Defining a Calculation Field
        1. Operators
          1. Mathematical Operators
          2. Comparison Operators
          3. Logical Operators
          4. Text Operators
        2. Other Options
          1. Storage Option
          2. Repeating Field
          3. Do Not Evaluate
          4. Limitations
      3. Summary
    3. Chapter 12 Real-World Calculations—Part 1
      1. Introduction
      2. FileMaker’s Help Files
      3. What Are Functions?
        1. Text Functions
          1. Left
          2. A Calculation within a Calculation
          3. LeftWords
          4. Length
          5. Position
        2. Building Complex Calculations
          1. The Steps
          2. Using the Process
          3. Make It Permanent
        3. Number Functions
          1. The Process
          2. A Calculation for FMP7 and Later
        4. Date and Time Functions
          1. Time Clock
      4. Commenting Calculations7
      5. Summary
    4. Chapter 13 Real-World Calculations—Part 2
      1. Introduction
      2. Aggregate Functions
        1. Aggregate Examples
      3. Summary Functions
        1. GetSummary Example
      4. Repeating Functions
        1. GetRepetition Example
      5. Financial Functions
        1. PMT Example
      6. Trigonometric Functions
        1. Pi Example
      7. Logical Functions
        1. If and Case
        2. IsEmpty
        3. Evaluate7
        4. Let7
        5. GetLayoutObjectAttribute8.5
        6. Self9
        7. GetFieldName10
      8. Get Functions7
        1. Get (RecordNumber) Example
        2. Get (TemporaryPath)9
        3. Get (DocumentsPathListing)10
        4. Get (HostApplicationVersion)9
      9. Design Functions
        1. FieldNames and FieldStyle Examples
      10. External Functions
      11. Text Formatting Functions7
      12. Timestamp Functions
      13. Custom Functions7
      14. Summary
  12. Part 4 Creating a Real Solution
    1. Chapter 14 Automating Your Database with Scripts
      1. Introduction
      2. What Are Scripts?
        1. One Step at a Time
        2. Script Options
      3. Planning Your Script
        1. Where Am I Now?
        2. What Do I Want to Do?
        3. Where Do I Want to Be When I Finish?
        4. What Can Go Wrong?
      4. Script Steps Overview
        1. Script Steps
        2. Control Category
      5. Main Menu Script Example
        1. Going to Contact
        2. Fail-safe
        3. Getting Back
        4. Testing
        5. Access the Main Menu Layout from Other Files
        6. Using Layouts from Other Files7
        7. The Separation Model
        8. Portable Portals7
      6. Print Invoices Example
        1. Else If7
        2. The Print Script
        3. The Invoices Scripts
          1. Figuring Interest
          2. Printing One Invoice
        4. Monthly Billing Script
          1. Testing Monthly Billing
        5. Thoughtful Additions
        6. Printing to PDF8
        7. Get (TemporaryPath)10 and Send Mail10
        8. Append to PDF9
        9. Script Variable8
        10. Match Found Set (Related Records)8
      7. Debugging Scripts
        1. Important Hints
      8. Import Scripts
        1. Button Options (Pause, Halt, Exit, Resume)
        2. Startup/Shutdown Scripts
        3. Show Custom Dialog Script Step
        4. Set Field By Name10
      9. Other Features
        1. New Window7
        2. Script Parameters7
        3. Accounts Script Category7
        4. External Data Sources9
      10. Script Organization Features9
        1. Script Folders9
        2. Filter Scripts9
        3. Copy and Paste Scripts9
        4. Copy and Paste Script Steps9
        5. Multiple Script Windows9
        6. FMP Advanced Features9
      11. And Finally. . .
      12. Summary
    2. Chapter 15 Making Sense of Your Information with Layouts
      1. Introduction
      2. What Is a Layout?
        1. Layout Types
          1. On-screen Layouts (Data Entry)
          2. Printed Layouts
      3. Layout Parts
        1. Title Header
        2. Header
        3. Body
        4. Sub-summaries (Leading and Trailing)
        5. Leading and Trailing Grand Summaries
        6. Footer
        7. Title Footer
      4. Working with Layout Parts
        1. Adding and Deleting Parts
        2. Modifying Parts
      5. Summary
    3. Chapter 16 Working with Script Triggers10
      1. Introduction
      2. Make a Script
      3. Object-Based Script Triggers
        1. OnObjectExit10
      4. Layout-Based Script Triggers
        1. OnRecordLoad10 and OnLayoutLoad10
        2. OnObjectKeystroke10 Example
        3. When to Use Auto-Enter Calculated Value
        4. OnObjectModify10 Example
      5. Script Trigger Notes
      6. Script Trigger Ideas
      7. Install OnTimer Script10
        1. Install OnTimer Script Details
        2. OnTimer Script Ideas
      8. Functions Related to Script Triggers
        1. Get (TriggerModifierKeys)
      9. A Case for FileMaker Pro Advanced
      10. Summary
    4. Chapter 17 Designing Your Screen Layouts
      1. Introduction
      2. Basic Design
        1. Know What You Like
        2. Keep It Consistent
        3. Group Formatting
          1. Selecting Groups of Layout Objects
        4. How to Make It Pretty
          1. Arrange and View Menus
          2. Rotate Layout Objects
          3. Icons
        5. Locking Objects on a Layout
        6. Adding Graphics and Movies to a Layout
          1. Formatting Graphics on a Layout
      3. Customizing the Appearance of Objects on a Layout
        1. Adding Borders, Baselines, and Fills to Fields
        2. Adding Object Effects
          1. Embossed
          2. Engraved
          3. Drop Shadow
          4. Changing Colors
      4. Tab Control8
      5. Object Names8.5
      6. Autoresize Layout Objects9
        1. Getting Autoresize to Work
        2. Autoresize Rules
      7. Tooltips8
      8. Conditional Formatting9
      9. Summary
    5. Chapter 18 Designing Your Printed Report Layouts
      1. Introduction
      2. What Is a Report?
        1. Creating a Useful and Attractive Report
        2. Report Types
      3. Creating a Report
        1. What Results Do You Want?
        2. What Data Do You Have (and Not Have)?
      4. Example Reports
        1. Customer Sales Report
          1. Setup—Making the Data Available
          2. Building the Report
          3. Touch-up Work
          4. How to Set Up a Find Layout
        2. Percent of Sales by Product Report
          1. Building the Report
          2. Touch-up Work
          3. How to Set Up This Find Layout
        3. Other Common Reports
        4. Avoiding the Today and Get (CurrentDate) Functions in Calculation Fields
        5. Organizational Details
      5. Summary
  13. Part 5 Sharing Your Database
    1. Chapter 19 Personal Data Sharing
      1. Introduction
      2. What Is Personal Data Sharing?
        1. Capabilities
        2. Network Requirements
      3. Using Personal File Sharing
        1. Using Files as a Guest
          1. Sharing Remotely by Way of Modem
          2. File Sharing Caution
        2. Limitations
          1. File and Guest Limits
          2. Global Fields and Shared Files
        3. Optimizing Performance
        4. Hardware Considerations
        5. FileMaker Server
        6. Some Final Words
          1. Closing Hosted Files
          2. Slow Network Traffic
        7. Save/Send Records as Excel8
        8. Export Field Contents/Fast Send8
        9. Email Link to Published Database9
      4. Summary
    2. Chapter 20 Sharing Your Data on the Web
      1. Introduction
      2. Why Publish on the Web?
      3. FileMaker and Instant Web Publishing (IWP)
        1. Setting Up Your Computer
          1. If You Have an Internet Account
          2. If You Don’t Have an Internet Account
        2. Setting Up FileMaker Pro for IWP
          1. Instant Web Publishing Settings
          2. Advanced Options
        3. Setting Up Your Database
          1. Browsers
          2. Layouts
        4. IWP Browser Interface
        5. Web Security
        6. IPv6 Support10
        7. Try It Out
        8. Other Notes and Options
      4. Cool Web Sites Powered by FileMaker
      5. Summary
    3. Chapter 21 Sharing Your Data with Other Applications
      1. Introduction
      2. Using FileMaker’s Import and Export Commands
      3. ODBC Sharing
        1. Making FileMaker Data Available to Other Applications
        2. Getting Data from Another Application
          1. Setting Up the Data Source (Macintosh OS X)
          2. Importing the Data (Macintosh)
          3. Setting Up the Data Source (Windows)
          4. Importing the Data (Windows)
        3. Saving the Import as a Script
      4. Execute SQL Script Step
      5. Converting and Importing Data from Microsoft Excel
      6. Importing a Folder of Files in a Single Operation
      7. Summary
  14. Part 6 Protecting Your Information
    1. Chapter 22 Keeping Your Data Secure
      1. Introduction
      2. Why Protect Your Data?
      3. FileMaker Security
      4. Accounts Tab7
        1. Edit Account7
          1. Guest Account7
          2. Admin Account7
          3. New Account7
          4. A Word about Passwords
          5. Privilege Set7
      5. Privilege Sets Tab7
        1. Edit Privilege Set7
          1. Data Access and Design
          2. Other Privileges7
          3. Extended Privileges7
      6. Extended Privileges Tab7
      7. Expand and/or Reduce Privileges
        1. Editing and Deleting Accounts7
        2. Editing
        3. Cautions
      8. Limiting Access through Scripting
        1. Startup Script
        2. Controlling Layout Access
      9. Limiting Access through Field Calculations
        1. The Script
        2. The Calculation
      10. Internet Considerations
      11. Conversion Issues
      12. Summary
    2. Chapter 23 Backup and Recovery
      1. Introduction
      2. Why Back Up?
        1. FileMaker Server Backup Features
        2. Using Commercial Backup Applications
        3. Problems with Tape
      3. Database Corruption
        1. What Is a Clone?
        2. Saving a Clone
      4. Backup Routine
        1. While You Work
        2. Other Related Measures
        3. FileMaker Techniques
        4. One-Record Table
        5. More about Exporting
      5. Apple’s Time Machine
      6. Using Your Backups
        1. Restoring
        2. Importing Data
          1. More about Importing
        3. Tying Up the Loose Ends
      7. Summary
  15. Part 7 Beyond FileMaker
    1. Chapter 24 FileMaker and XML
      1. Introduction
      2. What Is XML?
      3. Add in XSL
      4. What Can I Use It For?
      5. Examples: Exporting XML as HTML
        1. Using a Local XSL Style Sheet
        2. Creating the Export XML Script
        3. Using a Remote XSL Style Sheet
        4. And Beyond. . .
      6. Profile of a FileMaker XML Web Request
      7. Import XML
      8. Open XML
      9. Summary
    2. Chapter 25 FileMaker and the Internet
      1. Introduction
      2. Web Viewer8.5
      3. Creating a Web Viewer
      4. Web Viewer Economy
        1. Layout Work
        2. Importing Some Data
        3. Working with the Radio Buttons
      5. Working with the Web Viewer
        1. Getting the URL
        2. Mini Web Viewer
        3. Automatically Filling Out Forms—Not
        4. Which Way Did He Go?
        5. Back and Ahead Buttons
        6. Screen Scraping
        7. Saving the URL
      6. The Example File
      7. The Web Viewer and Other Applications
      8. Cleaning Up
      9. Rules
      10. Suggested Uses
      11. Data URLs9
      12. Summary
    3. Chapter 26 Working with External Data Sources
      1. Introduction
      2. External SQL Sources (ESS)9
      3. Selecting the Data Source
      4. Using ODBC Tables in the Graph
      5. Shadow Tables
      6. ODBC Tables in Browse Mode
      7. ESS-based Value Lists10
      8. Other Stuff You Need to Know
      9. Getting Your Data from Bento10 (Macintosh Only)
      10. Summary
    4. Chapter 27 File Conversion Issues and Answers
      1. Introduction
      2. Documentation
      3. Step By Step
        1. Conversion Log
        2. Comparing Fields
        3. Comparing Layouts
        4. Accounts & Privileges
        5. Comparing Scripts
        6. Comparing Relationships
        7. External Data Sources
        8. More on Accounts and Passwords
        9. Finishing Up
      4. Summary
  16. Appendix A Leftover, but Important Stuff
  17. Appendix B Getting More Help
  18. Index