FileMaker Pro 9: 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 (see ) or a completely blank slate (see ), 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 9009.) 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 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 .

You can download a sample database form the "Missing CD" page at

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 (see ) or a completely blank slate (see ), 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 9009.) 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 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 .

You can download a sample database form the "Missing CD" page at www.missingmanuals.com/cds.

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 , left. 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 : plain and simple. That's because you haven't added any content yet. In , 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 Starter Solutions: sample databases that you can fill in with your own data and even customize as you see fit. A Starter Solution, or 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 for the full catalog.

If you're the do-it-yourself type, see 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 can 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 create a new database from a template, you start launching FileMaker Pro (by using the Start → Programs menu in Windows, for example, or clicking its Dock icon on the Mac). Then choose File → New Database. The FileMaker Quick Start dialog box appears, as shown in .

Keyboard shortcut aficionados beware. If you're used to typing Ctrl+N (⌘-N) for a new document, you're in for a surprise. In FileMaker, that command makes a new record, not a new file. So when you really do want a new file, you'll have to resort to using the mouse and the menu.

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.

FileMaker Pro's standard templates make it easy to perform routine tasks found in typical businesses, schools, and homes. Just choose a template and get right to work. Chances are one of the 40 templates built into FileMaker is set up to manage the kind of information you're working with, so you waste no time on setup. Here's an overview of the nine basic template categories:

The Business - Finance set has the bean counters in mind. Track employee expenses and work hours (billable or otherwise), and generate purchase orders.
The Business - General category is an assortment of useful templates from document management to to-do lists. With this category, you can keep track of computer files, create a searchable and printable product catalog, and more.
If you need to keep track of real things—physical assets, customers, inventory, or just about anything else—use the Business - People & Assets category.
Managers and planners can start in the Business - Projects category, where they'll find databases to help plan events, track problems, schedule people and other resources, and manage tasks and projects.
See Education - Activities if you're organizing field trips or other events that require you to keep track of who's coming or who made a nice donation to the school boosters' fund.

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 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 less likely to be lost in case of a crash.

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

Figure 1-8: Specify the size of 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 is more likely to be lost than what FileMaker has 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, and 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.

When FileMaker looks for records, it expects them to match your find request exactly. For example, if you put "Mohinder" in the First Name field, and "Suresh" in the Last Name field, FileMaker only finds Mohinder Suresh. Mohinder Ashtapur won't cut it, and neither will Arup Suresh. FileMaker ignores any fields that are empty in your request, so it doesn't matter what Mohinder's title is because you didn't include a Title field in your find request.

Although FileMaker insists that every field in each found record match each field in your request, the data inside each field doesn't have to be a perfect match. The next chapter explains how FileMaker decides when a match is good enough, and how you can change its decision-making process.

Finding the right records can be a real balancing act. Be too specific and you may not find anything at all; be too vague and you'll find more than you can handle.

Bear these in mind when you enter your find requests:

Since FileMaker matches field values flexibly, you can often save typing and improve accuracy by being brief. For example, if you're looking for someone named "Rufus Xavier Sarsaparilla," just type ruf in the First Name field and sar in the Last Name field. Chances are you'll find the right guy, and you don't have to worry about spelling out the whole name.
Enter data only in fields you're sure you need. For example, even if you know Rufus lives in Montana, you don't have to put Montana in the State field in your find request.

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 want to look at those records again at some point. In fact, some may say that's the whole point of building a database. But first, you need to learn how to navigate 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/cds.

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 .
The slider is kind of a turbo-charged version of the Book icon. Instead of clicking once for each record, you can advance through a bunch of records by dragging. If you know approximately where you want to go (like "around halfway" or "about one-third from the end"), then the slider is the quickest way to get there. The slider is most handy when you want to get to the beginning or the end of the database. In that case, just drag the slider as far as it will go in either direction.

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. You'll get 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.

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 (see ) 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 with superpowers, and then suddenly get the paperwork you need to edit the detail on another record. Although you could enter data on the list layout, all the fields you need may not be available. Just create a new window and look up the record you want to edit (see ). Your super-power group is safe and sound in the first window. See the box about Record Locking on to see what problems could occur with this kind of data entry.

