The word “database” can be alarming. It calls to mind images of software engineering degrees and pocket protectors. But databases have been around much longer than computers—a phone book, a cookbook, and an encyclopedia are all databases. In fact, if you look up the word “database” in a dictionary (which is a database, too), you’ll find that a database is just a collection of information, or data.
The purpose of any database is to organize information so you can find what you’re looking for quickly and easily. Image a business card file (yep, that’s also a database) that organizes information about people alphabetically by name. You can find any person’s card because you know where in the alphabet to look, even though there may be thousands of cards to look through. Such physical databases have major limitations compared with their digital cousins. What if you want to get a list of all your associates in California? Your card file isn’t organized by state, so you have to flip through every card, one by one, to create a list. Digital databases help you avoid that kind of tedium.
FileMaker Pro helps you build a database so you can store information and then see that information the way you need to see it. At heart, a digital database isn’t much different from one collected on business cards. It contains lots of information, like addresses, Zip codes, and phone numbers, and it organizes that info in useful ways (see Figure 1 for an example). The program lets you organize the same information in numerous ways with ease—say, by name or by state. That list of associates in California you took hours to generate from a card file? FileMaker Pro can do in less than a second what it would take hours to do on physical cards.
Figure 1. FileMaker Pro lets you do just about anything with the information you give it. You can use it like a business card file to store and retrieve customer information, or run your entire business with 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.
This book shows 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 don’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, the photographs you’re selling on the Web, or how long it typically takes each member of your staff to get through his workload.
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 may be your choice:
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 nontechnical 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. FileMaker Pro, despite its focus on ease of use, is very powerful. It can handle large amounts of data. It lets lots of people in locations around the office or around the world share information in real-time. It even meets the needs of bigger companies, like integrating with high-end systems. And it’s adaptable enough to solve most problems. For example, 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, FileMaker can handle that, too.
FileMaker Pro works on Macs and PCs. If you use both types of computers, FileMaker Pro makes the connection seamless. You can use the exact same databases on any computer, or better still, share them over the network simultaneously without a hitch (Chapter 20).
FileMaker Pro lets you take your data with you. FileMaker Pro understands that people work on the road these days. Road warriors can access FileMaker databases from remote cities with an iPhone, iPad, or iPod Touch using FileMaker Go. If you don’t have an iOS device, you can still communicate with your database using a web browser (Chapter 21).
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’d 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.
You have plenty of company. Perhaps best of all, FileMaker Pro is very popular—there are more than 20 million copies of FileMaker Pro running around the globe. 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 comes with an impressive online help system, containing links to PDF user’s guides. These resources are helpful—if you’re a programmer, that is, or if you’ve been working with FileMaker for a while. Between the user’s guides and the help system, you can figure out how FileMaker works. But you have to jump back and forth to get the complete picture. And neither resource does a great job of guiding you toward the features that 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 help system and user’s guides don’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. And you can bookmark or highlight the most helpful passages!
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 Go is the free app that lets you run FileMaker databases on your iPhone or iPad. The iPad’s remarkable acceptance in boardrooms, factory floors, medical settings, and even the restaurant and retail realms has given FileMaker Pro a whole other life as an app-creation program. See Chapter 3 for an introduction to FileMaker Go.
FileMaker Pro Advanced contains advanced tools and utilities aimed at making development and maintenance of your databases easier. It’s a must-have for people who spend most of their time making databases for others. Its features are covered in Part 4. FileMaker Server lets you share your databases more safely and quickly than FileMaker Pro’s peer-to-peer sharing.
This book was written for advanced-beginner or intermediate computer users. But if you’re a first-timer, special sidebar articles called Up to Speed provide the introductory information you need to understand the topic at hand. Advanced users should watch for similar boxes called Power Users’ Clinic. They offer more technical tips, tricks, and shortcuts for the experienced FileMaker user.
The hundreds of figures in the book are snapshots of the FileMaker Pro screen, chosen to illustrate the objects and concepts covered in these pages. They were created using FileMaker Pro Advanced. If you’re using the standard version of FileMaker Pro, a handful of these images may differ from what you see on your own screen.
Except for the sections that cover the developer tools available only in FileMaker Pro Advanced—which includes most of Chapter 14—the examples and exercises in this book aren’t affected by any minor differences in what you see onscreen. As you learn to create more complex and powerful databases, or if you plan to become a professional FileMaker developer, you’ll eventually want to invest in FileMaker Pro Advanced. The additional cost is worth the programming efficiencies you’ll reap.
FileMaker Pro works almost the same in its Macintosh and Windows versions. For the most part, dialog boxes have exactly the same choices and the software behaves exactly the same way. When that’s not true, you’ll learn how and why there is a difference. In this book, the illustrations get even-handed treatment, rotating between Windows 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 ⌘ (command) 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 closing a window is Ctrl+W (⌘-W).”
FileMaker Pro 14: The Missing Manual is divided into six parts:
Part One, Getting Started with FileMaker Pro. Here, you’ll learn about FileMaker Pro’s interface and how to 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 learn how to filter the records you’re looking at with finds and how to snazz up your data with basic formatting. You’ll also learn how to manage a database in FileMaker Go.
Part Two, Building Your First Database. It’s time to put theory into practice and build a new database from scratch. 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, a database’s appearance is organized into layouts; you’ll learn how to use them to make your data easier to interpret and use. 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, which is two-dimensional, like a spreadsheet or table, and make it relational, so different tables of information can work together in powerful ways.
Part Three, 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 Four, Becoming a Power Developer. Now that you’re a living, breathing database creation machine, it’s time to trade up to FileMaker Pro Advanced, the FileMaker version created 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, and everything (well, almost)!
Part Five, Security and Integration. FileMaker knows your data is 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 Six, 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 will come in handy when you’re learning how to lay out your databases: It lists all of the badges that identify different types of layout objects. Geared toward developers, Appendix C demystifies using Insert commands with FileMaker Pro 14’s enhanced container fields. Appendix D is a list of all the error codes you may encounter when scripting FileMaker.
FileMaker Pro 14 is a single software package that serves two fundamentally different types of people: users and developers. 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 14 introduces a boatload of new features to make database creation more powerful and efficient. Here are the most notable:
The Script Workspace is a fundamentally new way to create scripts. The endless clicking and scrolling has been replaced with quick and efficient composing and editing from the keyboard. You can now work on multiple scripts in the Script Workspace’s tabs, and the script steps are color-coded for better readability.
Calculation editing also has a new look and feel. Extensive use of predictive type suggests functions as you start to type them. Better still, it offers to fill in the names of your fields as you start to type them, too.
Buttons can now have embedded icons along with, or instead of, text. You can also create Button Bars—single layout objects comprised of several independently clickable areas.
Navigation parts are ideal spots for those Button Bars. These non-printing parts remain anchored to the top and bottom of the FileMaker window regardless of how much scrolling takes place in between.
Placeholder text offers a new way to label your fields. The text appears when the field is empty, but disappears as soon as some information has been entered.
Security gets a streamlined interface with easy access to the most frequently used options and all the more complicated stuff just a step away.
The improved color palette gives a leg up to the chromatically challenged with a wider range of colors, collections of hues that work well together, and the ability to paste hexadecimal color values right into the palette.
Twelve new or improved script steps including Refresh Portal to update related data in a portal without having to update the whole screen, and Set Allowed Orientations to protect your finely crafted iOS solutions from undesired screen rotations.
Twelve new or improved functions bring more control over touchscreen keyboard and information about the state of the improved Audio/Visual player.
New script triggers aimed at improving the user experience on iOS.
Gesture support comes to Windows 8 touchscreen users.
Even users who never get beyond Browse mode will benefit from enhancements like:
The Launch Center, where you see your favorite, recent, and network-hosted files are all in one friendly place. And to prevent it from becoming a sea of identical icons, the Launch Center lets you apply custom solution icons to files so they’re easier to spot.
Audio and video playback provides a new set of controls that make playing, pausing, scrubbing, and going fullscreen more intuitive.
Windows users now benefit from saved passwords, bringing the formerly Mac-only feature to everyone.
Signature screens in FileMaker Go can now be more than just “Sign Here.” Users can see just what they’re agreeing to right above the signature.
WebDirect now officially supports well-equipped mobile devices on Google Android and Microsoft Surface operating systems, with faster performance for all WebDirect users.
If you’re planning to host your own databases, here are two new FileMaker Server capabilities worth noting:
Reconnect attempts to gracefully re-establish dropped user connections to hosted databases putting you back in where you left off so you don’t have to reopen your databases from scratch.
Standby Server is like an insurance policy. The feature lets you have a second FileMaker Server in constant readiness. Should your primary server crash, you can cut over to the standby server in minutes.
Meanwhile, three features present in earlier versions do not appear in FileMaker Pro 14:
Bento import has been discontinued in the wake of that product’s demise.
The Quick Start screen no longer appears when you open FileMaker, as it’s been supplanted by the new Launch Center.
Insert QuickTime has become Insert Audio/Video in support of improved media handling and playback.
Throughout this book, you’ll run into a few terms and concepts that you’ll encounter frequently in your computing life:
Clicking. This book includes instructions that require you to use your computer’s mouse or track pad. To click means to point your cursor (the arrow pointer) at something on the screen and then—without moving the cursor at all—press and release the left button on the mouse (or laptop track pad). To right-click means the same thing, but pressing the right mouse button instead (or, if you have a Mac with a one-button mouse, press ⌘ as you click). Usually, clicking selects an onscreen element or presses an onscreen button, whereas right-clicking typically reveals a shortcut menu, which lists some common tasks specific to whatever you’re right-clicking. To double-click, of course, means to click twice in rapid succession, again without moving the pointer at all. And to drag means to move the cursor while holding down the (left) mouse button the entire time. To right-drag means to do the same thing but holding down the right mouse button.
When you’re told to Shift-click something, you click while pressing the Shift key. Related procedures, like Ctrl-clicking, work the same way—just click while pressing the corresponding key.
Tapping. If you’re using a mobile device you won’t have a mouse, but you can still interact with your database. You just use your finger instead of a mouse or trackpad. Whenever you read the word “click,” just point to the item and tap lightly with the pad of your finger. Most of the variations above work the same way: you can double-tap an item, or keep your finger pressed to the screen while you drag an item. But since you don’t have buttons on your fingers (well, most people don’t), there’s no such thing as a right-drag on a mobile device.
Keyboard shortcuts. Nothing is faster than keeping your fingers on your keyboard to enter data, choose names, trigger commands, and so on. That’s why many experienced FileMaker users prefer to trigger commands by pressing combinations of keys on the keyboard. For example, in most word processors, you can press Ctrl+B to produce a boldface word. In this book, when you read an instruction like “Press Ctrl+A to open the Chart of Accounts window,” start by pressing the Ctrl key; while it’s down, type the letter A; and then release both keys.
Throughout this book, and throughout the Missing Manual series, you’ll find sentences like this one: “Open your Documents→eBooks→Downloads folder.” That’s shorthand for a much longer instruction that directs you to open three nested folders in sequence, like this: “Choose Open→Documents. In your Documents folder, you’ll find a folder called eBooks. Open that. Inside the eBooks window is a folder called Downloads. Click or tap 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.
As the owner of a Missing Manual, you’ve got more than just a book to read. Online, you’ll find sample databases so you can get some hands-on experience, as well as tips, articles, and maybe even a video or two. You can also communicate with the Missing Manual team and tell us what you love (or hate) about the book. Head over to www.missingmanuals.com, or go directly to one of the following sections.
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. Go to www.missingmanuals.com/cds/fmp14mm. To download, simply click this book’s title and then click the link for the relevant chapter.
Got questions? Need more information? Fancy yourself a book reviewer? On our Feedback page, you can get expert answers to questions that come to you while reading, share your thoughts on this Missing Manual, and find groups of folks who share your interest in FileMaker. To have your say, go to www.missingmanuals.com/feedback.
In an effort to keep this book as up to date and accurate as possible, each time we print more copies, we’ll make any confirmed corrections you’ve suggested. We also note such changes on the book’s website, so you can mark important corrections into your own copy of the book, if you like. Go to http://tinyurl.com/fmp14-mm to report an error and to view existing corrections.
If you register this book at oreilly.com, you’ll be eligible for special offers—like discounts on future editions of FileMaker Pro 14: The Missing Manual. Registering takes only a few clicks. To get started, type http://oreilly.com/register into your browser to hop directly to the Registration page.
Safari Books Online is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.
Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.
Safari Books Online offers a range of plans and pricing for enterprise, government, education, and individuals.
Members have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and hundreds more. For more information about Safari Books Online, please visit us online.
Get FileMaker Pro 14: 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.
