Back in Chapter 3, you saw how to use a value list to validate information entered in a field. On Section 3.4, for example, you created a simple value list to make sure folks can type values of only zero through five in the Goodness Rating field. Preventing database users from entering inappropriate information is all well and good, but why not be even more helpful? You use the true power of value lists when you make it easier for them to enter the correct information in the first place.

In this example, you can use a value list to translate those numbers into words anybody can understand without a legend. So, you define a new field—called Goodness Description, say—where you specify that a rating of five means “Angelic.” While you’re dedicated enough to type the full word out faithfully every single time, your teenaged son, who’s helping out around the office after school, types Angel when he feels energetic and slips into Ang towards quitting time.

You don’t have to adjust your son’s Goodness Rating downward. Open your People database and make a value list. Then your son can just click a pop-up menu instead of doing all that exhausting typing. The Define Value List dialog box is shown in Figure 6-1.

Figure 6-1. The Define Value Lists window (File → Define → Value Lists) shows you every value list in your database. Here, you can add new lists, or modify those you’ve previously defined. You can use the “View by” pop-up menu to sort the list.

Figure 6-2. The Edit Value List window is also where you create new lists. Start by giving it a good, descriptive name. Then choose one of the three basic value list flavors. If you have some specific ideas for what should go in the field, create a custom value list, as shown here. You can also use values from an existing field or a completely separate file.

See Section 6.2 for details on attaching your new value list to the Goodness Description field.

The "Use values from field” radio button tells FileMaker you want your value list to automatically include data that’s already in your database. It might sound strange, but this technique is pretty common. By the time you figure out that your assistant isn’t following the data entry rules you’ve set down, there’s already lots of data in your file. Put it to use by selecting the field’s data for use in a value list.

When you want to use values from a field, simply click that option, and the Specify Fields for Value List dialog box appears (Figure 6-3).

Figure 6-3. When you create a value list using values from a field, you get to pick which field to use. You must first select a table from the “Use values from first field” pop-up menu. (In this case you have only one table—People.) The list below it then shows every field in that table. Simply select the field you want.

In the simplest case, just pick one field from the “Use values from first field” list. The value list goes and checks your old friend, the field’s index, and then displays every value it finds in that field across all records. For example, if you choose the City field, the value list includes every city in your database.

If your short list of approved part numbers changes, you need a way to edit your custom value list to reflect the new company policy. Choose Define → Value Lists, select the value list you need to change, and then click Edit.

When you’re through editing a value list, just click OK. If you decide you don’t want to change it after all, click Cancel instead. Either way, you wind up back in the Define Value Lists window. Just like Define Fields, if you instead click Cancel, everything you’ve changed is tossed out. The value lists go back to the way they were before you opened Define Value Lists. FileMaker asks you if you’re sure first, since the Cancel button is precariously close to the OK button.

In addition to the New and Edit buttons, this Define Value Lists dialog box has a Duplicate button. It does just what you’d expect: creates a new value list that’s an exact copy of one you already have. You can live a long, healthy life without ever using this button, because once you create a value list in FileMaker, you can use the same list over and over in a multitude of fields and databases. (But isn’t it nice to know you have the option?)

The Delete button deletes the selected value list, after an appropriate warning. As usual, you can hold down the Shift (Option) key as you press Delete to skip the warning.

Most of the fields you’ve used so far have been Edit Boxes. These are the click and type variety that normal people call a text box. Distinctive as always, FileMaker has its own moniker.

When you select the Edit Box format, you have the choice of turning on the “Include vertical scroll bar” checkbox. You saw this option when you added a scroll bar to the Notes field on Section 4.4.8. If you anticipate that a field will hold lots of information, a scroll bar can be a good idea, as Figure 6-6 shows.

This field type looks just like an Edit Box. But when you click into the field, a list of available choices appears just below it (Figure 6-7).

The items in the list come from a value list (Section 3.4). When you pick the Drop-down List type, you get a “Display values from” pop-up menu. From this menu, choose the value list to use. You can also choose Define Value Lists to create a new one.

Figure 6-6. FileMaker fields can hold a lot of text. In the top window, the Text field has more information than can fit in the field boundaries. Adding a scroll bar lets you know there’s more. The bottom window shows what happens without a scroll bar. When clicked, the field expands to reveal its entire contents, covering anything below it. The field shrinks back down when you click out of it.

Figure 6-7. A Drop-down List gives you a scrolling list of choices to save you the trouble of typing the one you want. Just click an item in the list and FileMaker enters it into the field. You can also use the up and down arrow keys to select an item in the list. Finally, you can type the first few letters of an item’s name to select it. Once you have an item selected, press Enter (Return) to accept it.

Drop-down Lists look just like Edit Boxes until you click them or tab into the field. Then the list drops down for your data entry pleasure. But it could get annoying to have lists flashing at you just because you happen to be tabbing through the data in a record, so FileMaker lets you stop the list waving by choosing “Include arrow to show and hide list.” Now the list is a little more polite. When the field is active, the list doesn’t drop down until you click the arrow. If you make a choice from the list, the list disappears. Or, if you don’t want to enter anything into the field after all, just click the arrow and the list goes back home.

Finally, turn on “Include ‘Edit…' item to allow editing of value list” if you want your users to be able to easily modify the list of choices. When folks choose “Edit…” (always the last item in the list), the window in Figure 6-8 shows up so they can modify the value list.

Figure 6-8. You can include an Edit item in your Drop-down List. When folks choose it, this little Edit Value List box appears. From here, they can add new items, delete items, or edit items to the list-all by typing directly in the text box.

When you format a field as a Pop-up Menu, the look changes significantly, as shown in Figure 6-9. Rather than type into the field, your users must click the menu and make a choice.

Figure 6-9. Here you can see a Pop-up Menu as it appears on the layout and when it’s clicked. It works just like an ordinary pop-up menu: Just pick the item you want, and FileMaker puts it in the field.

Like Drop-down Lists, Pop-up Menus let mere mortals edit value lists, plus they up the ante by including an Other item. When your users pick “Other…” from the menu, they see the window in Figure 6-10. Entering a value in this window puts it in the field but doesn’t add it to the value list. Turn on “Include ‘Other…' item to allow entry of other values” to grant this power to the masses. (See the box on Section 6.2.4 for advice on the ramifications of letting your users to edit or bypass your carefully crafted value lists.)

Figure 6-10. If the Pop-up Menu, Checkbox Set, or Radio Button Set can’t cover all the bases, you can include an Other item. Selecting “Other…” opens a dialog box that you can type into freely.

