FileMaker Pro 8: The Missing Manual

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

FileMaker Pro databases can be as simple as a phone list for the soccer team or as complex as a company-wide system for purchasing, sales, inventory, invoicing, shipping, and customer tracking. But they all have a few important aspects in common and essentially work the same way. This chapter gives you a tour of FileMaker's major features and gets you up and running on your very first database.

FileMaker's vast assortment of tools and options can make its window as intimidating as a jumbo-jet cockpit. But the program's menu commands, dialog boxes, keyboard shortcuts, and other options stay largely consistent across all databases, from the most basic to the most high-powered. Almost everything you learn in the next few pages applies to every database you'll ever use.

Because a database usually solves a problem of some kind, some FileMaker experts call a database a solution, as in, "I can create an inventory solution for your bakery, but it's going to cost you some dough." Usually, database and solution mean the same thing, although the term solution sometimes implies a system of several connected databases (more on that in Part 3).

Every FileMaker Pro database has two things in common: One, it stores heaps of information; and two, it gives you the tools to manage that information. Whether you start from one of FileMaker's templates (Section 1.1.2.2) or a completely blank slate (Section 1.2.1), your new database starts out data-free. It's an empty vessel waiting to be filled. (It would be nice if the program could figure out what data you want and enter it for you, but that probably won't happen until FileMaker Pro 8008.) You have to take care of the data entry yourself. At first, all you see in the window are the tools—buttons, controls, and pop-up menus—that you use to fill the database up with data.

Every database window has the same basic structure. FileMaker provides a handful of special items around the window, as you can see in Figure 1-1.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Every FileMaker Pro database has two things in common: One, it stores heaps of information; and two, it gives you the tools to manage that information. Whether you start from one of FileMaker's templates (Section 1.1.2.2) or a completely blank slate (Section 1.2.1), your new database starts out data-free. It's an empty vessel waiting to be filled. (It would be nice if the program could figure out what data you want and enter it for you, but that probably won't happen until FileMaker Pro 8008.) You have to take care of the data entry yourself. At first, all you see in the window are the tools—buttons, controls, and pop-up menus—that you use to fill the database up with data.

Every database window has the same basic structure. FileMaker provides a handful of special items around the window, as you can see in Figure 1-1.

Figure 1-1: Every database window has the same apparatus around the edges—what's inside the window is up to you. Compare this window with the one shown in Figure 1-2. Both windows have a status area, zoom controls, and a Mode pop-up menu. The content is the only difference.

While some features like the status area, zoom controls, and Mode pop-up menu stay the same no matter which database you're using, what's inside the content area is the actual information (data) that makes each database unique.

When you create a new FileMaker database, you start with something that looks a little like Figure 1-1: plain and simple. That's because you haven't added any content yet. In Figure 1-2, you can see what your database will look like once you've started to fill it in. Depending on how you want to look at your information, you can use the controls described below to alter how your content appears onscreen.

This unassuming menu may not be the most prominent control in the window, but it's one of the most important. Each of FileMaker's four

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The best way to understand the concepts introduced in this chapter is to get some mouse-on experience. Fortunately, FileMaker Pro gives you a quick way to jumpstart a new database.

Although a database can do just about anything, most people want to do a few of the same things (like keep track of their contacts). Accordingly, FileMaker Pro comes with dozens of prebuilt templates: sample databases that you can fill in with your own data and even customize as you see fit. A template is essentially a sample database, without any information filled in yet. Templates let you start up a database quickly, and as you go along, change or expand it to suit your needs. Almost any conceivable database can be built on one of these foundational layouts; see the box on Section 1.1.2.2 for the full catalog.

if you're the DIY type, see Chapter 3 for instructions on designing your own database layout from scratch.

Since just about everybody in the world needs to keep track of people, a good place to start your FileMaker experience is with a Contact Management database, which does just what its name suggests: It keeps track of people and their various numbers and addresses. This is the template you might use if, for example, you volunteer for a local repertory company and need a place to store the names and addresses of all season-ticket holders. Once you've entered all the information, you can use the database to, say, print letters asking your subscribers for donations to provide new cup holders for the orchestra pit.

To start a new database from a template, you start by opening the template. Launch FileMaker Pro (by using the Start → Programs menu in Windows, for example, or clicking its Dock icon on the Mac) and choose File → New Database. The New Database dialog box appears, as shown in Figure 1-4.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Each database you create with FileMaker Pro is stored in a file on your hard drive—just like your Microsoft Word documents, Excel spreadsheets, and all the PowerPoint presentations you've ever created. This file contains all the information about how the database is structured, plus all the information stored inside it, which means you can open, close, copy, or back up a database as you would any other file. But if you need a bit more explanation, this section explains how to do those tasks, including some quirks that are particular to FileMaker files.

To open a database that already exists, open FileMaker Pro and choose File → Open Database. Now select the file you want to work with (Figure 1-6). If you prefer, you can find the file using Windows Explorer (Windows) or the Finder (Mac OS X) and double-click its icon.

When you open a database, you'll see one or more windows on your screen. If you've opened the Contact Management Template you created on Section 1.1.2.2, you have one database open, and that database has one window displayed.

Figure 1-6: When you choose File → Open, you see the standard dialog box that lets you find the file you need. A drop-down menu lets you choose the specific type of file you're interested in opening. (As you can see, FileMaker can handle more than just its own files. You'll learn more about that in Chapter 17.)

To close a database, close all of its windows in FileMaker: Choose File → Close or press Ctrl-W (⌘-W) When you have more than one database open, it isn't always easy to tell which windows go with each database.

If you're not sure what to close, there's an easy way to close all the windows in all the databases you currently have open. Hold down the Alt key (Windows) or the Option key (Mac), then click the trusty File menu. In place of the Close command, you'll see the more powerful Close All. Choose it and FileMaker closes all its open windows, which also closes all your open databases.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Everybody knows that it's important to save files early and often, right? So you're working along in FileMaker entering information about your office birthday roster, and as good habit dictates, you type the keyboard shortcut that saves in practically every program in the known universe (that'd be Ctrl+S on Windows and ⌘-S on the Mac). Up pops the wrong dialog box. This one's asking you how you want to sort your data! What gives?

