Setting up Field Controls

You flirted a bit with the Field/Control Setup dialog box in Chapter 4, when turning the Goodness Rating field into a pop-up menu (Section 3.4). Fields can be more than pop-up menus, though. The six main field styles are Edit Box, Drop-down List, Pop-up Menu, Checkbox Set, Radio Button Set, and Drop-down Calendar.

Each style (except Edit Box and Drop-down Calendar) lets you associate a value list with the field. Value lists limit the information you enter to a few, frequently used terms, numbers, dates or times. Using a value list, you can then easily enter a value from the value list into the field. You tell FileMaker how you want these choices presented, by selecting one of the field control styles.

Using a value listbased field control helps in two ways. First, you can more easily enter data into your database. If folks need to put a part number into a field, for example, they can just pick it from a list and avoid typing it exactly. Just as important, fields formatted this way help ensure consistency.

As your database grows, one of the toughest challenges you face is inconsistent data. For example, suppose you have two people entering addresses in your database. Cheryl is a fussbudget, and she always spells things out. Frank, on the other hand, is…ahem…keystroke efficient. He likes to abbreviate wherever possible. If all your customers are in central Arizona, you might wind up with some people in Phoenix and others in PHX; some in Tucson and others in Tcsn. Even worse, Frank, like most of his kind, can't even keep his own abbreviations consistent. He's got customers in Chnd, Chndlr, Chand, and (on a particularly bad day) Ch.

Even horrendous abbreviations may not cause a big problem to the human eye. After all, you can probably figure out where the person lives just by reading the City field. But what happens when you need to use FileMaker's find tools to find all the Chandler, AZ customers in your database? Searching for "Chandler" won't turn them all up. Instead, you have to try to think of all the different ways it could be abbreviated and search for each one. If you use a field formatted with a value list, however, you can make sure that everybody enters data the same way every time.

A value list has two parts. First, you decide which items appear in your value list. In this part, you're limiting the scope of the data your users can enter in a field. Creating value lists isn't technically a part of field controlyou can define them at any time, using the Define Value List dialog box (just like the one you first saw on Section 3.4). But often, you create new value lists as you format a field with controls, so youll learn how to define value lists first.

Once you've got your value list defined, you jump right back into Layout mode, and tell your field how to display the new value listas a drop-down menu, a pop-up list, a Checkbox Set, or radio buttons. Here's the low-down on defining value lists.

---

Tip: If you don't need to create value lists, or if you want to provide a calendar for entering dates into a field, you can skip the next section. Go straight to Section 6.2 and start setting up the field.

---

6.1.1. Creating Value Lists

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 fieldcalled Goodness Description, saywhere 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 images/U2192.jpg border=0> Value Lists) shows you every value list in your database. Here, you can add new lists, or modify those youve previously defined. You can use the "View by" pop-up menu to sort the list.

1. Open your People database.

   If you haven't been following along page-by-page, you can download a file with the fields listed in these steps from the Missing Manual Web site (Section 3.4.5). (If you prefer to keep working in the file you created in Chapter 2, define a new text field called Goodness Description.)

2. Choose File Define images/U2192.jpg border=0> Value Lists

   In the Define Value Lists dialog box, click New. Then, when the Edit Value List dialog box opens (Figure 6-2), give your new Value List a name.

   Since you already know that you're using this value list for the Goodness Description field, it's helpful to name it something that helps you remember that. You might just call it Goodness Description, but you can name it pretty much whatever you want.

3. In "Use Custom Values" box, type the values you want to appear in your list.

   Be sure to separate each item in your list with a line break (press Return at the end of each line). For your goodness scale, you might type: Angelic, Role Model, Reliable, Somewhat Unpleasant, Approach With Caution, and Whiff of Sulfur.

   If you want a menu divider in your list (between Reliable and Somewhat Unpleasant, perhaps), type a hyphen and press Return between those two values.

   ---

   Tip: Don't leave stray line breaks in the middle of your list, though. They create blank lines.

   ---

4. Click OK, and then OK again to close both dialog boxes.

   You're back in your database.

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.

---

Note: You can also define a value list right there in layout mode. Select a field, then choose Format Field/Control images/U2192.jpg border=0> Setup. Select Drop-down list, Pop-Up Menu, Checkbox Set, or Radio button set from the "Display as:" pop-up menu. Youll see the Define Value Lists dialog box and from there, you repeat steps 35 above.

---

6.1.2. Values from a 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).

---

Note: If you've already specified the field for this value list, and you want to change it later, return to the Edit Value List dialog box and click Specify.

---

POWER USERS' CLINIC
Using Tables for Value Lists

What if your database is so new that you don't have data in your fields yet? The "Use values from a field" method described on Section 6.1.2 doesn't do you much good. So FileMaker lets you create a table for the sole purpose of holding the values that should be in the list. Then you can put the data in the table just the way you need it.

But still, why create a whole new table just to hold a little ol' value list? Well, you may have people using your database who're perfectly capable of adding a record to a table, but don't have the training to make sense of the Edit Value List dialog box. A table lets them edit their own value lists while it protects your database's structure from mishaps.

To set up a value list table, you create a new table in your database and give it a main field to hold all the values that you want in your new value list. And to help your data entry people, you might even add a comments field that lets them make notes about the values they add. For example, "The value 'Angelic' is rarely appropriate in our business, but my sunny disposition requires me to include it here." Remember that one record in your table equals one item in your value list.

A word of warning: If your FileMaker database needs to share data with SQL or Oracle databases, be aware that a value list table may not translate perfectly.

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