Figure 6-11 shows a field formatted as a Checkbox Set. With this format, database users can simply click each item to turn it on or off. As they do, the data in the field changes to reflect the checked items.

Figure 6-11. When you format a field as a Checkbox Set, it looks like the Author field shown here. FileMaker adds one checkbox for each item in the value list, and arranges them to neatly fill the boundaries of the field. You can still apply borders, fills, and effects to a field like this—they affect the entire area the Checkbox Set covers.

Checkbox sets can include the Other item, just like a Drop-down List (Section 6.2.1). If someone enters a value that isn’t in the value list, FileMaker just turns on the Other checkbox to indicate the field has something more in it.

A Radio Button Set (Figure 6-12) works much like a Checkbox Set. The only distinction is that your users can turn on only one item. If they try to turn another item on, FileMaker turns off items as necessary.

Figure 6-12. This time the Author field is formatted as a Radio Button Set. Now you can simply click the right author to set the field.

Just as with Drop-down Lists and Checkbox Sets, you can add an Other or Edit item to the Radio Button Set. (See the box on Section 6.2.4 for more detail.)

In FileMaker, you can easily read date fields, and easily set them up. But actually typing dates into them is notoriously tricky. If you don’t get just the right combination of numbers and separators, FileMaker gets all huffy and tells you the value you’ve just typed isn’t valid. And if somebody swipes your desk calendar, how are you supposed to know what numbers to type in the first place? FileMaker Pro 8 can handle both these problems. It lets you give your date fields a nifty drop-down calendar where anyone can simply point and click to enter a date.

When you first format a field with a drop-down calendar, FileMaker doesn’t give you any visual feedback letting you know a calendar’s lurking there, waiting to drop down when you tab into the field. If you want to provide a visual clue, select “Include icon to show and hide calendar.” Then you see a teeny, tiny calendar at the right side of your field (see Figure 6-13). Tiny as it is, the calendar icon still takes up some room, so you might have to make the field a little wider to display the entire date plus the new icon.

The calendar itself is a little dynamo. To enter a date, click the Month Year display at the top and you see a pop-up menu that lets you jump to a specific month in the current calendar year.

Figure 6-13. The drop-down calendar makes entering dates a snap. It has some sweet controls, too. When the field’s empty, the current date is highlighted when the field drops down. Or, if there’s data in the field already, FileMaker highlights that date when the calendar appears.

Right-click anywhere on the calendar (⌘-click on the Mac), and the pop-up menu changes to read “Go to today.” The calendar closes and plunks the current date into the field.

You can also change the month with the right and left arrows at either side. The left arrow icon moves you backward in time (careful, don’t touch anything or you could change the course of human history!) and the right one moves you forward. The up and down arrow icons change the display of years. Finally, you can move the highlighted date with your keyboard’s arrow keys. Tap the down arrow key a few times and you see how fast time flies.

But if you get carried away playing the controls (is my birthday on a Friday in 2015?), the calendar’s footer always displays the current date. Just click that display to enter the current date, then drop the calendar down again. It reorients to today’s date.

Auto-complete is a strong ally both for database designers who care about data consistency and for data entry folk who hate to type. Unlike the other Field/Control field styles, you can apply this little beauty to a regular Edit Box. Once you’ve turned on the option to "Auto-complete using previously entered values,” the field gets ESP and tries to figure out what you want to enter. Where do these super-human powers come from? From that old friend, the field’s index (Section 3.3.4.2).

But Auto-complete behaves a little differently on Edit Boxes than it does on Drop-down Lists.

The differences are discussed in the following sections.

On Section 3.3.4.2, you learned about repeating fields, which let you put several values in one field, with a separate Edit Box for each value. Remember, when you put a repeating field on a layout, you get to decide how many times it shows up. Even if a field has 200 repetitions, you don’t have to show them all on the layout. You can elect to show just the first ten. Or just the last ten. Or numbers 37 through 118.

To control how repeating fields display, you use the same Field Format dialog box shown in Figure 6-5. This time, focus your attention at the bottom of this dialog box under Repetitions. Just enter the first repetition you want in the “Show repetitions” box, and the last one in the “through” box. You can’t show noncontiguous repetitions here—you have to pick a range with one beginning and one end. But try this technique instead. Put multiple copies of the field on the layout, but specify that each copy displays a different range of repeats. You’ll get much the same effect.

Once you’ve figured out which repetitions to show, you get to pick an orientation. Your choices are Horizontal and Vertical, and Figure 6-14 makes sense of them.

So far, you haven’t explored the right side of the Field/Control Setup dialog box, where there lives an incredibly powerful feature—the “Display data from” option. With this powerful tool, you can make one field display the contents of…another field. But why? While you ponder that Zen-like paradox, read the following example.

Say your layout has a series of similar fields—Email, Mail, and Phone—each of which indicates whether or not you have permission to contact a customer using that method. You format the Email field with a nice pop-up control and a value list with the custom values of Yes and No. You shorten it to just the right width and apply beautiful font formatting. Now, select Mail and Phone in turn and give them the same controls and text formatting. But that would take forever—twice!

Figure 6-14. This layout shows two repeating fields, one with horizontal orientation, and one with vertical. (See the box on Section 6.3.4 for further advice.) As you enter data in a repeating field, FileMaker numbers each repetition in a repeating field to let you know which one it is (bottom). The first repetition shows the full field name and—if there’s room—the repetition number in brackets.

Instead, do the following: Delete the Mail and Phone fields, then copy the Email field, and paste it where the Mail field used to be. Next, use the Field/Control Setup dialog box to tell the duplicated Email field to display data from Mail instead. Paste again, change this copy to display data from the Phone field, and with a few quick clicks, you’re done. You’ve just borrowed the formatting from one field to display the information from another. The moral of the story: Use “Display data from” to avoid doing work you’ve already done.

Sometimes you want to show a field value, but you don’t want any one changing it. If that’s your fancy, select the field in Layout mode and choose Format → Field Behavior. Then simply turn off the “In Browse mode” checkbox (next to “Allow field to be entered”;).

Just because you don’t want this field being modified on this layout doesn’t mean you don’t want to use it in a find request. If you leave “In Find mode” turned on, your users can still type into the field in Find mode. If you don’t want that either, turn off “In Find mode.” If you want to let people modify a field but not search in it, then turn on “In Browse mode” and turn off “In Find mode.” For example, turn off Find if the field isn’t indexed (Section 3.3.4.2) and you don’t want people stuck with slow finds.

If a field is formatted as an Edit Box or pop-up list, folks can click it to type a value. Normally, when they first click the field, a flashing insertion point marks the spot they clicked. If you prefer, you can instead have the field start out with all its content selected. For example, if the First Name field contains “Stacey,” a single click on the “c” selects the entire name. To get this behavior, turn on “Select entire contents of field on entry.”