Don't worry, FileMaker has you covered. The program automatically saves all your work in a cache, which is part of your computer's RAM (Random Access Memory). Then, periodically, FileMaker transfers the information from the cache to your hard drive—where it's secure.

You can control how much work is held in cache before it's saved to your hard drive, as described in Figure 1-7. In Windows, choose Edit → Preferences, then click the Memory tab. On the Mac, choose FileMaker Pro → Preferences, then click the Memory tab.

Figure 1-7: Specify the size of the FileMaker's cache and how often your work is moved from the cache to your hard drive. (Nerds call that flushing the cache.) A larger cache yields better performance but leaves more data in RAM. If you're working on a laptop, you can conserve battery power by saving cache contents less frequently. Just remember, in case of a power outage or other catastrophe, the work that's in cache isn't as secure as what FileMaker's saved to your hard drive.

Chances are the data in your database is important (would you really go to all this trouble if it weren't?). Although FileMaker automatically saves your work as you go, what if the database file itself gets lost or suffers some digital harm? It's in your best interest to back up your database periodically. You can perform a backup by simply copying the database file. For example, you can copy it to a CD, email it to a friend, or duplicate it and tuck the copy away in another folder. The easiest way to make a backup is to choose File → Save a Copy As. You'll see the typical Save dialog box. Just make sure that "copy of current file" is selected as the Type option. When you click OK, FileMaker makes your copy in the background, then you can continue working in the original file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When you first open a new FileMaker database (the Contact Management template in this example), it has everything you need to make a database except…well, data. Now that you understand the basic components of a FileMaker database, it's time to start adding your own content. Whether your database contains information about individual persons, objects, pictures, dung beetles, or whatever, FileMaker always thinks of that information in individual chunks called records. Each record contains everything the database knows about that person, thing, insect, or whatever.

Now, because you need to store many smaller pieces of information in each record (like a person's phone number, address, birthday, and so on), FileMaker can bestow each record with an almost infinite number of fields—the specific bits of data that define each record and make it unique.

For example, each person in a database of magazine subscribers gets her own record. Her first name, last name, phone number, street address, city, state, zip code, and the expiration date of her subscription are all examples of fields each record might include.

The techniques in this section work the same whether you're creating a new database for the first time (from the Contact Management template described on Section 1.2.1, for example), or adding to an existing one (like the example file described on Section 1.6.1).

All records in a database must contain the same fields, but that doesn't mean you have to have to fill them all in. For instance, in a gift list database, if your boyfriend refuses to disclose his hat size, you can just leave that field blank in his record.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As exciting as it is to enter data into a database—well, the first time anyway—chances are you'll also want to look at those records again at some point. In fact, some might say that's the whole point of building a database. But first, you need to learn how to navigate around the records in the database, which this section explains. Fortunately, moving around a database isn't nearly as tough as following maps and highway signs in a foreign country. FileMaker makes it easy to skip from one record to the next, all the while keeping you abreast of where you are.

As you go through this section on navigating, it helps to have a database open in front of you so you can follow along and try some of these techniques. Since you probably don't have many records in the Contact Management database you just created, there's a sample one for you to download on the "Missing CD" page at www.missingmanuals.com (Section 3.4.5).

In the Contact Management database, you can add as many records as you want. But you can view only one at a time in the window FileMaker displays. To tell FileMaker which record you want to look at, you have three options:

The book icon lets you flip from record to record one at a time. Pretend your database is a book, with each record on its own page. To get to the next record, click the right-hand page. To go back, click the left-hand page. If you can't go any further in one direction, the appearance of the icon's "page" changes, as shown in Figure 1-10.

Figure 1-10: The status area is the key to record navigation. In addition to displaying the controls for switching records, the status area indicates where you are in the database. This series of pictures, for example, shows the status area when you're on the first record in the database (left), a record in the middle of the database (middle), and the last record (right). Notice how the appearance of the Book icon and slider changes in each picture.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

So far, you've spent all your time in the same Contact Management window. If you're feeling a little claustrophobic, fear not. At any time, you can get yourself another window by choosing Window → New Window. What you get is an exact copy of the window you were just looking at. What's the big deal, you ask? The big deal is that you can switch to another record or do almost anything else in the new window, without affecting what's displayed in the first one.

Figure 1-14: FastMatch lets you select a piece of information from the record you're viewing (in this case, it's "hogwarts" from the email field) and find all other records that match it without the rigmarole of switching to Find mode.

When you have multiple windows open, you can change most of the settings you've seen so far for each window. For example, if you want to compare two contacts side-by-side, you can show one contact in each window.

You can even zoom one window in and another one out, or show the status area in one window but not the other (if that's your idea of fun). Another time multiple windows are useful is when you're working with one set of found records (Section 1.6.3.2) but need to do another search. You can perform a find in a new window without disrupting your work in the original window. Say you've been fiddling with the Find command to come up with all your contacts who live in California, then suddenly need to find the phone number for a colleague in New York. Just create a new window and look up your East Coast colleague there (Figure 1-15). Your California group is safe and sound in the first window.

If you have two windows open, both of them are connected in one way. If you edit the data in one window, the changes show up in the other window (or every window you have open, if you're working with multiple windows). Since FileMaker's windows just display the records in your database, a second window doesn't mean you have a second database—instead, both windows share the same data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

With all its advanced database options—like Find mode in this chapter and the relational database features covered in the later parts of this book—FileMaker is a pretty good deal for $279. But you're getting way more for your money than a powerhouse database program. FileMaker has many of the capabilities of a basic word processor built right in, so you can unleash your creative text-formatting urges using tools that look and feel quite familiar. What you can do with FileMaker is limited only by your imagination and willingness to plunge into a few dialog boxes.

Once you have even a few records entered into your database, clicking through them all whenever you want to make changes gets cumbersome. This chapter shows you how to pull up the records you want to work with. Then once you've got 'em, the real fun begins. You'll find out all the ways you can format text in File-Maker to get just the right look for your project, and how to print it out on paper.

In the Contact Management database example, you learned how to add to and edit your new database with only one record onscreen at a time (Section 1.5.3). This view is a common way to look at your information, but it's far from the only way. In fact, there are three possible ways to look at a database: Form view, List view, and Table view.

The one-at-a-time approach to viewing records you saw in Chapter 1 is called Form view. In List view, you see lots of records in, well, a list. If they don't all fit in the window, you can use the vertical bar to scroll through them. If you've used a spreadsheet program like Microsoft Excel, the Table view will look familiar—it looks a lot like a spreadsheet, with one row for each record and one column for each field.

To switch among views in any FileMaker database, use the View menu. Choose View → As Form, View → As List, or View → As Table. Figure 2-1 shows the same database in all three views.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the Contact Management database example, you learned how to add to and edit your new database with only one record onscreen at a time (Section 1.5.3). This view is a common way to look at your information, but it's far from the only way. In fact, there are three possible ways to look at a database: Form view, List view, and Table view.

The one-at-a-time approach to viewing records you saw in Chapter 1 is called Form view. In List view, you see lots of records in, well, a list. If they don't all fit in the window, you can use the vertical bar to scroll through them. If you've used a spreadsheet program like Microsoft Excel, the Table view will look familiar—it looks a lot like a spreadsheet, with one row for each record and one column for each field.

To switch among views in any FileMaker database, use the View menu. Choose View → As Form, View → As List, or View → As Table. Figure 2-1 shows the same database in all three views.

Figure 2-1: Same database, same layout, different views. In Form view (top) you see just one record at a time. List view (middle) has the same look, but it shows every record in one scrolling list. Table view (bottom) ignores the design completely and displays just the information.

When you design a database, you can add clickable buttons, tabs, or other handy means to switch among views. For example, the Contact Management database you used in Chapter 1 has View tabs built into the template (see Figure 1-5). You can even turn off certain views if you want. If your database holds mostly digital photographs and makes no sense in Table view, then you can make sure no one ever sees it that way. (You'll learn how in Chapter 5.)

In Form view, you see only one record at a time. If you want to see the next record, you must click the pages of the Book icon, press Control-down arrow, or use some other method of switching records (see Section 1.6.1). Most of the database work you've done so far has been in Form view. You might use Form view when you have a lot of information to see about one record, or if you want to focus on just one record without being distracted by all the other records.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 1 you learned about using Find mode for simple searches—but you only scratched the surface. The more records you have in your database, the more you'll need advanced finding techniques to avoid wasting precious minutes clicking the Book icon 1,057 times in a row just to find the record or records you want to display, edit, or print. FileMaker's Find tools give you the power to track down the one record in 100,000 you need right this minute, or the five records with missing phone numbers that you created a week ago Tuesday.

Figure 2-4: When you click a column header, FileMaker darkens that header and sorts the list. Here, for example, the list is sorted by Last Name. If you sort the records using the Records → Sort Records command instead, FileMaker doesn't try to figure out which column was used. Instead, it simply lightens all the column headers.

If you know what you don't want better than what you do want, you can use the Omit checkbox. This feature comes in handy when the records you're looking for can best be described by what they aren't. For example, "every person who isn't from California" is a lot easier to say than "everyone from Alabama, Alaska, Arizona, Arkansas, Colorado…" In this case, you can get what you want by creating one find request, with California in the State field. Then, before you perform the find, simply turn on the Omit checkbox, which appears in the status area only when you're in Find mode (Figure 2-5). That's all there is to it. FileMaker starts with every record in the found set, then throws out all the records with "California" in the State field, so you're left with everything else.

Omit also works with multiple find requests (see the box on Section 2.2.1.3). If all your requests have the Omit option set, then all the records that match any of your requests are thrown out. Whatever's left over goes in your found set. If you have a mix of requests with and without Omit set, FileMaker first finds all the records that match your normal requests. It then discards any that match an Omit request. You're finally left with a found set in which every record matches at least one of your requests, and doesn't match any of your Omit requests.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sometimes the whole reason you performed a find is to change something in several records. If one of the companies you work with changes its name, you may have several records that need to be updated. The first step to fixing them is to find them. Once your found set includes the people at that company, you could change the Company Name field one record at a time (especially if you're billing by the hour). But a better use of your time is to use the Replace Field Contents command. Here's how it works:

Click in the Company Name field (it doesn't matter which record), and correct the company name.

You've just fixed one of the records. All the others in the found set need the same fix.
Choose Records → Replace Field Contents

The Replace Field Contents dialog box appears (Figure 2-6). It has a handful of options that may not make sense to you yet. That's OK; just choose the first one: "Replace with." The new data you just typed is listed beside this option.

Figure 2-6: The Replace Field Contents dialog box has three options: "Replace with," "Replace with serial numbers," and "Replace with calculated result." The first option is the only one you're concerned with right now. It replaces the contents of the current field in every record in the found set with whatever is in the current record when you click Replace. (You'll learn about serial numbers in Chapter 3 and calculations in Chapter 9.)
Click Replace

FileMaker now updates the Company Name field in every record in the found set to match what you typed in the current record. When it's done, you're still sitting on the same record, but if you use the Book icon to click through the records, you'll see that they've all been changed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Your Rolodex may be limited to an alphabetical (by last name) arrangement, but a FileMaker database has no such limitation. You can sort the records in any order you want, as often as you want. You can even do a sort-within-a-sort, as you'll see later in this section.

The process always begins the same way: Choose Records → Sort Records. You'll see the Sort Records dialog box shown in Figure 2-7.

The Sort Records window lists all fields in your current layout on the left. Use the pop-up menu at the top of the list to switch to a list of all the fields in the table that are tied to the current layout. See Section 7.3.5.7 for details on tables and layouts. You tell FileMaker what to sort by moving the field to the list on the right. For example, if you're in need of a short-term loan, you may find it useful to sort your contacts by annual income, so you'd start by clicking the annual income field.

Figure 2-7: The Sort Records dialog box offers a lot of options, but the two lists on top, and the first two radio buttons, are critical to every sort you'll ever do in FileMaker. In a nutshell, you pick the fields you want to sort by and the order they should be sorted. Then click Sort. That's the essence of any sort, from the simple to the most complex.

The steps below, though, show a more common example—sorting by Last Name:

Choose Records → Sort or press Ctrl+S (⌘-S).

The Sort Records dialog box (Figure 2-7) appears.
Select the Last Name field from the list on the left, and then click Move.

The field name appears in the Sort Order list on the right. You can save a little mouse-mileage by double-clicking a field on the left instead of selecting it, then clicking Move.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once you've found the records you want to work on, it's time to learn timesaving and creative ways to revise and format your record text. FileMaker fields are a lot more capable than the little fields you're used to from dialog boxes. Each field is like a mini–word processor, with features that you're familiar with if you've ever written a letter on a computer. Of course you can do basic things like select text, and cut, copy, and paste. There's even a Find and Replace feature and flexible text formatting powers. Read on to find out what you can do.

Like your word processor, FileMaker has a Find and Replace feature. And also as in your word processor, you can (and should) use Find and Replace tools as often as possible to automate your editing process and eliminate retyping.

Suppose one of your clients is called Anderson Consulting. For one reason or another, they decide to change their name to Accenture. Unfortunately, you have 27 folks in your database with the old name. You could look through your records one by one and fix them yourself, but you're never going to become a database guru that way. Instead, do a Find/Replace operation.

FileMaker fields can hold a lot of information, and it's not at all uncommon to put things like letters, emails, product descriptions, and other potentially long documents into a field. In cases like this, the Find/Replace command is just as useful as it is in your word processing program.

Since FileMaker has fields and records to worry about, though, its Find/Replace dialog box is a little more complicated that what you may be familiar with. Luckily, the concepts are simple, as shown in Figure 2-10. The Find/Replace dialog box lets you search a field, a record, or all records for a little snippet of text. It can also replace every occurrence of that text with something new—either one at a time, or all at once.

Don't confuse Find/Replace with Find mode. Find/Replace is for finding

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sometimes you put lots of information in a field. For example, suppose you decide to keep track of all your correspondence with your customers. You might make a database to store letters and emails so you can search them or print them out, as needed. In a database like this, you usually end up typing (or pasting) long blocks of text into a field, where you need to control more than just the font, size, style, and color.

Text alignment is perhaps the most important aspect of your document's appearance. You can have text rigidly justified on both right and left, or with a loose right margin for a more casual look. If you use FileMaker to send party invitations to your contacts, you can use centered alignment for an attractive, social look. People may not think about text alignment much, but it really sets the whole tone of a document. FileMaker puts these settings right on the Format menu.

The Format → Align Text menu offers four useful choices:

Choose Left to make every line of text start at the left edge of the field. As the text wraps from line to line, the right edge appears jagged.
Choose Center to make every line of text center itself in the field. In this case, both edges are jagged.
Choose Right if you want every line to end exactly at the right edge of the field. As you can probably guess, this options means the left edge is jagged.
The Full text alignment (sometimes called Justified in other programs) tells FileMaker to take special care to make every line start on the left edge of the field and end on the right edge. It adds a little space between words and letters as necessary so that everything lines up.

The Format → Align Text menu also has three perpetually disabled items: Top, Center, and Bottom. These items control

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Words and numbers form the bulk of most databases. All these letters and figures convey important information, but they can be pretty boring. Increasingly, people are using FileMaker databases to store images, movies, and other bits of multimedia. For example, you can store a photograph of each employee right along with her personnel records. Or add product shots to your inventory database.

The Contact Management database has one field you may have been ignoring until now: Image. Unlike all the other fields in this database, the Image field doesn't expect (and won't accept) typed text. It's a special kind of field called a container field. Container fields can hold just about anything you want, including pictures, sounds, animation, music, and movies. You can even put any file from your hard drive into a container field, like a PDF file or a Word document. You can see container fields holding assorted things in Figure 2-21.

Figure 2-21: Container fields can hold any file. If you put a picture into a container field, FileMaker shows you the picture. If you insert a sound file, FileMaker gives you a special speaker icon that plays the sound when clicked. Music and Movie files give you the typical play, pause, fast-forward, and rewind controls, just like movies on the Web. For other kinds of files, FileMaker just shows you the file's icon.

Whether you realize it or not, when you use container fields you're using FileMaker as asset management software. That's the industry term for elaborate, customized software that contains and catalogs huge numbers of images (for use in brochures, books, or Web sites, for example). These programs are essentially databases, and with FileMaker, you can also manage large numbers of the images and documents you use every day. The data in a container field falls into one of four categories: picture, QuickTime, sound, and file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Considering all the tools FileMaker provides for editing your records, it'll come as no surprise that it also has a full spell checker built right in.

Before printing out your database or otherwise sharing it with the greater public, you'll want to make sure your spelling is correct. If you've been typing new records at lightning speed, there are bound to be some typos. And nothing screams "amateur" louder than a date field that reads "Febuary 15." All the spell checking commands are found under the Edit → Spelling menu. Here you have three choices:

Choose Edit → Spelling → Check Selection to spell check selected text only.
To check the entire record, choose Edit → Spelling → Check Record.
And finally, you might want to check spelling on many records at once. In this case, choose Edit → Spelling → Check All. When you choose this option, you're telling the spell checker to look at every field of every record in the current found set.

To check spelling in every record, make sure you have them all showing first. Just choose Records → Show All Records before running the spell checker

No matter how many records you're checking, FileMaker opens the same Spelling dialog box shown in Figure 2-23 as soon as it stumbles upon a misspelled word. At bottom, the Spelling window says Status: Questionable Spelling. The Word text box displays the word in question. There are a few different ways things can proceed from here:

You can edit the misspelled word directly in this box. More often than not, though, the correctly spelled word appears in the list below. In this case, just click the correct spelling, then click Replace. (Alternately, you can simply double-click the correctly spelled word and save the trip to the Replace button.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

All this talk about the wonders of an electronic database may leave you thinking FileMaker has nothing to do with paper, but that's not true. It's a cruel fact of life that eventually you'll need to put your data on paper. You might want mailing labels for all your customers in Canada, or a special printed form prefilled with patient information for insurance filing. Sometimes you just need your data with you when you're away from your computer. As you'll learn in Part 2, you can arrange the data any way you want in FileMaker, and make certain layouts that are particularly suitable for printing. But for now, remember that FileMaker lets you print anything you see onscreen (just choose File → Print). Its Print dialog box has a few special options, as shown in Figure 2-25. (In Mac OS X, you have to choose FileMaker Pro from the pop-up menu to see them.)

Figure 2-25: FileMaker's Print dialog box gives you all the Windows (right) or Mac OS X (left) standard options, depending on what kind of computer and printer you're using. It also lets you decide which records you want to print (every record in the found set, or just the current record). To see the FileMaker-specific options on Mac OS X, you first have to select FileMaker from the unlabeled pop-up menu in the Print dialog box.

Records being browsed tells FileMaker to print every record in the found set (Section 1.6.3.2). If you want to print all your Canadian customers, choose this option.
Current record will print just the current record, which comes in handy when you just want to print one thing: your doctor's contact information to keep in the car, perhaps, or maybe Aunt Edna's candied yams recipe.
Blank record, showing fields

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In Chapter 1, you jump-started your first database using one of FileMaker Pro's built-in templates. All FileMaker templates started out as blank databases. Some diligent FileMaker employees set up the fields and the formatting to get you started quickly, but there's no magic involved. With just a little knowledge and experience, you can create a database with all the power and fancy features of any FileMaker template. This chapter shows you how to build a contact management database from the ground up.

You really get FileMaker to work for you when you build your own databases. Lots of programs out there can help you keep track of one thing or another, but a database you build can think the way you do. You can design it to capture, organize, and display the information you need to know, when you need to know it—rather than making your business (or life) fit the software. It's time to learn how to custom-craft a database just for you.

So you're getting tired of your job, and you decide it would be exciting to get into the private investigator business. You found an office, ordered business cards, secured a phone line, and purchased some snazzy furniture. But have you thought about how you're going to run your business? Chances are, you could use some help from a database. And the first thing you need to think about when building a database is what kind of information it's going to track. For example, a private investigator might want to keep track of mostly people—names, phone numbers, aliases, passport numbers, and so on. Someone in retail, however, might want to track inventory—product names, descriptions, item numbers, prices, quantities, and similar details.

You already know that whatever information you put in your database goes into fields—and that's where your database building begins. These fields in turn comprise a table. Tables are at the heart of the database, holding all the information and keeping it organized. Everything else in a database works in service of the tables in some way: letting you edit, extract, or view the information.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

So you're getting tired of your job, and you decide it would be exciting to get into the private investigator business. You found an office, ordered business cards, secured a phone line, and purchased some snazzy furniture. But have you thought about how you're going to run your business? Chances are, you could use some help from a database. And the first thing you need to think about when building a database is what kind of information it's going to track. For example, a private investigator might want to keep track of mostly people—names, phone numbers, aliases, passport numbers, and so on. Someone in retail, however, might want to track inventory—product names, descriptions, item numbers, prices, quantities, and similar details.

You already know that whatever information you put in your database goes into fields—and that's where your database building begins. These fields in turn comprise a table. Tables are at the heart of the database, holding all the information and keeping it organized. Everything else in a database works in service of the tables in some way: letting you edit, extract, or view the information.

Conceptually, a table has rows and columns. The fields you define become the columns in the table. As you add records, you add new rows to the table. It's called a table because it stores information in a tabular form, just like the table of values in the back of a college math book. When you first start out with FileMaker, you may not even be aware that tables exist, but you have one anyway. FileMaker always creates one table for you when you make a new database, and that's where it puts all your information. It doesn't matter how your database looks—even if you never see any rows and columns, you still have a table inside where the data lives. See the box on Section 3.1.2 for more on tables.

In this chapter, you're going to build a database based on a single table. As you've probably guessed, a database can have more than one table. A database can also have no tables at all—instead, it can work with tables from other databases. You can even hook several tables together so they can share data and keep track of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you know by now, every FileMaker database starts with a table. And since fields and tables are so fundamental to a database, FileMaker asks you to create them as soon as you start. FileMaker assumes your needs are pretty simple, and starts right off asking what fields you want in this single table.

Choose File → New Database.

Up comes the New Database dialog box, just like the one in Figure 1-4. This time you're not going to start with a template. Instead, you're starting with a clean slate, so you can build a database that's all your own. (Can't you just feel the excitement?)
Click "Create a new empty file," and then click OK.

FileMaker asks you what to name your new database and where to put it.
Call this database People since that's what you're going to track.

In the Save As box (Mac OS X) or the "File name" box (Windows), type People. Then click Save.

FileMaker creates the new file and automatically adds the .fp7 file name extension to your database name.

That's not a typo. The extension .fp7 from FileMaker Pro 7 is the same for version 8. Good thing, too: It means people who still have version 7 can use your version 8 files just fine.

Just as promised, FileMaker asks you what fields you want—you see the Define Database dialog box shown in Figure 3-1. This little beauty is where you build and manage tables. There's a tab for the tables themselves, a tab for the fields in those tables, and a tab called Relationships, where you tell FileMaker how different tables work together. For now, you can simply ignore Tables and Relationships. Your one free table has already been created (it's called People, just like the database), and you're ready to give it some fields.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

At this point your database is simple, but entirely usable. In fact, you could stop reading right now, and you'd be able to build super-simple databases to solve all kinds of problems. But FileMaker's Define Fields dialog box has lots more settings to make your database even smarter, easier to use, and more consistent.

All your fields are completely empty when you create a new record. Often, but not always, that's what you want—a completely blank slate. With FileMaker, you have a handful of other possibilities. Suppose most of your clients are from North Dakota, for example. It would be nice if FileMaker could put ND in the State field for you—you can always change it later. In fact, this situation is so common that every field has a group of Auto-Enter settings where you can tell FileMaker exactly what to put in the field for you (Figure 3-6).

Figure 3-6: FileMaker has several options for automatically entering information into a field. Most happen just once: when the record is created. The Modification, "Calculated value," and "Looked-up value" options, however, can automatically enter information in fields at other times as well.

When you first create a field, all the Auto-Enter options are turned off, so the field stays empty. To turn one on, follow these steps:

Choose File → Define → Database.

The Define Database window appears.
Click the Fields tab. In the field list, select any field and then click Options.

Even quicker, just double-click the field. The Field Options dialog box emerges.
In this window, click the Auto-Enter tab

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You've just finished learning a lot about the various options and settings in FileMaker's Define Database window. At this point, it would be a good idea to take them for a test drive by adding some more functionality to your database. While you're at it, you'll get a chance to add a new field to an existing database (something you haven't done yet) and see how FileMaker responds—see Missing Fields below.

A private investigator has to deal with all kinds of characters. You've decided you might need a little help separating the good guys from the bad guys. Of course, Mom always said there's some good in everybody, so what you really need is a field that holds a Goodness Rating: George Costanza gets a zero, Mrs. Cleaver gets a five, and everybody else gets something in between. You want to make sure the rating is between zero and five, and you'd like it to be three unless you manually change it.

In the Define Database window, enter Goodness Rating in the Field Name box and, from the Type pop-up menu, pick Number. Then click Create.

You now have a field to hold the goodness rating.
Click the Options button, which has now become available, and then select the Auto-Enter tab.

The Auto-Enter options appear. You might have to switch to the Auto-Enter tab, if it isn't visible.
Turn on the Data checkbox. Then, in the text box next to it, type 3.

Remember, this option tells FileMaker to place some fixed piece of information in the field when you first create the record. Since you're assuming people are generally a three, it makes sense to let FileMaker automatically fill in that value for you.
Click the Validation tab, turn on "In range" and enter 0 to

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the last chapter you created your first database, and it really works. Unfortunately, it doesn't lookall that great. For example, the Street Address field is the same width as the State field, even though street addresses are usually much longer than state names. The Goodness Rating field is muchlonger than it needs to be. And unless you're a real minimalist, the whole thing just looks boring (see Figure 4-1).

Figure 4-1: This database works, but it could work better. What if you want to see lots of people at once? What if you want to print address labels for all these people? What if you want to arrange these fields in logical groups so they're easier to figure out? This database can't do any of those things, but if you give it a few new layouts, you can do all of them—and lots more.

There are other things to worry about as well. There's no good way to see lots of people at one time—in a nice list, for example. A list would also be handy for printing. As it stands, you have to print a whole page for each person in the database if you want a hard copy.

The FileMaker concept of layouts solves all these problems and more. While the Define Database window lets you define the structure of your database (its fields and tables), layouts let you design the look and feel.

If th

Table of Contents