FileMaker® Pro Design & Scripting For Dummies®
Published by
Wiley Publishing, Inc.
111 River St.
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc., and/or its affiliates in the United States and other countries, and may not be used without written permission. FileMaker is a registered trademark of FileMaker, Inc. in the United States and other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.
Library of Congress Control Number: 2005939205
ISBN-13: 978-0-471-78648-1
ISBN-10: 0-471-78648-9
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1O/RW/QW/QW/IN
Timothy Trimble is a professional computer geek, writer, and software developer with over 25 years of industry experience. He started as a video game developer and worked his way into the PC and PDA software development markets on various commercial, corporate, and vertical market applications. He currently exhibits his geekish tendencies as a FileMaker developer at SolutionMakers, Inc. (www.solutionmakers.com) in Woodinville, WA.
Timothy has written a multitude of articles for various industry publications and wrote a book on flight simulation. He maintains a blog on the Art of Software Development at www.timothytrimble.info. Although he has significant publishing credits in the computer industry, he dreams of some day having his science fiction work published as well.
When Timothy is not cranking away at the keyboard, he likes to be with his family, entertain his friends by singing and playing guitar, and door-to-door to tell others about his faith. Feel free to contact Timothy with your comments on this book by e-mailing him at fmp4d@timothytrimble.info.
To the WhoWeAre Band. Two CDs, the House of Blues, and a wonderful, nine-year ride that I’ll never forget!
First of all, my loving wife and soul-mate, Denise. You continue to love and support me on this adventure of life. And to the pride and joy of my life — my boys Alex, Andrew, and Sam — proof that the nuts don’t fall far from the tree, though they hate to admit it. I love you all dearly.
A special thanks goes to Dennis Cohen for his pulling up the tail end of the edits on the book during crunch time and to the staff at Wiley Publishing: Tiffany Franklin for nudging me when I needed nudging; Rebecca Huehls for putting up with my strange writing style while she taught me For Dummies style; Nancy Stevenson for helping me through my first chapter; and of course the unsung heroes at Wiley who handle the layout, printing, and awesome cartoons.
To my buddy, spiritual brother, fellow musician, and strangely enough — my technical editor, Mark Hammer. You’re one of the reasons that I decided to take such a serious look at FileMaker. So, how big is the stage now?
A special thanks to Marco and John, for giving me the initiative to dive back into the FileMaker world.
A very special thanks goes to my agent at Waterside Productions, Carole McClendon. Not only am I impressed that such a prominent agent is willing to handle my stuff, but she has a gift for turning my proposals into real work. Thank you, Carole!
A heartfelt and warm thanks and appreciation to the Flowers family for helping my family and me through a very challenging period in our life. Because of you, this book could be written.
No acknowledgments would be complete without a special thanks for the things that got me through this book: Red Bull and Monster for getting me through the late nights of writing; Eve-online and my fellow pilots in Codito Ergo Sum for providing the brief moments of computer recreation when the diversion was needed; and iTunes for the constant flow of music while madly typing away.
Of course a thank you goes to the FileMaker, Inc., organization for making FileMaker Pro the product that it is today, and for promoting what is truly an Art in Software Development.
Lastly and most significantly, thanks to Jehovah for blessing me with the gift of words and the ability to grasp technology, and for allowing me to know all the people I mentioned above.
We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: Rebecca Huehls
Acquisitions Editor: Tiffany Ma
Copy Editor: Heidi Unger
Technical Editor: Mark Hammer
Editorial Manager: Leah P. Cameron
Media Development Specialists: Angela Denny, Kate Jenkins, Steven Kudirka, Kit Malone
Media Development Coordinator: Laura Atkinson
Media Project Supervisor: Laura Moss
Media Development Manager: Laura VanWinkle
Editorial Assistant: Amanda Foxworth
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinators: Tera Knapp, Adrienne Martinez
Layout and Graphics: Andrea Dahl, Denny Hager, Barbara Moore, Barry Offringa, Lynsey Osborn, Alicia B. South
Proofreaders: Leeann Harney, Dwight Ramsey, Techbooks
Indexer: Techbooks
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Title
Introduction
About This Book
Foolish Assumptions
How This Book Is Organized
Icons Used in This Book
Downloads for This Book
Where to Go from Here
Part I : Getting to Know FileMaker Pro
Chapter 1: Introducing FileMaker Pro
Why Use FileMaker?
Picking the Version That’s Best for You
The Happy Modes of FileMaker
What’s on the Menu?
The Right Toolbar for the Job
Configuring Your FileMaker Installation
Chapter 2: Creating a Database
Starting a Custom Database from Scratch
Adding and Renaming a Table
Playing the Field: Adding Fields to the Table
Connecting Tables with Parent/Child Relationships
Chapter 3: Calculation Programming: You Have the Power!
Introducing the Calculation Editor
Deciding Where to Put a Calculation
Getting Crazy with the Calculation Functions
Part II : Building the Perfect Beast
Chapter 4: Designing a Good FileMaker Application
Analysis Isn’t Just for Doctors
Data In: Creating a Well-Designed Database
Data Out: Designing an Effective Layout
Chapter 5: Whipping the Layout into Shape
Aligning Objects the Easy Way
Inserting the Current Date on the Layout
Simplifying Life with the Format Painter
Adding Pretty Pictures to the Layout
Make Those Fields Behave
I Need a Tooltip!
Organizing the Layout into Tabs
It’s Mini-Me! — Adding Subtables
Adding a Grand Summary
Chapter 6: Finding and Sorting Your Data
Searching for Data
Putting Your Jobs in Order (By Sorting)
Chapter 7: Making FileMaker Do Tricks
Buttoning Up the Layout
Putting a Data Field to Work
Using Those Wonderful Drop-Down Lists
Popping Up Menus
Presenting Choices with Check Boxes and Radio Buttons
Storing Pictures, Documents, Sounds, and More in a Container Field
Customizing the Menus
Part III : Taking Control with FileMaker Programming
Chapter 8: It’s All in the Script!
Saving Time with FileMaker Scripts
Checking Out the Parts in a Script
Creating a Script in ScriptMaker
Playing with Animation: The ShipMover Database
Chapter 9: Your Programming Toolbox
Good Commenting Practices
Auto-Centering a Layout
Using External Scripts and Value Lists
Calling Up an Address with Google Maps
Automated Error E-Mails
Part IV : FileMaker Exposed! Sharing and Protecting Your Database
Chapter 10: Share (Data) and Share Alike
Sharing a Database over a Network
Exporting Your Data to Other Formats
Importing Data from Other Sources
Linking to Other FileMaker Databases
Sharing Data with Another Database System via ODBC/JDBC
Generating the Ultimate Database Report
Chapter 11: Batten Down the Hatches! Keeping Your Data Safe
Introducing FileMaker Security Methods
Defining User Accounts
Using Privilege Sets
Managing Extended Privileges
FileMaker Security Guidelines
Chapter 12: Putting Your Databases on the Web
Designing Layouts for the Web
Script Writing for the Web
Putting Your Database on the Web
Part V : The Part of Tens
Chapter 13: Ten Cool Things You Can Do with FileMaker
Making Hidden Buttons
Giving Your Layouts Pizazz with Graphics
Making Layout Titles and Text Dynamic
Reusable Layouts
Showing Movies in FileMaker
Using Auto Start & End Script Code
Sending E-Mail from FileMaker
Launching a Web Site with FileMaker
Changing a Field Background Color
Running Another Application
Chapter 14: Ten (Or So) Items to Aid Your FileMaker Development
Checking Out Online Resources for FileMaker Pro
Plugging into FileMaker Pro
Expanding Your Themes
Not Just Scripting, but AppleScripting
Appendix: Scripting Reference
Add Account
Adjust Window
Allow Toolbars
Allow User Abort
Arrange All Windows
Beep
Change Password
Check Found Set
Check Record
Check Selection
Clear
Close File
Close Window
Comment
Commit Records/Requests
Constrain Found Set
Convert File
Copy All Records/Requests
Copy Record/Request
Copy
Correct Word
Cut
Delete Account
Delete All Records
Delete Portal Row
Delete Record/Request
Dial Phone
Duplicate Record/Request
Edit User Dictionary
Else If
Else
Enable Account
End If
End Loop
Enter Browse Mode
Enter Find Mode
Enter Preview Mode
Execute SQL
Exit Application
Exit Loop If
Exit Script
Export Field Contents
Export Records
Extend Found Set
Flush Cache to Disk
Freeze Window
Go to Field
Go to Layout
Go to Next Field
Go to Portal Row
Go to Previous Field
Go to Record/Request/Page
Go to Related Record
Halt Script
If
Import Records
Insert Calculated Result
Insert Current Date
Insert Current Time
Insert Current User Name
Insert File
Insert From Index
Insert From Last Visited
Insert Object (Windows)
Insert Picture
Insert QuickTime
Insert Text
Install Menu Set
Loop
Modify Last Find
Move/Resize Window
New File
New Record/Request
New Window
Omit Multiple Records
Omit Record
Open Define Database
Open Define File References
Open Define Value Lists
Open File Options
Open File
Open Find/Replace
Open Help
Open Preferences
Open Record/Request
Open Remote
Open ScriptMaker
Open Sharing
Open URL
Paste
Pause/Resume Script
Perform AppleScript (Mac OS)
Perform Find
Perform Find/Replace
Perform Script
Print Setup
Recover File
Refresh Window
Re-Login
Relookup Field Contents
Replace Field Contents
Reset Account Password
Revert Record/Request
Save a Copy as
Save Records As Excel
Save Records As PDF
Scroll Window
Select All
Select Dictionaries
Select Window
Send DDE Execute (Windows)
Send Event (Mac OS)
Send Event (Windows)
Send Mail
Set Error Capture
Set Field
Set Multi-User
Set Next Serial Value
Set Selection
Set Use System Formats
Set Variable
Set Window Title
Set Zoom Level
Show All Records
Show Custom Dialog
Show Omitted Only
Show/Hide Status Area
Show/Hide Text Ruler
Sort Records
Speak (Mac OS)
Spelling Options
Undo
Unsort Records
Update Link (Windows)
View As
With over 10 million units of FileMaker software sold around the world, FileMaker has become the diamond in the rough when it comes to creating and managing data. After over 20 years of use, today’s FileMaker Pro 8 has matured from its humble beginnings to become the hottest cross-platform database-development environment available.
Though I hate to admit it, when I first took a look at FileMaker in the early ’90s, I wasn’t impressed. The database tables were flat (no relationships), and aside from being able to quickly throw together some forms, I couldn’t see how or why this application had any appeal. Needless to say, I was blinded by my experiences with dBASE and Superbase. Yes, I was being narrow minded. I took another look with version 5.0, and though I was impressed with the improvements to the database structure and the ability to write programming scripts, I still felt that it was underpowered as a software development tool.
Then FileMaker 7 came out, and I was blown away! My socks rolled up and down! I was impressed with the ability of FileMaker to be utilized as a full-blown application development tool — not only for Mac OS X but Windows as well. Now with the release of version 8 (the current version as I write this book), the FileMaker folks have provided a serious, relational database design and development system. After being in the computer industry since 1982 (yeah, I’m showing my age), I am finally excited about a programming environment again. By the time you get through this book, you’ll be excited too!
I wrote FileMaker Pro Design & Scripting For Dummies for all types of FileMaker users. Whether you’re just starting out in developing applications or have experience in database design but are new to FileMaker, this book can help you get custom applications up and running.
You can select individual topics from the book to aid in gaining a deeper understanding of the power of FileMaker, or you can start at the beginning and cover each chapter in sequence. How you use this book is up to you, but regardless of your current FileMaker experience, I hope that you find out something new about this incredible product.
Because FileMaker is a cross-platform tool, this is a cross-platform book. Whether you work on a Mac or a PC, you find the pointers you need.
This is where I usually get into trouble — especially with my wife, when I assume that she knows what I’m thinking or when I assume that I know what she’s thinking. Fortunately, with this book, I can make only some basic assumptions about you as the reader.
First of all, to get to this spot, I’m assuming that you have a computer (either a Mac or a PC), you know how to use it, and that FileMaker Pro is already installed on it.
To keep the focus on design and scripting, I also assume that you’re fairly comfortable with the basics of using FileMaker. If you need more general information about working with FileMaker, check out FileMaker Pro 8 Bible by Dennis R. Cohen and Steven Schwartz (Wiley Publishing).
I don’t assume that you have any programming knowledge, or that you have done any database development before. It should be easy for you to grasp an understanding of these topics by reading this book.
This book is organized in a manner that allows you to quickly find what you’re looking for. While a complete understanding of FileMaker Pro design and scripting could fill several books with thousands of pages, my intent with this book is to give you enough information to make you productive as a beginner.
To kick off Part I, Chapter 1 introduces why FileMaker is such a great tool for application development, helps you choose the right version, offers tips for setting up FileMaker, and gives you a quick tour of the interface. In Chapter 2, I explain how you begin building your database, and Chapter 3 helps you tap into the power of scripts.
Part II begins with one of my favorite chapters of the book, Chapter 4, which guides you through the process of designing a good FileMaker application. Here, you discover the fundamentals of database design, layout design, and what makes a good software program.
The remaining chapters in this part help you fine-tune your creation, and a sample application called Hey, Look at Me! is provided as a workbench. Chapter 5 explains how to make your database layouts look good while adding more functionality to your database application. Chapter 6 offers tips and tricks for adding searching and sorting capabilities to your application. And in Chapter 7, you discover how to assign functions to objects on the layout, use lists and menus, and more.
This is where you get to be geeky! Chapter 8 digs deep into the logical processing power of the Calculation Editor so that you can enhance your programs. And you’ll get a nice, dice-rolling application out of it too! Then in Chapter 9, I share the programming tools I’ve put together over the years so you can start creating a toolbox of templates and organization tricks all your own.
This very important part of the book deals with sharing your data — making sure you share it only with the people who should have access to it. In Chapter 10 you find out how to move data in and out of your database, by importing data from common sources, such as Excel or comma-delimited files, or exporting it to other formats when you need to. This chapter also explains how to make your FileMaker database available for others on a network. And because others will use your database, you find out about security and how to create and manage user accounts and Privilege Sets in Chapter 11. Then no discussion of sharing data would be complete without a chapter on how to put your database on the World Wide Web (Chapter 12).
In Chapter 13, find out how to apply your newfound knowledge to the amazing capabilities of FileMaker. You find ten cool things you can do with FileMaker, beginning with hiding buttons and including discovering how to launch another application on your computer. Chapter 14 wraps up this part with an introduction to great add-ons that help you extend FileMaker’s capabilities.
Be prepared to come back to this part of the book on a regular basis. This section is a reference for the FileMaker Pro scripting language. Not only do I show you the syntax and options of various script steps, but I also give some examples on how to use those functions in your FileMaker programs.
.jpg)
In many places throughout this book you’re directed to files that you can download as examples and workbenches for steps in the book. These locations are:
www.timothytrimble.info
www.dummies.com/go/filemakerprodesign
If you’re an experienced FileMaker user, you might want to just skim the table of contents and determine what’s of interest to you. You might be amazed to discover that there are some areas of FileMaker that you haven’t found out how to utilize yet. However, if you’re just getting started with FileMaker, dive into Part I, follow each chapter in sequence, and be prepared to have your socks knocked off!
In this part . . .
You take the first steps toward building your own application in this part. Chapter 1 introduces application design the FileMaker way by explaining what you can do and what version you need to do it, and taking you on a crash course of the interface from a developer’s perspective. Chapter 2 shows you how to create the foundation of your application — the database that will hold the data. And Chapter 3 gets you started with building calculations in the Calculation Editor and adding them to your database. If you’re new to development, this may seem like a huge undertaking, but FileMaker (and these chapters) make it easier than you’d think.