Figure 1-20: In this example, the window in the back shows several records in the database on the List View layout. But the window in the front shows one record in detail. You can flip through records in the front window in their detailed glory and leave your list intact in the background.

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 display only 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 $299. 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 FileMaker 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 (see ). 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 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 scroll bar to scroll through them. If you've used a spreadsheet program like Microsoft Excel, 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. You'll see what each view looks like as you learn about them on the following pages.

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 has view tabs built into the template (see ). You can even turn

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 (see ). 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 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 scroll bar to scroll through them. If you've used a spreadsheet program like Microsoft Excel, 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. You'll see what each view looks like as you learn about them on the following pages.

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 has view tabs built into the template (see ). 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 .)

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 ). Most of the database work you've done so far has been in form view, and you can see an example in . You can use form view when you have a lot of information to see about one record, or when you want to focus on just one record without being distracted by all the others.

Figure 2-1: This layout is set to form view right now. Looks familiar, huh? You've already spent some time in form view. Notice that you see only one record, although there are 25 in this database. You can page through records one by one by clicking the Book icon.

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 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.

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 (). That's all there is to it. FileMaker starts with every record in the table, 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 ). If all your requests have the Omit option turned on, 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 turned on, FileMaker goes through them in order. If the first request isn't an omit request, FileMaker finds all the matching records. If your first request is an omit, FileMaker finds every record that doesn't match and then moves on to the next request. That request may add new records to the found set, or remove records that are already there. In fact, a record could be found by one request, omitted by the next, and then found again by the third, so the record would be included in the found set since the last request added it. In the end, you're left with a found set in which every record matches at least one of your requests. Other records that would have matched may have instead been thrown out by omits.

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. (Make sure you're still in the Company Name field or the next step won't work.)
Choose Records → Replace Field Contents.