Unless you work for the UN, you probably never use the “Set input method” setting, but for the sake of completeness here’s what it does:

In some languages, entering text isn’t as simple as in English. For example, Japanese has many more characters than fit comfortably on a keyboard. To get the job done, a Japanese typist must use an input method—the set of rules and software interfaces used to get text into the computer. Unfortunately there isn’t just one method, and FileMaker lets you pick which one you want. Normally, the “Synchronize with field’s font” option is selected. This option just means FileMaker automatically picks the input method based on the font you’ve selected. If you want to override this behavior and specify a hard-coded input method, turn on this checkbox and, from the pop-up menu, choose the input method.

Way back in Chapter 1 you learned to use the Tab key to move from field to field (Section 1.5.3). It turns out you can actually change this behavior too: You can designate the Tab key, Return key, or Enter key to jump to the next field. You can select any combination of these three keys, including none of them.

You might want to change these settings for two reasons. First, if you have a field that often needs tabs typed into it (like a field that holds an ingredient list—quantity [Tab] unit [Tab] ingredient [Return]…), it can be annoying to have to press Ctrl+Tab (Option-Tab) all the time. You can turn off “Tab key” in the Field Behavior dialog box and make typing tabs easier. Since field behaviors are set for each field, you can give the Ingredients field this behavior, and keep the normal tabbing behavior for all the other fields on the layout.

The second reason comes down to the fact that humans are funny creatures. People are smart enough to put a man on the moon, but for some reason they hate to learn new ways to work. If you’re creating a database for people whose old computer system used, for example, the Enter key to move between fields, you might decide to make FileMaker mimic that behavior to give their brains a break.

Sometimes you have something on a layout that you don’t want to print, such as a layout that’s designed to print over the top of preprinted invoice forms. When you view this layout in Browse mode, you want the appropriate boxes to show so your users know where to type. But when you print, you want only the field data.

You can tell FileMaker not to print any layout objects. First select the object (or objects). Then choose Format → Sliding/Printing. This brings up the Set Sliding/ Printing dialog box, which you learned about in the last chapter (Section 5.3.5.4). What you didn’t learn about is the “Do not print selected objects” checkbox at the bottom of this window. Turn this checkbox on and the selected objects show in Browse, Layout, and Find modes, but not in Preview mode and not when printed.

Occasionally your printed page needs to spread records across several columns. For example, when you print on address label sheets, the sheets you buy usually have two or three columns of labels on one page. Even when printing a list or detail layout, if your data’s narrow, you can save paper by printing two records side by side. How do you tell FileMaker what to do in special cases like this? Figure 6-19 illustrates the problem.

Figure 6-19. FileMaker draws a dashed line through your layout to show you where the columns land (bottom). It also covers every column but the first with a dotted pattern. This pattern is its way of saying, “Don’t expect anything you put here to print.” Since every column is identical, you just have to lay out the first. FileMaker repeats it for the rest. Bottom: With the layout set to print in three columns, everything comes together (top).

When you turn on column printing for a layout, FileMaker does some pretty crazy stuff to your layout in Layout mode, as Figure 6-19 shows.

The column setting has no effect on your layout in Browse or Find modes. But if you print or switch to Preview mode, you can see the effect. Instead of repeating the body part just vertically, FileMaker tiles the body part both horizontally and vertically so that it fills the page.

Every column has to be the same width (this makes sense because every column contains the same kind of information). FileMaker automatically sets the column width so that the columns perfectly divide the page. But it bases its assumptions about the size of the page on the settings in the Print Setup (Windows) or Page Setup (Mac OS X) dialog box at the time you turn on columns. If you later switch to a different paper size or orientation, you probably want to resize the columns. To do so, just drag the first (left-most) dashed line on the layout. When you finish, FileMaker makes every column the same width as the first one.

Lastly, FileMaker gives you two choices for the way it arranges records in the columns. Choose “Across first” in the Layout Setup dialog box if you want the second record to be at the top of the second column. Choose “Down first” if it should be the second item in the first column. The flow arrows on the icons in the Layout Setup dialog box (Figure 6-18) show how the data flows onto the printout.

Select a number field and choose Format → Number to control how the number inside the field is displayed. The Number Format dialog box has a lot of options, as you can see in Figure 6-20. You can use most of these in any combination, so the possibilities are vast.

Figure 6-20. FileMaker has four top-level number format settings: General Format, “Leave data formatted as entered,” Format as Boolean, and “Format as decimal.” If you choose either of the last two, you get still more choices. If you’re looking for the kind of number formatting options you’re accustomed to in a spreadsheet program, turn on “Format as decimal” and go to town. The sample at the bottom of the dialog box shows how a number looks with your settings applied.

Format as decimal

The most flexible setting is called “Format as decimal.” When you choose this option, you activate a host of new settings.

Fixed number of digits. Turn on “Fixed number of decimal digits” if you want to force every number to have the same number of decimal places. FileMaker also lets you say how many decimal places you want. If the number in the field doesn’t have a decimal part, FileMaker just fills in zeros after the decimal point.

Notation and symbols. The “Use notation” checkbox lets you add currency symbols, units, or other labels to your numbers:

• Choose Percent to turn the number into a percent value. FileMaker automatically multiplies the number by 100 and puts a percent sign after it. That way, your users can enter .1 for 10 percent, and you can later have your database multiply the percent value by another number. If the field held the value 10% instead, you wouldn’t be able to use that number to do any math.

• Choose one of the Currency options to format the number as a monetary value. When you do, you can supply an appropriate currency symbol as well. The four currency options in the pop-up menu have to do with where the symbol is placed, as explained in Figure 6-21.

Figure 6-21. FileMaker has four different currency options: Leading/Outside, Leading/Inside, Trailing/ Outside, and Trailing/Inside. Leading and Trailing determine on which side of the number the currency symbol should live. Inside and Outside matter only for negative numbers. As this picture shows, the currency symbol goes between the negative sign and the number with the Leading/Inside option, and before the negative sign with the Leading/Outside option. FileMaker also lets you put parentheses around negative numbers if you want. In this case, you get an Inside/Outside choice for trailing currency symbols too.

Do not display number if zero. Suppose you have a report with lots of numbers, where many of those numbers are zero. If you don’t enter anything in a number field, FileMaker displays a value of zero—what else? But not every record needs every field filled in. In fact, it’s quite common to have a lot of empty fields in financial databases, resulting in a teeming mass of meaningless zeros. So turn on the “Do not display number if zero” option, to make the zeros go away. Figure 6-22 shows a classic aging receivables report with and without zeros.

