FileMaker® Pro Design & Scripting For Dummies®

 

by Timothy Trimble

 

 

 

About the Author

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.

 

Dedication

To the WhoWeAre Band. Two CDs, the House of Blues, and a wonderful, nine-year ride that I’ll never forget!

 

Author’s Acknowledgments

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.

 

Publisher’s Acknowledgments

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

Contents

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

Print

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

Introduction

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!

About This Book

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.

Foolish Assumptions

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.

bullet 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.

bullet 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).

bullet 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.

How This Book Is Organized

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.

Part I: Getting to Know FileMaker Pro

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: Building the Perfect Beast

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.

Part III: Taking Control with FileMaker Programming

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.

Part IV: FileMaker Exposed! Sharing and Protecting Your Database

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).

Part V: The Part of Tens

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.

Appendix: Scripting Reference

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.

Icons Used in This Book

Tip

The Tip icon draws your attention to tips on how to use FileMaker to its full potential.

Remember

Remember when you used to tie string around your finger to help you remember something? Well, now we all use PDAs and smartphones. Anyway, this icon indicates something that you should remember when using FileMaker.

Warning(bomb)

The Warning icon alerts you to very important information that you should be aware of. Ignoring this information could lead to very undesirable results.

TechnicalStuff

If you’re feeling geeky or would like to have a little more technical knowledge about FileMaker, the computer industry, terminology, or just geeky stuff in general, feel free to read the items marked with this icon at your leisure.

Downloads for This Book

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:

bullet www.timothytrimble.info

bullet www.dummies.com/go/filemakerprodesign

Where to Go from Here

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!

Part I

Getting to Know FileMaker Pro

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.