Introduction
For many people, the word “database” conjures up an image of a computer, and a vast collection of information you can only access via a computer screen. But actually, databases are all around you—a phone book, a cookbook, and an encyclopedia are all databases. So is the stock page in your newspaper. In fact, if you look up the word “database” in a dictionary (which is a database, too) you’ll probably read that a database is just a collection of information, or data.
Ideally, a database’s information is organized so that you can easily find what you’re looking for. For example, a Rolodex has information about people organized alphabetically, so you can find any person’s card pretty quickly because you know approximately where it is, even though there may be hundreds of cards to look through. But physical databases like this example all have major limitations compared to those that are stored on a computer. What if you want to get a list of all your associates in California? A Rolodex isn’t organized that way, so you have to look through every card one by one. That kind of tedium is one of the reasons so many Rolodexes are now at the bottom of landfills, and it’s one of the biggest problems a computer database program like FileMaker Pro can help you avoid.
The term database program means a computer program designed to help you build a database. Lots of computer programs use a database in one way or another—for example, your email program tracks hundreds of email messages you’ve sent and received, but it isn’t a database program. A database program lets you build your own database and customize it to meet your specific needs.
A database stored on a computer isn’t much different in theory than one painstakingly collected on Rolodex cards or other forms of paper. It contains lots of information, like addresses, zip codes, and phone numbers, and organizes that info in useful ways (see Figure I-1 for an example). But because it’s stored on a computer, you can organize the same information in numerous ways with ease—say, by name or by state. Computers also make searching databases a whole lot faster. In fact, a computer can often look through almost any amount of data in less than a second, which is a lot quicker than flipping through all your Rolodex cards to find everyone who lives in Kansas.
This book will teach you how FileMaker stores your information and how you can rearrange that information and get the answers to meaningful questions like which employees are due for performance reviews, who’s coming to the company picnic and which amusement park has the best deal on Laser Tag for your top 50 performers. You won’t have to learn to think like a programmer (or know the arcane terms they use), but you will learn how to bend FileMaker Pro’s hidden power to your will and make it tell you everything it knows about your company, your stock portfolio or how much you’ve spent on the replacement parts for the ’58 Bonneville Sport Coupe you’re rebuilding.
Why FileMaker Pro?
If you’re reading this book, you’ve already decided to use a computer databases instead of the mulched-up tree variety. Choosing a database program from the many options on the market is overwhelming. Some are enormously powerful, but take weeks to learn how to use. Others are easy to get started with, but don’t offer much help when you’re ready to incorporate some more advanced features. Here are a few reasons why FileMaker Pro is a great choice for most folks.
FileMaker Pro is the ease-of-use champion. While other programs use funny words like query, join, and alias, FileMaker Pro uses simple concepts like find, sort, and connect. FileMaker is designed from the ground up for non-technical people who have a real job to do. It’s designed to let you get in, get your database built, and get back to work.
FileMaker Pro can do almost anything. Some other database programs are simple to use, but they’re just plain simple, too—they can’t do the kinds of things most businesses need to do. FileMaker Pro, despite its focus on ease of use, is very powerful. It can handle large amounts of data. It lets lots of people on different computers share data (even at different locations around the world). It even meets the needs of bigger companies, like integrating with high-end systems. And it’s adaptable enough to solve most problems. If your home-based crafting business is taking off, and you need to figure out how much it costs you to create your top-selling items, FileMaker can do that. But if you’re a large school district tracking dozens of test scores for more than 50,000 students in grades K-12 and you have to make sure those scores are tied to federal standards, well, FileMaker can handle that, too.
FileMaker Pro works on Macs or PCs. If you or your company use both types of computers, FileMaker Pro makes the connection seamless. The exact same databases can be used on any computer, and even shared simultaneously without a hitch (see Section 17.5.2).
FileMaker Pro is fun! It may sound corny, but it’s exciting (and a little addictive) to have such a powerful tool at your fingertips. If you get the bug, you’ll find yourself solving all kinds of problems you never knew you had. You might not think that getting married is an occasion for breaking out a new database, but you’ll be amazed at how helpful it is. You can make a mailing list for your invitations, track RSVPs, note which favorite aunt sent you a whole set of bone china (and which cheap cousin cheaped out by signing his name on his brother’s gift card), and you can record what date you mailed the thank-you notes.
Everybody uses it. Perhaps best of all, FileMaker Pro is very popular—more people buy FileMaker Pro than any other database program. And the program’s fans love it so much they’re actually willing to help you if you get stuck. You can find user groups, Web sites, discussion boards, chat rooms, mailing lists, and professional consultants all devoted to FileMaker Pro. This is one case where there’s good reason to follow the crowd.
What’s New in FileMaker Pro 8
FileMaker Pro is a single software package that serves two fundamentally different types of people: users and designers. Users are the folks who need a database to help them organize and manage the data they work with in order to do their jobs. Designers create the databases that users use. No matter which category you’re in (and lots of people fall into both categories, sometimes popping back and forth dozens of times a day), you’ll find that FileMaker doesn’t play favorites. The features you need for both roles are equally accessible.
Day-to-day users appreciate features like the following, because they address the most frequently needed tasks:
If your boss down the hall needs a report, but he doesn’t use FileMaker, you can still send him the data he needs. Just create an Excel file from inside FileMaker with the Save Records as Excel command.
But what if your boss doesn’t even use Excel? No problem. Export and email the contents of any field–text, numbers, graphics, or even electronic files–without leaving FileMaker.
Maybe you don’t want to give people the actual data you’ve painstakingly gathered, but you do want them to be able to see it. You can make PDF files right from the menu to pass around the office or post on your Web site. No more fussing around with Adobe Acrobat or arcane print settings.
When the sales data indicate that you’ve had a record month and you want to congratulate the entire marketing department, you could do that in older versions of FileMaker–if you knew how to write scripts. The new email merge feature lets you send batches of email with a few, simple commands.
If you’ve got a warehouse full of paper products that have to be moved, you can find a list of all customers who’ve bought that product without even typing. Just find the first customer who bought item #456-234-WJ43. Highlight the item number, and then select Find Matching Records. Voila! You’ve got a list of customers without typing a single keystroke.
Auto-complete settings work like type ahead in word processing programs. This setting can make data entry faster and more accurate.
FileMaker’s finicky about how you type a date. If you get the wrong sequence of numbers and slashes, the program complains until you get it right. Now, you can attach calendar pop-ups to any field. Just click the date you need and FileMaker pops it into the field without grouching at you.
Visual spell checker highlights suspect words as you type, just like your favorite word processing program. You can correct your spelling as you speed type merrily along.
Some of the features listed above aren’t completely new, as designers can tell you. They know, because in previous versions, designers spent lots of time making these tasks easy for their users. But now that FileMaker has built-in ways to send data as email or PDFs, designers can spend more time making other tasks easier and more reliable. Below are some new features that help you create databases (or improve the ones you already have):
Layout alignment tools help you arrange fields so they line up and fit together better onscreen and for print. What would you do if somebody could give you back all the time you used to spend nudging and tweaking fields so they looked perfect?
If you’re combining data from other files, which is a major process if you’re updating FileMaker files from versions prior to FileMaker Pro 7, the new Table Import feature is a welcome time and sanity saver. You can import data without defining tables and fields first. FileMaker creates the table for you before it brings your new data into your database.
More than 20 new functions make your databases more powerful and efficient. If you’re an old hand at writing calculations or creating complicated scripted processes, you’ll love the power at your fingertips. If you’re just starting to learn how to make calculations and scripts, you’ll be out of the box faster.
Scripting variables make designing scripts easier and more efficient because you don’t have to create a bunch of extra fields to store data that’s needed only for certain behind-the-scenes processes.
Find out what happened to your data while you were running a script with the new option to attach a calculation to the Exit Script step.
A powerful new Go To Related Records option lets you find all the records that match all the records you’re already looking at. Say you’re looking at a group of students and you need a list of all the students who have a class with any kid in your current list. In the old days, it was easy to find all classmates for a single student, but it wasn’t so easy to get a list of more than one student’s classmates at the same time. Now, you can do it with one script step.
Tip
Be sure you’re using the latest version of FileMaker. Check for updates at www.filemaker.com/support/downloads/index.html
The Very Basics
You’ll find very little jargon or nerd terminology in this book. You will, however, encounter a few terms and concepts that you’ll see frequently in your computer life. They include:
Clicking. This book offers three kinds of instructions that require you to use the mouse or trackpad attached to your computer. To click means to point the arrow cursor at something onscreen and then—without moving the cursor at all—press and release the clicker button on the mouse (or laptop trackpad). Right-clicking works the same as clicking, but you use the right mouse button instead. (If you use a Mac and don’t have a right mouse button, press the Control key as you click.) To double-click, of course, means to click twice in rapid succession, again without moving the cursor at all. And to drag means to move the cursor while keeping the button continuously pressed.
When you’re told to Ctrl-click something, you click while pressing the Ctrl key (the Control key on the Mac) on the bottom row of your keyboard. Such related procedures as Shift-clicking and Alt-clicking work the same way—just click while pressing the corresponding key.
Note
On the Mac, the key that does most of the Alt key functions is called the Option key. Macs also have an extra key called the Command key, which has a cloverleaf (⌘) on it. When the Mac keystroke is different than the one in Windows, this book gives it in parentheses (see Section 3.4.2).
Menus are the lists of commands that you pull down from the words at the top of the FileMaker window. (On the Mac, they’re always in the bar across the top of the screen.) There are two equally valid ways to choose from these pull-down menus with your mouse: Click once to open the menu and then click again to choose a command, or click and hold the button as you drag down the menu and release when you get to the desired command. Use whichever method you find easier.
Keyboard shortcuts. Every time you take your hand off the keyboard to move the mouse, you lose time and potentially disrupt your creative floow. That’s why many experienced computer jockeys use keystroke combinations instead of menu commands wherever possible. Ctrl-P (⌘-P) opens the Print dialog box, for example.
When you see a shortcut like Ctrl-Q (⌘-Q), which closes the current program, it’s telling you to hold down the Ctrl (⌘) key, and, while it’s down, type the letter Q, and then release both keys.
If you’ve mastered this much information, you have all the technical background you need to enjoy FileMaker Pro 8: The Missing Manual.
About This Book
FileMaker Pro comes with a printed manual, and an impressive online help system. These are actually pretty helpful resources…if you’re a programmer, that is, or if you’ve been working with FileMaker for a while. Between the manual and the help file, you can figure out how FileMaker works. But you’ll have to jump back and forth between page and screen to get the complete picture. And neither source does a great job of letting you know which features apply to the problem you’re trying to solve.
This book is designed to serve as the FileMaker Pro manual, the book that should have been in the box. It explores each feature in depth, offers shortcuts and workarounds, and explains the ramifications of options that the manual doesn’t even mention. Plus, it lets you know which features are really useful and which ones you should worry about only in very limited circumstances. Try putting sticky tabs in your help file or marking the good parts with a highlighter!
FileMaker comes in several flavors, and this book addresses them all. FileMaker Pro, the base program, takes up most of the book’s focus. FileMaker Pro Advanced is an enhanced version of the program. Like the name promises, it contains advanced tools and utilities aimed at making development and maintenance of your databases easier. Its features are covered in Chapter 12 and 19. FileMaker Server lets you share your databases more safely and quickly than FileMaker Pro’s peer-to-peer sharing. Learn about Server in Chapter 18.
About the Outline
FileMaker Pro 8: The Missing Manual is divided into seven parts.
Part 1: Introduction to FileMaker Pro. Here, you’ll learn about FileMaker Pro’s interface and how you perform basic tasks, like entering data and then sorting through it again. You’ll also find out how FileMaker Pro stores your data inside fields and then organizes those fields into units called records. You’ll see how to define fields and make them do some of the data entry work for you.
Part 2: Layouts. Just as your actual data is organized into fields and records, the appearance of your database is organized into layouts. FileMaker Pro provides a whole raft of tools that make creating layouts fast and powerful. You’ll find out how to use layouts to make data entry easier and how to create layouts that list and summarize your data.
Part 3: Multiple Tables and Relationships. When you’re storing lots of different types of data in a database, it’s helpful to organize types of data using FileMaker’s tables. And when you have more than one table, you need to figure out how the information in one table relates to the information in another. You’ll learn how to create, connect, and manage multiple tables and how to set up complex relationships that show you just the data you need to see.
Part 4: Calculations. Most databases store lots of numbers, but the most important information in your database comes from performing some kind of math on those numbers. You’ll learn how to use FileMaker Pro’s 200+ functions to do the math for you. Surprisingly, you can also use functions on fields that don’t contain numbers. For example there are functions for working with text, dates, and times. And some of the most powerful functions are the logical functions, which can perform tests on your data, and then give you results based on what the function finds out.
Part 5: Scripting. Because there are so many things you have to do with a database that are repetitive, tedious, or just plan boring, FileMaker Pro provides a way for you to automate those tasks using scripts. You’ll start with simple steps that teach you how scripting works, and learn how to make scripts for people to use. You’ll see how to use scripts to work with fields and records, or with windows, or even with entire database files. And you’ll explore more complex techniques, like making scripts pass information to one another and nesting scripts within other scripts.
Part 6: Security and Integration. FileMaker knows your data’s important enough to keep it safe from prying eyes. In this section, you’ll learn how to protect your database with password and how to use privileges to determine what folks can do once they get into your database. This part also teaches you how to move data into and out of your database and how to share that data with other people. In the final chapter, you’ll see the design and development tools provided in FileMaker Advanced to speed creation time and give you more control over how your database works.
Part 7: Appendixes. No book can include all the information you’ll need for the rest of your FileMaker career. Well, it could, but you wouldn’t be able to lift it. Eventually, you’ll need to seek extra troubleshooting help or consult the program’s online documentation. So at the end of the book, Appendix A explains how to find your way around FileMaker’s built-in help files and Web site. It also covers the vast online community of fans and experts: People are the best resource of all for fresh ideas and creative solutions. Appendix B lists FileMaker error codes.
Living Examples
Each chapter contains living examples—step-by-step tutorials that help you learn how to build a database by actually doing it. If you take the time to work through these examples at the computer, you’ll discover that these tutorials give you invaluable insight into the way professional developers create databases. To help you along, online database files provide sample data for you to work with and completed examples against which to check your work.
You can get these files any time from the “Missing CD” page at www.missingmanuals.com (Section 3.4.5). To download, simply click this book’s title, and then click the link for the relevant chapter.
Macintosh and Windows
FileMaker Pro works almost precisely the same in its Macintosh and Windows versions. Every button in every dialog box is exactly the same; the software response to every command is identical. In this book, the illustrations get even-handed treatment, alternating between Windows XP and Mac OS X.
One of the biggest differences between the Mac and Windows versions is the keystrokes, because the Ctrl key in Windows is the equivalent of the Macintosh ⌘ key.
Whenever this book refers to a key combination, you’ll see the Windows keystroke listed first (with + symbols, as is customary in Windows documentation); the Macintosh keystroke follows in parentheses (with - symbols, in time-honored Mac fashion). In other words, you might read, “The keyboard shortcut for saving a file is Ctrl+S (⌘-S).”
About → These → Arrows
Throughout this book, and throughout the Missing Manual series, you’ll find sentences like this one: “Open your Home → Library → Preferences folder.” That’s shorthand for a much longer instruction that directs you to open three nested folders in sequence, like this: “In the Finder, choose Go → Home. In your Home folder, you’ll find a folder called Library. Open that. Inside the Library window is a folder called Preferences. Double-click to open it, too.”
Similarly, this kind of arrow shorthand helps to simplify the business of choosing commands in menus, as shown in Figure I-2.
About MissingManuals.com
At www.missingmanuals.com, you’ll find news, articles, and updates to the books in this series. Click the “Missing CD-ROM” link to reveal a chapter-by-chapter list of the databases referred to in the book.
If you click the name of this book and then the Errata link, you’ll find a unique resource: a list of corrections and updates that have been made in successive printings of this book. You can mark important corrections right into your own copy of the book, if you like.
In fact, the same page offers an invitation for you to submit such corrections and updates yourself. In an effort to keep the book as up-to-date and accurate as possible, each time we print more copies of this book, we’ll make any confirmed corrections you’ve suggested. Thanks in advance for reporting any glitches you find!
In the meantime, we’d love to hear your suggestions for new books in the Missing Manual line. There’s a place for that on the Web site, too, as well as a place to sign up for free email notification of new titles in the series.
Safari® Enabled
When you see a Safari® Enabled icon on the cover of your favorite technology book, that means it’s available online through the O’Reilly Network Safari Bookshelf.
Safari offers a solution that’s better than e-books: it’s a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.
Get FileMaker Pro 8: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