Figure 6-22. These two windows show the same report, an Aging Receivables report just like you might get from your accountant. In the top window, all those zeros just get in the way of the real data. The bottom window shows how the report looks when each field has its “Do not display number if zero” option turned on (in the Number Format dialog box).

WORKAROUND WORKSHOP: Currency-ish

The currency symbol is, in a sense, just an expression of units. It isn’t at all uncommon to want to put other unit labels on your numbers too. But FileMaker doesn’t have an option for this variation. It seems hopelessly in love with currency.

But don’t let the word currency in the “Use notation” popup menu get you down. Since you can type anything you want for the currency symbol, you’re welcome to use one of the currency options for any other kind of unit too. In this picture, the Cost field is formatted with normal currency settings. The Height field uses the Trailing/Outside option with " cm” as the currency symbol, to display a number with centimeter units. Weight uses a similar approach, with a currency symbol of " lbs.” (Note there’s a space before the abbreviations here, so they don’t crash into the field’s actual number.)

The Part Number field shows another creative use of the currency formatting options. This time, “PN/” is inserted before the number value, using the Leading/Outside option. Every one of these fields really has 233 in it, but through number formatting they show very differently.

The currency symbol box accepts only up to five characters –and that includes the leading space if you want one– so it isn’t perfect. But in many cases it gets the job done.

Separators. It’s no surprise to you that numbers use special symbols to separate their parts. For example, you usually see a decimal point between the whole and fractional parts of a number, and a comma after the thousands place. FileMaker uses whatever symbols your operating system dictates for these special purposes, but you’re free to override them if you want. For instance, you can use a space instead of a comma between each third digit.

In the "Decimal separator” box, type the character you want in place of a decimal point. The box accepts only a single character. FileMaker calls the comma between every third digit a “thousands separator” and you can opt to leave them out by turning on “No thousands separator.” Turn on “Use thousands separator” if you want them, and feel free to enter something other than a comma in the associated box.

Formatting negative values. The last thing you get to control with the “Format as decimal” option is the way FileMaker represents negative numbers. In the “Format as” pop-up menu, you can choose any one of six negative number formats:

• -1234 puts a negative sign before the number in the usual fashion.

• 1234- puts the sign on the end instead, if that’s the kind of thing that makes your socks go up and down.

• For that oh-so-financial look, choose (1234) instead. It puts negative numbers in parentheses.

• <1234> is similar, but it uses angle brackets instead of parentheses.

• If you’re an accountant, hold onto your hat. The 1234 CR option will make you feel right at home when crediting those accounts.

• The last choice, (1234 with a black triangle before it) puts that funny triangle before negative numbers. No idea what that’s all about. If you know, more power to you.

To draw more attention to a negative number, you can have FileMaker automatically color it. Turn on “Use color” and pick a color by clicking the little icon button to its right.

Formatting a number’s text. At the end of the day, a number is text too, really. It’s made up of numbers and symbols using some font, size, style, and color. As such, you can choose the Format → Text command with a number field selected. If you’re already in the Format Number dialog box and you want to format the text as well, the Text Format button serves as a shortcut to the Text Format dialog box.

Note

This window has two more choices—“Numeral type” and “Use kanji separator.” These options are specific to Japanese numbers and aren’t covered in this book.

The Date Format dialog box works a lot like its number-oriented brother. This time, though, you tell FileMaker how you want dates to look. This date could be the value in a date field, or the date part of a timestamp field. For example, the date at the top of a letter might look best spelled out, while the due date on a list of 25 invoices might best be served by an abbreviated numbers-only format. Figure 6-23 shows the Date Format dialog box (no surprises here: just choose Format → Date).

Figure 6-23. The Date Format dialog box gives you lots of control over how dates look. You get three primary choices (“Leave data formatted as entered,” “Format as,” and “Custom”), the last two with assorted settings. And you get a sample demonstrating your choices as well.

Custom

For the ultimate in control, choose Custom. When you do, the mysterious unlabelled boxes, buttons, and pop-up menus to the right become usable. By selecting different parts of a date from the pop-up menus and adding your own text as appropriate to the boxes, you tell FileMaker exactly how you want the date formatted. You would use this option when none of the Format As formats (Figure 6-25) are exactly right. For example, if you’re in the U.S., FileMaker suggests date formats that follow typical U.S. standards (month, then day, then year). But if people in England use your database, you might want to construct a custom format in line with their expectations (day, then month, then year).

Figure 6-24. The “Format as” pop-up menu gives you six canned date formats to pick from. If you choose either of the first two, you can pick something other than “/” to go between each number by typing it in the “Numeric separator” box (it’s covered by the menu in this picture). You can also add a leading zero or space to single-digit day and month numbers by picking from the Leading Characters area’s pop-up menus.

Figure 6-25. The Custom date format options (top picture) consist of a series of text boxes and four Date Value pop-up menus. You can put anything you want in the text boxes, and choose any of several common date-related pieces of information from the menus, as shown in Figure 6-24. FileMaker strings the text and date values together to produce the final result. The bottom picture shows three possible date formats, with the text and date values labeled.

When FileMaker formats a date with the Custom option turned on, it assembles the final date value piece by piece according to your specifications. Whatever you type in the Start Text box comes first, followed by the first date value. FileMaker then adds the text from the top Between Text box. Next comes another date value and more between text. This process continues until the last text box is added to the result. By mixing and matching text and date values, you can make a date look any way you want.

If you don’t want to use one of the date values, choose “<none>” from the relevant pop-up menu. Likewise, to skip a between text value, clear its text box. The pop-up menu buttons to the right of each text box give you a choice of common date-related symbols for easy picking. The have a “<none>” option as well, if you want to quickly clear the associated box.

In addition to “<none>”, the date value pop-up menus all have identical choices, divided into four sections, as shown in Figure 6-26.

Figure 6-26. The first group of three choices in this menu lets you show the day portion of the date. You can show the day-of-month number, or the day-of-week name as an abbreviation or full name. Next, you get the same three ways to display the month. If you’re so inclined, you can add a quarter to your date in two ways. Finally, you can pick between a two-digit and a four-digit year.

As with the “Format as” option, you can use the pop-up menus in the Leading Characters area of this window to tell FileMaker how to handle single-digit month and day numbers. If you want them left alone, choose “<none>.” To insert a leading space, choose Space, and for a leading zero, choose Zero.

Note

The “Display as” pop-up menu only applies to Japanese dates. And if you don’t recognize these characters, you probably don’t need to use this pop-up menu.