6.1.2.1. Adding a second value field

Back on Section 6.1.1, you created a value list for Goodness Description, and it's helped your son with his data consistency, but you really prefer the numbering system. You can use the values in the Goodness Description field to remind your son that five is the highest goodness rating and zero is no goodness at all, and still get the number you want entered in the Goodness Rating field. The secret lies in the "Also display values from second field" option:

1. In the People database, choose File Define images/U2192.jpg border=0> Value Lists. In the Define Value Lists dialog box, click New, and then name the new value list A descriptive name like this helps you remember what kind of information the list holds.
2. Click "Use values from field."

   The Specify Fields for Value List dialog box pops up (Figure 6-4). You can see the name you just gave your value list in the box's title, which is really helpful if you get called away from your database in the middle of your creative work.

   

   Figure 6-4. A value list with two fields shows both field values side by side. Only the first value is actually part of the list: If you select "0 Whiff of Sulfur," only 0 goes in the field. The "Whiff of Sulfur" description just reminds you that the character you're rating has no character. (You learned how to make value lists appear in pop-up menus on Section 4.4.7.2.)

    
3. From the "Use values from field" pop-up menu, choose People. Then, in the field list, select Goodness Rating.

   Your new value list includes every item in the field's index. That is, the value list contains one instance of every value that you've entered in the Goodness Rating field. FileMaker sorts the list by the Goodness Rating fieldunless you tell it otherwise. If you'd rather sort the list by Goodness Description, just turn on the "Sort by second field" radio button. The list works the same way, but FileMaker sorts it alphabetically by Goodness Description instead. See Section 3.3.4.2 for details on field indexes.

4. Turn on "Also display values from second field." Then, in the field list (at right), select Goodness Description.

   You've just told FileMaker to show your Goodness Descriptions in the value list.

5. Click OK, then OK again, and then OK a third time to dismiss all the dialog boxes.

   You're now back in your database.

Sometimes you need to keep track of numerical datalike the Goodness Rating but you think people using your database would find it confusing to see all those numbers cluttering up the value list. Solution: Hide the numbers. In the Specify Field for Value List dialog box (Figure 6-4), turn on "Show values only from the second field." This option suppresses the Goodness Rating number, letting only the Goodness Description show. But behind the scenes, FileMaker still puts the Goodness Rating number in the Goodness Rating field.

---

Note: Showing values only from the second field is most useful in conjunction with a Pop-up Menu (Section 6.2.4), which always lets you see the contents of the second field. A Drop-down List, by contrast, displays the contents of the first field until you click it. Only then does it show the values of the second field like it's supposed to.

---

6.1.2.2. Value list from another file

If you have another database that already defines just the right value list, you don't have to recreate it here. For example, your People database can use a Zip code list containing all the Zip codes in your state from a different database. Instead of copying all that data into your People file, you can create a value list that simply goes and looks up the information in the other databaserefers to it, in other words. If you later change the original value list (when the post office changes Zip codes, say), People reflects that change as well.

To define this kind of value list, you have to hook two databases together using a file reference. A file reference doesn't require anything fancy, like a relationship (something you'll learn much more about in Chapter 8). Instead, the file reference just remembers where the other file is. When you click a field that uses a value list from another file, FileMaker can just hop over to the other file and grab what it needs without bugging you to find where that other file is stored. Here's how to reuse a list of information from another database:

1. In the Edit Value List dialog box, turn on "Use value list from another file."

   Now you can choose from a pop-up menu to the right of this radio button. (Right now the menu probably includes only two choices: Add File Reference and Define File References. If you've already added file references [Section 8.5.1], each database you've connected to also shows up in this menu. If the database you want is already listed, choose it and skip the next two steps.)

2. From the top pop-up menu, choose Add File Reference. When the Open File dialog box appears, select another database file, and then click Open.

   The selected database's name appears in the pop-up menu, and the second pop-up menu lists every value list in that database.

3. Select a value list from the second pop-up menu, and then click OK to seal the deal.

   FileMaker creates a reference to the value list you specified.

---

Note: The value list you pick can be any of the three types explained in this section. In fact, if you change a referenced value list's type, your new value list still works fine.

---

6.1.3. Editing Value Lists

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 images/U2192.jpg border=0> 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.

GEM IN THE ROUGH
Custom Sort Orders with Value Lists

Value lists play a part in so many database activities, it's a wonder they didn't call it ValueListMaker Pro. The Sort dialog box is another place Value lists show up. Suppose you have a database of clothing products, each with a size: Small, Medium, Large, X-Large. (If you want to follow along, you can find just such a database on the Missing Manuals Web site; see Section 3.4.5.) If you sort these records by size, they show up in alphabetical order:

Large

Medium

Small

X-Large

Chances are, that's not what you wantyou want them in order from smallest to largest, or largest to smallest. To arrange that, simply define a value list with custom values: each size in the proper order.

You can then tell FileMaker to sort the record by size, but to use the order of this value list instead of alphabetical order:

1. Choose Records Sort Records. You see the Sort Records dialog box.
2. From the Sort Order list, select Size. The radio buttons below the list are now clickable.
3. Choose the "Custom order based on value list" radio button. This option informs FileMaker to order the sizes according to a value list.
4. Select the Sizes value list from the pop-up menu. Here's where you pick which value list to use.
5. Click Move. The Size field moves to the Sort Order list. The little bar chart icon beside this field matches the "Custom order based on value list" icon.
6. Click Sort. FileMaker sorts the records according to size. This time, the records are in a good logical order