Value lists are great tools, but they have some inherent limitations. The more potential entries, the less useful the value list. Fortunately, you can dynamically alter the value list display.Imagine youre creating a database of store branches for a nationally franchised company. They have offices in major cities in each state. Although the number of cities is limited, it still averages 15 per state, and some of them have similar names with different spellings (like Charleston and Charlestown), making the entries difficult to validate. To solve this problem, you could begin by creating a table with two fields: the states and their cities. Using this table as a value list, you can create a relationship between the state and city fields of the store branches. When you enter a state, only its cities appear as entry values.
To use a conditional value list
1 .Choose File > Define > Database.2 .When the Define Database dialog box appears, click the Relationships tab (Figure 3.45).
Figure 3.45. Click the Relationships tab to view the Relationships graph.
3 .In the Relationships tab, click the Specify Table button (Figure 3.46).
Figure 3.46. The Relationships graph visually displays all existing relationships between table occurrences, with lines drawn to show match field connections.
4 .The Specify Table dialog box will appear. From the File drop-down list, choose the file that holds your state and city table. In the table list below, select the table that contains your information (Figure 3.47). In our case, the table is called State List.
Figure 3.47. Select the file reference that contains the table with the fields that will become your value list.
The name of your table will be inserted in the Name of Table Occurrence box. You can change it to something else, but its a good idea to maintain a similar name for your relationship and value list so youll remember what the relationship is for.5 .Click OK to return to the Relationships tab. The table with the city and state information appears in the Relationships window with its fields listed.6 .Click the Create a relationship button (Figure 3.48).
Figure 3.48. Clicking the Create a Relationship button allows you to connect the newly added table occurrence to a field from another table occurrence.
7 .When the Edit Relationship dialog box appears, select the current table from the left drop-down list, and the State List table from the right drop-down list.8 .Choose one match field from each of the two lists, then click Add (Figure 3.49).
Figure 3.49. Select one field from the table occurrence on the left, and a field from the table occurrence on the right, whose contents will become the value list.
9 .Click OK, then click OK again to close the Define Database dialog box.10 .Choose File > Define > Value Lists.11 .When the Define Value Lists dialog box appears, click New.12 .In the Edit Value List dialog box, type a name for the value list and click "Use values from field."13 .When the Specify Fields dialog box appears, select the relationship you defined in step 4 from the drop-down list. Select the match field from the field list (Figure 3.50).
Figure 3.50. Be careful to specify the field thats dependent on the first input when setting up the value list.
14 .Click the "Include only related values" radio button, and select the current table from the "starting from" drop-down list (Figure 3.51).
Figure 3.51. By including only related values, the value list will only display a relevant subset.
15 .Click OK three times to exit the Define Value Lists dialog boxes.16 .Make sure youre in Layout mode (Control+L/Command+L), then go to the layout where the field you just selected is displayed.17 .Click on the field that will display the value list subset and choose Format > Field Format (Control+Alt+F/ Command+Option+F) (Figure 3.52).
Figure 3.52. To make your value list subset appear as a group of choices in the correct layout field, select the field, and then choose Format > Field Format.
18 .When the Field Format dialog box appears, click "Pop-up List" in the Style section and choose the value list you created from the "Display values from" drop-down list (Figure 3.53). Click OK.
Figure 3.53. Choose the value list in the Display values from drop-down list.
When you switch back to Browse mode and enter a value into the first field, the pop-up list will display only the subset of value options appropriate to that field. If there is no entry in the first field, the list in the second field will be blank. TipYou can use conditional value lists with pop-up menus, radio buttons, and check boxes.