Compared to dates and numbers, formatting time values is a breeze—FileMaker gives you just a few simple choices. Figure 6-27 shows the Time Format dialog box (Format → Time).

Leave data formatted as entered

This time, “Leave data formatted as entered” really means it. The time value shows exactly as you typed it.

Figure 6-27. To have FileMaker show your time values exactly the way you type them, choose “Leave data formatted as entered.” If you want to standardize the display of time values, choose “Format as” instead, and then pick your options. Unsurprisingly, the Sample area shows you a sample time formatted according to your specifications.

Although FileMaker has a timestamp field (Section 3.2.6.5), there’s no Timestamp option in the Format menu. Remember that timestamp fields really contain two values: a date and a time. So, you use the Format → Date command to control how the date part of a timestamp looks, and the Format → Time command for the time portion. Like time values, you can also use Format → Number to control decimal places and points in the seconds part.

In order for a timestamp field to show your settings, though, you have to format both the date and time parts. If either is set to “Leave data formatted as entered,” the timestamp field just puts its hands over its ears and hums so it can’t hear you trying to format it. Once you enter both the date and the time formats, the timestamp field straightens up and follows your formatting instructions.

You do not have to set the number format if you don’t want to. If you don’t choose a number format, FileMaker leaves the decimal part of your time values formatted as your users enter them.

When your users want to put pictures or movies in a container field, they can use the Format → Graphic command to crop, scale, or otherwise control how it looks. If you’d like some control over how much formatting they can do, you can set some limits in the Graphic Format dialog box, as shown in (Figure 6-28).

Figure 6-28. With the Graphic Format dialog box you can tell FileMaker to shrink and/or enlarge a picture to fit the field boundaries, or to crop it—showing only what fits. You also get to decide where the picture lives inside the field, or what part gets cropped. If you don’t mind wildly skewed images, you can also tell FileMaker to stop keeping your pictures properly proportioned.

Basically, you’re taking control of what happens when folks use FileMaker’s usual image display commands. For example, when FileMaker shows a big picture in a container field, it normally shrinks it down so the whole thing fits inside the field. Smaller pictures are left at their normal size, and centered in the field. But this command isn’t the only way to work it. Here are all the settings you can bend to your wishes:

Scale

The Scale pop-up menu lets you decide how FileMaker should handle pictures that aren’t exactly the right size:

• Choose Crop if you want FileMaker to crop large pictures, showing only what fits. Figure 6-29 shows how this method works.

• Choose Reduce if you want FileMaker to shrink large pictures to fit, but leave small pictures alone. This setting is what you automatically get if you don’t change it yourself.

• Choose Enlarge to grow small pictures so they fit the field but are as big as possible. Large pictures get cropped.

• Choose Reduce or Enlarge if you want FileMaker to shrink big pictures and grow small ones. This setting ensures that every picture in the field (on each record) is the about the same size.

Figure 6-29. The back window shows a container field the way FileMaker creates them. The picture has been reduced to fit entirely in the field. In the front window, the field has been formatted to crop the picture. Now it shows only a small portion of the larger picture.

Alignment

If the picture is small and hasn’t been enlarged, the alignment pop-up menus control where in the field the picture appears. For example, choose Right from the Horizontal Alignment pop-up menu and Top from the Vertical Alignment pop-up menu to nestle the picture in the top-right corner of the field.

If a picture has been reduced or enlarged so that it fills the field, it might still be smaller than the field in one dimension. In this case, the alignment pop-up menus tell FileMaker where to put the picture along this dimension. Figure 6-30 shows this process in action.

Note

When FileMaker reduces or enlarges a picture, it keeps the picture’s aspect ratio the same. In other words, a picture that’s four inches by six inches may not be that size in the container field, but its height is 2/3rds of its width. If you’d rather FileMaker make the picture exactly the size of the container field, even if it means distorting it, turn on “Maintain original proportions.”

Figure 6-30. When FileMaker reduces or enlarges a picture whose aspect ratio doesn’t match the field, there’s space left over. The top-left window has a tall picture reduced and aligned in the center. Below it, you see the same picture aligned to the left. The top-right window has a wide picture center-aligned. Below this one, the same picture has been top-aligned. In every case, the picture stays the same size, but moves within the field.

You want to add a Go to List button to the detail layout in your People database, so open the database now and switch to the detail layout.

To add a button, first click the Button tool. Then draw the button on the layout as though you were drawing a rectangle. Figure 6-33 shows you where you might want to put it.

Figure 6-33. You can put your button anywhere you want, and make it any size you want to. But if it’s going to look like a button with the Go to List label, it should be close to the size of the button shown here.

When you release the mouse button, FileMaker immediately pops up the Button Setup dialog box (Figure 6-34).

Figure 6-34. The Button Setup dialog box shows up whenever you create a new button. You get to pick one action from the Button Action list. Most actions require a little configuration. When you pick one, its options appear in the Options area. You can come back to this box and make changes to an existing button: just double-click the button in Layout mode (or choose Format → Button) to open it.

You want this button to switch to a different layout whenever someone clicks it, so find Go to Layout in the action list and select it (it’s right under the boldface Navigation item). When you do, the Options area shows you a pop-up menu labeled Specify. You can pick any layout in your database from this menu. For this button, choose List.

If you’re picky about such things, you can even control whether your button has square corners or slightly rounded corners. Choose the “Rounded button” option for an elegant, classy look. (If you don’t make a choice, square is what you get.)

Something else changes when you select a button command: The “Change to a hand cursor over button” checkbox appears below the Button Style area. By turning this option on, you tell FileMaker to use a special pointing-finger cursor whenever someone mouses over the button. (The icon is similar to the one you usually see when you point to a link in a Web browser—the universal cue that an area is clickable.)

When you’re done making choices, click OK. You can now switch to Browse mode and give your button a try.

Buttons are handy, but they can be redundant. Often, there’s already something on your layout—like a picture or even a field—that would make a perfectly good button. For example, if you’ve got your company’s logo in the top-right corner of the layout, you can make it link to the main page of your Web site. A separate button reading “Go to Home Page” would be unnecessary clutter. (Incidentally, in this case you would use the Open URL button action; see Section 14.9.)

Here’s another common example: Now that you have a button that goes from the detail layout to the list layout (Section 5.3.2), you might want to give folks an easy way to get back where they came from. Rather than add a button to the already full body part on the list layout, why not turn the person’s name into a link of sorts? When you click the name, FileMaker shows you details on that person.

Turning one of a record’s existing fields into a button capitalizes on the fact that FileMaker always selects a record when you click it in list view. And when you switch layouts, it always stays on the same record no matter which layout you use. This time you’ve added a button to the works, but the original behavior is unchanged: Clicking the list switches records, and the button switches layouts.