The Replace Field Contents dialog box appears (). 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. So, in the current record, type what you want to be in every record before you call up this dialog box. (You'll learn about serial numbers in and calculations in .)
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.

Don't confuse sorting with finding. When you sort, FileMaker doesn't change which records are in the found set. Instead, it simply rearranges the records you're already working on into a new order. For example, if you need a short-term loan, you can sort your contacts by annual income. FileMaker still shows all your contacts, but with Uncle Moneybags at the very top of the list.

The process always begins the same way: Choose Records → Sort Records. You'll see the Sort Records dialog box shown in , with all available fields listed on the left. You tell FileMaker how to sort by moving a field to the list on the right.

By "available," FileMaker means only the fields showing on the current Layout. If you want to see all the fields because the field you want to sort by isn't in the list, for example, choose the second item from the pop-up menu above the list–the one that starts with Current Table.

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 in which they should be sorted, and then click Sort. That's the essence of any sort, from the simple to the most complex.

Follow the steps below to sort by, say, Last Name:

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

The Sort Records dialog box () 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, and 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. 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, and the name is sprinkled in Company Name fields, Notes fields, and so on. 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 than what you may be familiar with. Luckily, the concepts are simple, as shown in . 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.

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 can 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 option 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 vertical alignment of the text within the field. The reason they're gray is that you can choose them only when you're designing your layouts (), not when you're entering 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

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 their 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 .

Whether you're using FileMaker as a full-blown asset management system, or you just want to keep a photo next to each employee record, FileMaker sees your container data as one of four distinct types: picture, QuickTime, sound, and file.

Figure 2-21: A container field can hold any file, but it can be smarter about some file types than others–so long as you tell it what you're giving it. 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.

A picture can be in any of more than a dozen formats, including the common kinds like JPEG, GIF, PNG, TIFF, PICT, and BMP. FileMaker will also accept Photoshop and PostScript (.eps) files. When you put a picture in a container field, the field displays the picture itself.

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 City field that reads "Chciago." 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. This method comes in handy when you've just typed a word and it doesn't quite look right.
To check the entire record, choose Edit → Spelling → Check Record.
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 (choose Records → Show All first if you want to check every record in the database).

No matter how many records you're checking, FileMaker opens the same Spelling dialog box shown in .

Figure 2-23: The spell checker found a typo—"quandrey." You can see the misspelled word in the Word box and underlined in red in the box at the bottom of the window. If FileMaker figures out the correct spelling, it selects it in the list under the Word box. And if you're the type who calculates your gas mileage every time you fuel up, you'll be delighted to discover that FileMaker keeps track of how many words you've spelled wrong so far, and tells you at the bottom of the window.

Even if, by the magic of planetary alignment, you have no misspellings, FileMaker still opens the Spelling dialog box. In this case, though, it says "Status: Finished Spelling" in small print in the middle of the busy window. You're supposed to spot this right away and know the program's done. Of course, if you're like most people, you'll stare blankly at the screen for 30 seconds trying to figure out what went wrong first. Save yourself the confusion: Check the Status line when the window first appears. If FileMaker is finished, click Done.

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. show the Windows version. You can see the Mac OS X version (which requires a little more digging) in .

Figure 2-25: FileMaker's Print dialog box gives you all the standard options, plus a little more. The Print pop-up menu (at the top in the dialog box in Windows) lets you tell FileMaker which records to print.

Figure 2-26: On Mac OS X, FileMaker's special print options are tucked away in a secret place. You have to choose FileMaker Pro or FileMaker Pro Advanced from this unnamed but very important pop-up menu. In this example, you can see the field styles you can pick from if you elect to print a "Blank record, showing fields."

Records being browsed tells FileMaker to print every record in the found set. 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 , 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 when you build your own database, it "thinks" the way you think and works the way your business works. You can design it to capture, organize, and display the information you need to know, when you need to know it—rather than bending your business (or life) to 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 may want to keep track of people—names, phone numbers, aliases, passport numbers, and so on. Someone in retail, however, may 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 may want to keep track of people—names, phone numbers, aliases, passport numbers, and so on. Someone in retail, however, may 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 create 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.

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 FileMaker Quick Start dialog box, just like the one back in . 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?)
Select the "Create empty database" option, 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, and 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 9. Good thing, too: It means people who still have versions 7 or 8 can use your version 9 files, although some of the new features won't work for them.

Just as promised, FileMaker asks you what fields you want—you see the Manage Database dialog box shown in . 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.

Figure 3-2: In the Manage Database dialog box (File → Manage → Database), the Fields tab is where you go to tell FileMaker about the fields in your database. The field list shows all the fields in the selected table.

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 the Fields tab in FileMaker's Manage Database 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 Phoenix, for example. It would be nice if FileMaker could put Phoenix in the City 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 (see ).

Figure 3-8: 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 → Manage → Database.

Or press Ctrl+Shift+D (⌘-Shift-D). The Manage 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.

Now you see all FileMaker's Auto-Enter options (), in all their glory.

Section 3.3.1.1: Creation values

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 learned a lot about the various options and settings in FileMaker's Manage 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 the box on .

A private investigator has to deal with all kinds of characters. You've decided you may 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 Manage Database dialog box, type Goodness Rating in the Field Name box and, from the Type pop-up menu, pick Number. Click Create.

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

The Auto-Enter options appear.
Click the Data checkbox, and then, in the text box next to it, type 3.

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 usually a three, it makes sense to let FileMaker fill in that value for you automatically, saving you time for more important tasks.
Click the Validation tab, turn on "In range," and type 0 to 5 for the range options.

This action tells FileMaker to expect goodness ratings between zero and five.
Uncheck "Allow user to override during data entry."

You don't want to allow values outside the range

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|

Table of Contents

Section 3.3.1.1: Creation values