For many people, the word “database” conjures up an image of a computer and 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 18).
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, 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.
FileMaker Pro 10 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 10 includes many features that make day-to-day work in FileMaker easier than ever:
When you create a new file, you see a redesigned Quick Start screen with more options. You can still create a new database from one of FileMaker’s many templates, create a new file from scratch, open an existing file, or use the Learn More button to get help before you even start. You can create a new database from even more sources than ever before, like Excel .xlsx files, or FileMaker’s little sister Bento (version 2 only). (For the scoop on Bento, see the box on the next page.
All 30 Starter Files have gotten an interface-lift for a younger, fresher look, and the 13 most popular starter files have many new features. All the old features have been retained, though, so you can still use your existing databases and designs. The new designs also bear a strong family resemblance to Bento.
The largest cosmetic change also brings you new power. The Status toolbar has gotten a complete overhaul, and now lives at the top of the screen. All tools have a new look and new abilities, and show tooltips when you hover over them with your mouse. Plus, you can customize each toolbar so it displays only the tools you use.
Fields now have Find icons in Find mode. This extra feedback helps prevent you from entering data when you should be entering search criteria.
You can make onscreen and printed reports more easily than ever. The Saved Find Requests tool holds a pop-up menu that lets you choose from your recent finds, and save custom finds.
The toolbars and menus now include common font sizes, like 11 and 13 points. Now you don’t have to create a custom font choice just to change a font to one of those common sizes.
When you’re doing data entry in a found set that’s sorted (say you’re adding names to your company’s bulk mail list, and you want to see the records sorted by Zip code), new records automatically appear in sorted order. You don’t have to keep re-sorting.
Sub-summary values get updated in real time if you add records or edit data while you’re viewing a subsummary report. No need to go to Preview mode to view the updated math.
Database developers will appreciate these new features that help create databases (or improve those you already have):
You can now specify an SMTP server instead of relying on your users’ email clients when you use the SMTP-based Send Mail script step for sending email.
Insert into Tab Order lets you edit a layout’s tab order by typing the new object’s position in the order. All the other objects in the tab order automatically adjust to accommodate the new object in your specified order.
Script Triggers let you have a script run when someone clicks a button or does some other specific action in your database. Almost any object on a layout can run a script trigger, as can layouts themselves. So you can, for example, run a script that checks data entry for accuracy when someone edits data in a field, or automatically perform a find whenever the person navigates to a layout.
Ipv6 Support lets you address internal networks with the new, expanded protocol, plus the Get (HostIPAddress) function returns IPv4 and Ipv6 addresses as appropriate.
With more layout themes, you can start databases more quickly and with more polish.
New calculation functions let you use Unicode text and numbers, track information about the actions that trigger scripts, and more.
Save time when you create a new script, with a Default Script that gives you a jumping-off point for commonly used script steps.
An improved Print script step lets you save a specified target printer instead of choosing a printer every time.
You can now launch PHP Site Assistant from the Tools menu, and you don’t have to click through the Server’s start page to get there.
If you want to put data into a field using a script (maybe you’re checking a user’s data entry before you let her create a new record), you can now set the data without hard-coding the field’s name with the new Set Field by Name script step. That way, you can use the same script for many different purposes instead of writing a new script for each field into which you need to put data.
When you’re creating scripts that run under a variety of conditions (over the Web, on a FileMaker Server, or standalone), you can use the new Show Compatibility pop-up menu to see at a glance if your scripts will work .
With the new Install OnTimer Script Step, turn FileMaker into an automated processor that can run repeated tasks, like verifying and approving orders submitted through your Web site. It runs a script at the interval you set.
If you use Windows, you’ll appreciate the convenience of creating scripts while viewing your database in the background, since the Manage Script dialog box no longer opens maximized, covering your work area.
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:
When something goes wrong with your files, Advanced Recovery Options give you more control over the diagnostics and repairs FileMaker Pro Advanced can perform on damaged files.
To use FileMaker Pro 10’s new features more effectively and troubleshoot them quicker, see them each listed in the Database Design Report (DDR).
If something’s going wrong with a script, debugging is easier now that the most recent error is no longer cleared from the Get (LastError) function display.
New calculation functions let you find out more about your database and the data it stores. You can monitor Script Triggers with Get (TriggerModifierKeys) and Get (TriggerKeystroke). Char( ) and Code( ) give these functions even more power by pinpointing which characters are sent to scripts when they’re triggered. GetFieldName( ) and SetField by Name( ) give you the power to address fields with a calculation.
If you share your FileMaker files with other databases like Oracle and SQL, or even Excel and Bento documents, you’ll like the new features that make collaboration easier:
External Data Sourcesyou can use now include Oracle 11g, MySQL Community Server 5.1, and Microsoft SQL Server 2008.
ESS now lets you use Windows Single Sign-on.
You can make data entry in FileMaker more predictable by creating Value Lists from fields in ESS tables.
When you use Microsoft SQL Server’s datetime fields, you can now parse out only the date or only the time from those fields.
If your boss doesn’t use anything but Excel 2007/2008, you can now import and export FileMaker data to that format.
If you’ve got a client who doesn’t use anything but Bento, you can import her email list with Bento 2 compatibility.
If you share databases over the network, then you’ll be glad to know about FileMaker Server’s new features. Here are the highlights:
FileMaker Server can now send email without a robot machine to handle your emailing chores.
If you use multiple FileMaker servers, you no longer need a robot or human intervention to handle imports/exports—Server can now move data in and out of its hosted files without opening a file on a client machine.
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.
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 10: 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 Chapters Chapter 12 and Chapter 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.
FileMaker Pro 10: The Missing Manual is divided into seven parts.
Part One: 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.
Part Two: 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 Three: 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 Four: 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 more than 200 functions to do the math for you. Surprisingly, you can also use functions on fields that don’t contain numbers. For example, some functions work 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 they find out.
Part Five: Scripting. Because you have to do so many things with a database that are repetitive, tedious, or just plain 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 Six: 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. In the final chapter, you’ll see the design and development tools provided in FileMaker Pro Advanced to speed creation time and give you more control over how your database works.
Part Seven: 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 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, alternating 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 http://missingmanuals.com/, you’ll find articles, tips, and updates to FileMaker Pro 10: 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 Web site, so that you can mark important corrections into your own copy of the book, if you like. (Go to http://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 http://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 10: The Missing Manual.
When you see a Safari® Books Online 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 10: 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.