Here’s how:

If you need a bunch of buttons on your layout, you can use the technique outlined on Section 4.4.6.2 on several buttons at once. Just select all the objects that you want to button-ize before you choose the Format → Button command. FileMaker automatically groups the objects and turns the entire group into a button. These objects now act like any other grouped objects: Anything you do to one in Layout mode happens to all of them. If you later ungroup a grouped button, FileMaker warns you and removes the button behavior as well.

If you have a layout object that’s already a button, and you don’t want it to be a button anymore, you need a way to turn off its button-ness. It’s easy to do but not very intuitive: Select the button and choose Format → Button Setup (or double-click the button). In the Button Action list (Figure 6-35), choose Do Nothing, and then click OK. Now the object isn’t a button anymore.

You can even remove the button action from the “real” buttons you create with the button tool (Section 4.4.4.2). That’s because, in reality, the button tool just creates a specially formatted text object and automatically turns it into a button. If you switch it to Do Nothing, you’re just left with a fancy text object. (You might want to deactivate a button when you’re troubleshooting your database…or for a little April Fool’s Day fun.)

The Specify Button window has dozens of available actions, as you can see at the left side in Figure 6-34. In the previous section, you created buttons for the Go to Layout action, but there are many more. As you scan through these commands, you probably notice that many of them repeat the same functions you find in File-Maker’s menus. That’s not just meaningless redundancy. By giving your databases buttons for lots of everyday commands, you can make FileMaker even easier to use than it already is. Design some icons (perhaps in your corporate colors), attach buttons to them, and you may find that you hardly have to train your colleagues at all, because the buttons and their labels help explain what they need to do to use the database.

Here’s a brief rundown on what some of these actions do.

Go to Layout is the action you used for the two buttons you created earlier in this chapter. It transports those who click it to another layout instantly. You get to pick which layout to visit (Section 6.7.2).

The Go to Record/Request/Page action lets you switch to the Next, Previous, First, or Last record in the found set. If you’re in Find mode, it navigates find requests instead. Despite the word “Page” in its name, it doesn’t navigate pages in Preview mode because buttons don’t work in Preview mode. (The reason it has this name is because you can navigate pages with a script in Preview mode. See Chapter 13 for more.)

Use Go to field if you want a button that delivers its clickers into a specific field. You get to pick the field by clicking the Options area’s Specify button. For example, you could create a button that says “Back to Square One” that places the cursor in the first field in the tab order. (If you don’t pick a field, this step goes to no field. In other words, it gets the user out of whatever field she’s in.)

The Options area also has a “Select/perform” checkbox. For text, number, date, time, and timestamp fields, turn on this option if you also want FileMaker to select the contents of the field, so folks can just start typing to replace it. For container fields, this option tells FileMaker to perform the field contents. In other words, play the movie or sound, open the file, and so forth. This way, you could have a button that opens up a file (Section 2.7.5), stored in a container field. For the Go to Field action to work, the target field must be on the layout.

The Go to Next Field and Go to Previous Field actions have no options at all. They simulate the Tab and Shift-Tab keystrokes, bouncing to the next or previous field in the tab order.

The last three actions in the Navigation section—Enter Browse Mode, Enter Find Mode, and Enter Preview Mode—do just what you’d expect. Want a Find button on your layout? Assign it the Enter Find Mode action and you’re done.

FileMaker has seven editing actions, whose names are largely self-explanatory:

The Undo action takes no options at all; it simply runs the normal Edit → Undo menu command. Select All also has no options. It works exactly like the Edit → Select All command.

Cut, Copy, Paste, and Clear all let you optionally specify the field to act on. If you don’t give a field, they act on the field your user is in when he clicks the button. You can also turn on “Select entire contents” for these actions if you want FileMaker to do a Select All before it cuts, copies, pastes, or clears. Again, if you don’t, it works on whatever the user has already selected.

Perform Find/Replace lets you pop up the Find/Replace dialog box. You can click Specify to preload the dialog box with your own choices. And you can turn on “Perform without dialog” if you want it to perform your user’s Find/Replace choices without showing the dialog box at all.

In the Fields section you find several useful and familiar commands.

Insert Text lets you stick predetermined text into a field. You can tell FileMaker which field to put it in, or leave “Go to target field” unchecked to have it land in the field the user’s currently in. You also get a “Select entire contents” checkbox if you want the inserted text to replace whatever’s in the field. Click the bottom Specify button to tell FileMaker what text to insert.

Several actions mimic the choices in the Insert menu (in Browse mode):

For each of these, you can optionally choose the field to use, and whether or not to select everything in the field before inserting.

Finally, the Replace Field Contents action runs the Records → Replace Field Contents command. This time, you get to pick the field to replace into if you want. You can also predetermine what goes in the Replace Field Contents dialog box. If you turn on “Perform without dialog,” your users never even see the dialog box. Instead, the replace just happens. You could use this action, for example, to add a Flag button to your layout. It would automatically flag every record in the found set by putting a Y in the same field on each of those records.

Use the New Record/Request action to make a button create a new record. To duplicate the current record instead, choose Duplicate Record/Request. To delete a record, use Delete Record/Request. This action has one option: “Perform without dialog.” Turn this action on if you want your button to delete the record with no warning message.

The Open Record/Request action locks the record without actually entering any fields. Chances are you never need to do this action from a button.

Use Revert Record/Request to mimic the Records → Revert Record menu command. This one has a “Perform without dialog” option as well.

To give folks an obvious Save button, assign it to the Commit Record/Request action. This action is just like clicking out of the record. It’s useful, though. If you have your layout set to show a “Do you want to save…” message whenever someone edits the record, you can add a Save button to the layout, using this command with the “Perform without dialog” option turned on. Whenever anyone clicks the button, FileMaker saves the record straightaway without the annoying dialog box.

If you have certain finds that you perform a lot, you can create buttons to run them directly. Use the Perform Find, Constrain Found Set, and Extend Found Set actions to get the job done. Each action lets you specify what find requests you want them to use. See Section 1.6.3.1 for an explanation of how to specify these requests manually.

For now, though, you can easily use them like this: First, perform the find you want the button to do (just go to Find mode, enter your criteria, and click Find). Then add the button to the layout and pick one of the find actions. Turn on “Specify find requests” and FileMaker shows you its complicated Specify Find Requests dialog box. But the box already has the requests you used last, so you can just click OK. Now your button performs the right find.

The last few actions work just like their counterparts in the Records menu:

