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.
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.)
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.
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.
You're back in your database.
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.
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.
220.127.116.11. 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:
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.
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 18.104.22.168 for details on field indexes.
You've just told FileMaker to show your Goodness Descriptions in the value list.
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.
22.214.171.124. 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:
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.)
The selected database's name appears in the pop-up menu, and the second pop-up menu lists every value list in that database.
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:
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:
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help