For many people, the word “database” conjures up the idea of a vast collection of information that requires a computer and a technical degree to access. But databases are all around you—a phone book, a cookbook, and an encyclopedia are each 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, the information in a database is organized so you can find what you’re looking for quickly and easily. For example, a Rolodex has information about people organized alphabetically by name. You can find any person’s card because you know approximately where it is, even though there may be thousands of cards to look through. But physical databases have major limitations compared to those stored on a computer. What if you want to get a list of all your associates in California? A Rolodex isn’t organized by state, so you have to flip through every card one by one to get a list. The first database programs were created to help you avoid that kind of tedium. And FileMaker Pro is an easy-to-use, but powerful, database program.
The term database program means a computer program designed to help you build a database so you can store information you need. A database stored on a computer isn’t much different in theory from one 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 1 for an example). But since it’s stored on a computer, you can organize the same information in numerous ways with ease—say, by name or by state. Computers make searching databases a whole lot faster. That list of associates in California you took hours to generate from a Rolodex? A computer can do it in less than a second.
Figure 1. FileMaker Pro lets you do just about anything with the information you give it. You can use it like a Rolodex to simply store and retrieve information, or run your entire business with this one program. FileMaker’s built-in number crunching and word processing tools let you track people, processes, and things, creating all your reports, correspondence, and collateral documents along the way. Here are two examples of real-world FileMaker databases created for very different kinds of businesses.
This book will teach you how FileMaker Pro stores your information, and how you can rearrange that information to 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 so you can throw a party 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.
If you’re reading this book, you’ve already decided to use a computer database 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 years to learn how to use. Others let you easily get started, 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 jargon words like query, join, and alias, FileMaker Pro uses simple concepts like find, sort, and connect. FileMaker Pro 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, build your database, 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, then FileMaker can handle that, too.
FileMaker Pro works on Macs or PCs. If you use both types of computers, FileMaker Pro makes the connection seamless. You can use the exact same databases on any computer, and even share them over the network simultaneously without a hitch (Chapter 17).
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 cousin cheaped out by signing his name on his brother’s gift card), and you can even 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, Websites, 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.
FileMaker Pro 11 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. Developers 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.
FileMaker Pro 11 includes many features that make day-to-day work in FileMaker easier than ever:
FileMaker Pro 11 gives you one more reason not to fire up Excel—charts! Bar charts, line charts, pie charts and more are built right in and easy to create.
Speaking of spreadsheets, the updated table view adds spreadsheet-style conveniences. Adding a new column no longer requires a trip to the database manager window and a visit to layout mode. You can do it all without leaving the familiar data entry view.
FileMaker has taken (and improved upon) an old developer technique called Quick Find. With no programming necessary, you can place a Quick Find box in your toolbar, and then use it to search every field on the current layout in one shot.
Long a stalwart of word processors, text highlighting offers a way to emphasize text beyond bold and italic.
Instant Web Publishing now has an improved status toolbar so your online databases look and work more like FileMaker Pro. Using a database via a web browser just got simpler.
If you regularly import information from the same source, say an Excel spreadsheet, the Recurring Import feature can simplify the process. Once you point FileMaker to the data source, it sets up a special script and layout. To update your database with the most current information, simply switch to that layout.
Database developers will appreciate these new features that help create databases (or improve those you already have):
Developers spend a whole lot of time in Layout mode and until now there was no single place where all the most commonly used tools were grouped. Rejoice click-weary developers, now you have the Inspector. A three-tabbed floating palette, the Inspector consolidates all the position, appearance, and data formatting options of Layout mode.
Portal Filtering vastly simplifies the work required to build dynamic changing lists of related data. It also reduces the number of single use table occurrences and relationships necessary to display your information.
Snapshot Links are tiny files that record the layout, found set of records, and sort order of your database at the time you create the snapshot link. Now, rather than asking someone to open a particular database, switch to a particular layout, find a given set of records and sort them by fields A, B, and C, you can just email a snapshot link and say “double click this.”
If your layouts have prodigiously proliferated, use Layout Folders to tame them. Much like the script folders introduced in FileMaker Pro 10, Layout Folders give you a way to organize layouts into logical groupings instead of one very long list.
External File Protection is a new way to prevent unauthorized access between FileMaker files. It works like a “white list” permitting connections only from preapproved files.
New and Revised Script Triggers.
OnObjectValidate triggers just before field validation to give you the ability to intercept invalid data and deal with it as you wish.
OnLayoutExit runs when a user attempts to leave a layout, or close the window while viewing that layout. The script runs before leaving the layout.
OnViewChange activates when changing between Form, List, or Table views and executes after arriving at the new view.
Similar to Merge Fields, new Merge Variables lets you display the value of a global variable dynamically, as layout text. “Live” text on a layout no longer needs to be in a field.
A huge limitation of scripted finds has been lifted with the arrival of Scripted Find Variables. Previously, the search terms of a scripted find request had to be static. FileMaker now permits the use of variables, simplifying dynamic scripted finds.
Layout Badges, those little icons that appear on layout objects, have a few additions:
Objects with Conditional Formatting get a new diamond shaped badge.
Quick Find fields (QuickFind) will have either a green or yellow magnifying glass badge depending on the estimated speed that a find can be performed. Local, indexed fields get the green badge while related and unindexed fields get the yellow version.
All badges now come in two sizes that FileMaker Pro automatically sets based on the size of the object they’re attached to.
If you use FileMaker Pro Advanced for its developers’ tools (and if you aren’t using it, you should), your professional life just got a whole lot easier. Here’s a partial list of its new features:
Moving custom functions from one file to another used to be a multistep, error prone process. Now you can copy and paste custom functions or import them from another file.
The Custom Menu interface has been extensively revamped with simplified controls and clearer language.
The absolutely invaluable Script Debugger has some new powers. It can now debug buttons and menu items even if they don’t trigger scripts. If you assign object names to your buttons, those names appear in the Script Debuggers call stack.
If you share databases over the network, then you’ll be glad to know about FileMaker Server’s new features. Here are the highlights:
Administrator Groups let FileMaker Server Advanced administrators define groups of related databases and delegate limited administration duties to others. These “junior administrators” can perform administrative tasks in their group and only in their group.
Reinstalling FileMaker Server or migrating a server to new hardware typically means recreating your backup schedules. FileMaker Server Advanced is now able to backup your schedules and administrator groups, and then reload them after a fresh install of the server software.
The Client Statistics view assists in troubleshooting connection problems. It offers a real-time view of a particular FileMaker Server client’s vital connection statistics.
Server administration relies on an Admin Server process that can fail even when the database server process continues to operate. FileMaker Server now monitors and repeatedly attempts to automatically restart the Admin Server if it fails.
Under the hood, FileMaker Server’s unified ODBC/JDBC (Open- and Java-Database Connectivity) module has been extensively revised to improve performance and compatibility.
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 clicker 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. Related procedures like Shift-clicking and Alt-clicking work the same way—just click while pressing the corresponding key.
Menus are the lists of commands 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.) You have 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 then 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 flow. 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 11: The Missing Manual.
FileMaker Pro comes with a PDF manual and an impressive online help system. These resources are actually pretty helpful—if you’re a programmer, that is, or if you’ve been working with FileMaker for a while. Between the manual and the help system, you can figure out how FileMaker works. But you 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. FileMaker Server lets you share your databases more safely and quickly than FileMaker Pro’s peer-to-peer sharing. Learn about FileMaker Server in Chapter 17.
FileMaker Pro 11: The Missing Manual is divided into six parts:
Part 1: Getting Started with FileMaker. 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. 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 2: Building Your First Database. It’s time to put theory into practice and build a new database from scratch. You’ll learn the ingredients that go into a functional database, and then spice it up with calculations that do some thinking for you and scripts that do some grunt work for you. You’ll take your flat database and make it relational, putting a very powerful spin on data organization.
Part 3: Thinking Like a Developer. You’ve kicked the tires and driven around town with FileMaker. Now, do you want to see what this baby can really do? You’ll learn some theory behind relational database design and how to create a variety of relationship types. The world of fields will open up with auto-enter data and validation to keep your information consistent and accurate. You’ll dig into the vast capabilities offered in Layouts—like using colors and images for an attractive look, making clickable buttons and building reports. And you’ll get a handle on the remarkable power of calculations and scripts.
Part 4: Becoming a Power Developer. Now you’re a living, breathing database machine. It’s time to trade up to FileMaker Pro Advanced, the FileMaker version expressly for power developers. You’ll learn how to reuse database components, step through a running script with the Script Debugger, and even bend FileMaker’s menus to your will. You’ll literally tunnel deeply into relationships, make layouts pop with conditional formatting and charts, and even put a real live web browser inside your database. You’ll learn enough about calculations to derive the answer to life, the universe, everything!
Part 5: 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 passwords, 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, and even with other databases.
Part 6: Appendixes. No book can include all the information you’ll need for the rest of your FileMaker Pro 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 website. It also covers the vast online community of fans and experts: People are the best resource for fresh ideas and creative solutions. Appendix B lists FileMaker error codes.
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, and completed examples against which to check your work.
You can get these files any time from the Missing CD page (see About MissingManuals.com on About MissingManuals.com). To download, simply click this book’s title, and then click the link for the relevant chapter.
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, rotating between Windows Vista and Mac OS X by chapter.
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 may read, “The keyboard shortcut for saving a file is Ctrl+S (⌘-S).”
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 2.
At www.missingmanuals.com, you’ll find articles, tips, and updates to FileMaker Pro 11: The Missing Manual. In fact, we invite and encourage 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. We’ll also note such changes on the website, so that you can mark important corrections into your own copy of the book, if you like. (Go to www.missingmanuals.com/feedback, choose the book’s name from the pop-up menu, and then click Go to see the changes.)
Also on our Feedback page, you can get expert answers to questions that come to you while reading this book, write a book review, and find groups for folks who share your interest in FileMaker Pro.
While you’re there, sign up for our free monthly email newsletter. Click the “Sign Up for Our Newsletter” link in the left-hand column. You’ll find out what’s happening in Missing Manual land, meet the authors and editors, see bonus video and book excerpts, and so on.
We’d love to hear your suggestions for new books in the Missing Manual line. There’s a place for that on missingmanuals.com, too. And while you’re online, you can also register this book at www.oreilly.com (you can jump directly to the registration page by going here: http://tinyurl.com/yo82k3). Registering means we can send you updates about this book, and you’ll be eligible for special offers like discounts on future editions of FileMaker Pro 11: The Missing Manual.
Safari® Books Online is an on-demand digital library that lets you easily search over 7,500 technology and creative reference books and videos to find the answers you need quickly.
With a subscription, you can read any page and watch any video from our library online. Read books on your cellphone and mobile devices. Access new titles before they’re available for print, and get exclusive access to manuscripts in development and post feedback for the authors. Copy and paste code samples, organize your favorites, download chapters, bookmark key sections, create notes, print out pages, and benefit from tons of other timesaving features.
O’Reilly Media has uploaded this book to the Safari Books Online service. To have full digital access to this book and others on similar topics from O’Reilly and other publishers, sign up for free at http://my.safaribooksonline.com.
Get FileMaker Pro 11: 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.