Windows are, after all, where FileMaker displays your information, so it pays to be familiar with the button commands that control them. With these actions, you can adjust an open window so your data fits more comfortably, or even create a new window to navigate in while the form stays intact in the background.

The Files section has two important actions: Print Setup and Print. These give you control over the File → Print and File → Print Setup or File → Page Setup commands. Optionally, you can preselect the options in each dialog box, and use the “Perform without dialog” checkbox to perform the action with no user intervention. You could, for instance, have buttons that switch to landscape and portrait page layouts, and a third button to directly print the current record.

The Open Menu Item section gives you button access to several common dialog boxes. You can add them to a layout to save people a trip to the menu bar…especially useful if they don’t know where the menu bar is.

If you want a button that exits or quits FileMaker itself, use the very last action in the list: Exit Application.

If you want to add another set of fields to hold the work addresses of the contacts in your People database, you have to make the layout bigger in order to hold your new fields. But there are times when you just can’t stretch a layout any farther. Maybe some of your employees have smaller monitors and you don’t want them to have to scroll to see everything on the layout. Or you may feel you’ve already created a beautiful layout, with perfect proportions, and stretching it just to add a few more fields would ruin the design you worked so hard to create. Whatever the reason, if you don’t want to make a layout bigger, you can organize lots more fields by adding a tab panel (Figure 6-36)

Here’s how to use your existing detail layout (in the People database) to create a new layout using a three-tab panel:

There’s plenty of room on the Address panel for another set of address fields to hold a separate work address. You can define them now, if you like, and place them on the Address panel. (See Section 3.2 for a step-by-step tutorial on defining new fields.)

All the fields you’ve moved onto the tab panels are now firmly attached to those panels. If you move the panel, all the fields move along with it, so there’s no need for fancy selection techniques. Just drag the panel to your new location, and you’re golden. You can even copy the whole panel and paste it onto another layout. It works just as it did in the original location.

You can edit a tab panel by double-clicking it to summon the Tab Control Setup dialog box.

If you make a set of changes and realize you’ve made a mistake, just click the Cancel button. All changes you’ve made to the tab panel since you opened the dialog are swept away, even if you deleted some panels, added some and rearranged the furniture while you were in there. But once you click OK, your changes are written in stone—at least until you revisit the Tab Control Setup dialog box.

If you don’t want a tab panel after all, just select it and choose Edit → Clear or tap Delete. FileMaker warns you that it’s about to delete all unlocked objects on the tab panel as well (Figure 6-38). If that’s the way you want it, click OK. If you need to keep fields or objects on the tab panels, though, click Cancel, then move the keepers off the panel (way to the right of your layout, perhaps) for safekeeping.

Figure 6-38. If you try to delete a tab panel, FileMaker also deletes all the fields you’ve placed on the tabs, so it wants to make sure you really mean it. You can avoid this dialog box if you move all fields and buttons off each tab and onto another part of the layout first.

In Chapter 3, you learned that a summary field isn’t associated with records like the other field types (Section 3.3). Instead, summary fields gather up and process data from several records. Creating summary fields is much easier than describing what they do, as you can see from the following example.

You now have a field that helps you find out how many people are in the database. That piece of information is much more useful than it sounds; you’ll be using it to find out interesting things soon. It would also be nice to be able to summarize the Goodness Rating information. In this case, an average makes sense.

If you squeeze these two fields onto the detail layout and have a look in Browse mode, you see something altogether unimpressive: the total number of people in the database (the status area already tells you this information) and their average goodness rating (OK, this number is mildly impressive). If you don’t believe it, have a look at Figure 6-40.

This section began by talking about how useful it can be to summarize your data. It specifically mentions a report that shows sales by Zip code and genre for a music retailer. When you hear something like that, you’re sure to think that a Summary field should have some kind of a “by what” option. In other words, how do you tell that Total Sales field to summarize by Zip code? You don’t. There’s no such choice in the Options for Summary Field dialog box.

Figure 6-40. Showing your new summary fields on the detail layout isn’t terribly exciting. You don’t need a field to tell you how many records you have (the status area does this job just fine). Next you see the average goodness rating of all people in the database, which is only mildly interesting. For the real power, see “Sub- Summary Reporting” on Section 6.9.4.7.

A Summary field is, by nature, abstract. You tell it which field to summarize, and that’s about it. You might have one that counts people, another that totals orders, and a third that shows the standard deviation of the Height field.

The way to tell reports how to summarize your data is to design your layouts to produce the specific groupings you want. For example, you can make that Total Orders field display totals per customer on one layout, per product on another, and per month on a third. This field gives you tremendous flexibility when asking your database questions, without the need to define different fields for the totals on each different report. You can even make a new layout that slices your data a brand-new way without ever revisiting the Define Database window. Once you understand this process, defining summary fields is easy. You usually need just a handful at most.

To help you understand how to tailor a layout to a report’s best advantage, how about adding a new report to the People database that uses the new summary fields? Start by opening the database and switching to Layout mode.

First things first: This layout is a report, and as such it’s going to be printed. A lot. In this case, it makes sense to fix page margins so the printed output looks as nice as possible.

Since your layout now has fixed margins, you might want to see them in Layout mode so you have a feel for how things fit on the page. Just choose View → Page Margins.

Now it’s time to design the body part.

Next, you can add the summary fields to the layout. Since they summarize data, they really don’t belong in the body part, which repeats for every record on every printed page.

You need to show them only once—probably after all the data. There’s a part type that does exactly this: Trailing Grand Summary. When you start with a brand-new layout, it has a body, a header, and a footer, but it doesn’t automatically have any summary parts. You have to add them yourself:

Next up, you need to reduce the size of the body part so it shows a slim row, add a title and any lines you feel would look nice, and add a page number in the footer. You might also use separate title header and header parts so you can put a smaller header on every page after the first. You can see one way to format this report in Figure 6-42.

Figure 6-42. This is one way you could lay out your report. The Count of People and Average Goodness Rating fields are in a trailing grand summary part, which shows just below the body. Switch to Preview mode and click the last page to see the summary fields in action.

The Options for Summary Field dialog box has several choices. The first decision you need to make when defining a summary field is which radio button to turn on. Here’s what each choice does:

Total of

Use the "Total of” option to sum (add) number fields. For instance, you can make a summary field that shows total sales by turning on "Total of” and, in your Orders database, selecting the Order Amount field.

When you have this option selected, a “Running total” checkbox appears below the Available Fields list. When you use a summary field on one of those magical layouts (like the one you create on Section 6.9.4.7) that produces grouping—total sales by Zip code, total sales by state, and so on—you normally see just the totals for each group in the report. If you turn on “Running total,” FileMaker changes things slightly. Instead of individual totals for each group, the totals add up from group to group. Figure 6-43 shows just such a report.

Figure 6-43. This report shows summary fields in action. The Total Sales field uses the “Total of” option to add up sales numbers. The Running Total field is the same, but it has the “Running total” box turned on. As you can see, one shows totals for each individual state, while the other has a continually growing number. FileMaker adds each state’s totals to the previous total to arrive at the new number.

Average of

Obviously, the "Average of” choice calculates the average of the values in a number field. This time, you see a “Weighted average” checkbox. When you turn it on, another field list appears (see Figure 6-44). From this list, you choose the field by which to weight your average.

You would use a weighted average when the things you are averaging have an associated quantity. For example, suppose your database has a record for each product sale. It records which product was sold, how many were sold, and the unit price. If you want a summary field that calculates the average sale price, you probably want to turn on the “Weighted average” checkbox. Imagine you have these sales figures:

• You sold three laptop computers for $2,500 each.

• You sold 18 more laptops for $2,200 each.

• You sold a single laptop for $2,800.

If you use a simple average, FileMaker tells you the average sale price for laptops is $2,500 ($2,500 + $2,800 + $2,200, divided by three). But that’s not exactly right. You sold 18 of those laptops at just $2,200 each, but it only counts once in the calculation. In fact, you really sold 22 laptops in all, at three different price points. To calculate the correct average, you need to take quantities into consideration. In FileMaker, turn on the “Weighted average” checkbox and choose the Quantity field in the “Weighted by” list. Now it reports the correct average: $2,268.18.

Figure 6-44. When you turn on the “Weighted average” checkbox, FileMaker adds a second field list—Weighted by—to the window, where you can pick another number field. FileMaker averages the data in the first field, and weights each record’s input by the second field. If you’re not a statistician, see the explanation on the previous page.

Standard Deviation of

If you’re into statistics, use "Standard Deviation of.” It gives you a field that calculates its namesake for the selected number field. It also has a “by population” checkbox, which is a little oddly named (it probably should be called “of population”). Turn this checkbox on if your records represent the entire population in your particular domain, and FileMaker uses the formula for the standard deviation of a population. Turn it off to calculate the standard deviation of a sample.

Note

If none of this makes sense to you, then rest assured you don’t need to know what it means. If you’re cursed with a curious mind, Google define: standard deviation and go to town.

Figure 6-45. When you turn on the “Running count” and “Restart summary for each sorted group” options for a “Count of” summary field, you get a nifty report that counts up each group individually. This report was sorted by the City field, so the people records are grouped according to city.

Fraction of Total

Fraction of Total is the most complex summary option. It looks at the total for the group you’re summarizing, as well as the total for the entire database. It then reports what portion of the overall total the group represents, as a decimal number. If all your sales were in California, it would show 1. If California only accounted for 5 percent of your sales, on the other hand, it would say .05.

The “Subtotaled” checkbox that comes along with this option is also a little confusing. When you turn it on, FileMaker lets you pick another field from a list called “When sorted by.” The name of this list serves to inform you that you must sort the record by the selected field for this summary field to work. If you don’t sort the records yourself before you view the report, the field stays empty.

Note

That problem’s not as big as it might seem at first. As you’ll see on Section 6.9.5.1, you have to sort records to do a lot of things with summary fields.

FileMaker looks at the selected field, figures out which records have the same value in them as in the current record, and calculates the fraction based only on the total of those records. Figure 6-46 illustrates this concept.

Figure 6-46. This report shows sales figures by region as well as by state. The Total Sales column shows the total sales in each region or state. Percent of Total shows each region’s contribution to the total sales using the Fraction of Total summary type. The Percent of Region also uses Fraction of Total, with Subtotaled turned on and the Region field selected. Set up this way, it reports each state’s contribution to the total sales for the region, rather than the grand total.

The report you just created is nice, but it could be even nicer (imagine that). Wouldn’t it be fantastic to know how many people in your database live in each state? The average goodness rating per state would be pretty telling as well. Since there are 50 possible states and two summaries, you need 100 summary fields, right? Nope: Here’s where summary fields really start to shine. Since your new needs still involve just counting and averaging, you already have all the fields you need.

All you really need to do is sub-summarize your existing data by state. This process just involves adding a new part to your layout; a sub-summary part. You tell this part to use the State field, and any summary fields you put in it automatically operates on the correct subset of records.

Sub-summary parts

A sub-summary part shows summary information about a subset of the records in the found set. When you add a sub-summary part to a report, it acts like a cross between a trailing (or leading) grand summary and a body part. It doesn’t repeat for every record, but it does repeat: It shows once for each group of records.

POWER USERS’ CLINIC: Sub-Summary Power

You can actually put two sub-summary parts associated with the same field on a layout at the same time, as long as one’s set to print above the records and the other below.

You can also put several sub-summary parts on the same layout with each associated with a different field. This way you can create nested sub-groupings, each with their own summarization data. When you create them, you need to be extra careful with the sort order. For sub-summary parts that print above the records, you should sort the fields in the order of the sub-summary parts themselves. If your sub-summary parts are below the records, you should sort first by the field associated with the bottommost part, and then by the next one up, and so on.

If you don’t include a field in the sort order, FileMaker simply excludes the sub-summary part(s) for that field from the report. In this way, you can have a single layout that reports on data with different groupings based only on the sort order.

In the left window shown here, the records are sorted by state. In the right window, though, they’re sorted first by city, then by state. Even though each window shows the same layout, you see very different results.

You can even make a layout with sub-summary parts, but no body part. A layout set up like this just shows group summaries, without individual record details. For example, if you remove the body part from the report you just made, you see the people count and goodness rating for each state in a simple list, but you don’t see individual people.

Sub-summary parts are unique in two ways. First, they only show up in Preview mode. You never see a sub-summary part in Browse or Find modes. Second, they only show up when the data is sorted by the field they’re associated with.

You tell a sub-summary part how to group records by associating it with a single field in your database. The part shows up once for each unique value in that field (you get to decide if it comes before or after the records in its group). But the magic doesn’t stop there. If you put summary fields in a sub-summary part, the fields automatically summarize just the records in the associated group. In other words, you can put the same fields in different parts to see different results.

In the People database, for instance, you could add a sub-summary part to this report based on the State field and set to appear above the summarized records. The result would look something like Figure 6-47.

Figure 6-47. This version of your report includes a gray-colored subsummary part based on the State field. The part includes the State field itself, as well as the two summary fields. As you can see, the summary field values reflect just the group under them. In other words, they sub-summarize (clever, huh?).