Udostępnij za pośrednictwem

How to: relate two different drop down lists on the same form

  • Artykuł

Hint: use the Key column and Key value in the Lookup options.

Recently someone posed a question on our mail list,

" Can I relate two drop-down list boxes on the same form? Example: In the first one I have values given by hand: soccer, basketball. And in the second one, when I choose soccer in the first one, I just get the names of soccer teams and the same with basketball"

Ted Imboden supplies the answser below:

THE PROBLEM

Sometimes it can be very useful to use one drop-down list as a filter criteria, that determines the options displayed in another drop-down list. For this example, we want to have one drop-down list display a list of sports, and a second drop-down list show team names for the selected sport. Choosing "Soccer" in the first may present the options "Arsenal, Manchester United, Liverpool" in the second drop-down, whereas choosing "Hockey" in the first may present "Anaheim Ducks, Ottawa Senators, Pittsburgh Penguins".

THE SOLUTION

First, create a form containing the drop-down that has a pre-populated list of sports. This would be your “Sport Selector” field, and the field name should be obvious, like "Sport". Also, add a text field to this form, named "Team". The other field properties are left up to you to determine. Save the form as "Add Team".

Second, create a view that has both of these columns, and for the "Sport" field, open its Column Properties and add an Ascending and Descending sort to this column. Save the view as "All Teams".

Now, create a second form. Add the same exact drop-down to this form that you created for the "Add Team" form (i.e. don't create a new drop-down list field with the same options, just check the "Sport" field shown in the field list when you are editing the second form). Create another drop-down named "TeamSelector" and set the lookup options as follows:

By Key; Tool = This Tool; View = All Teams; Key Column=Sport; Key Value=Sport; check Key value is a field name;Lookup Column=Team; Unique; check No Cache;

Click the Apply button, set any other field properties that you'd like, and save the field. Save the form and name it "Select Team".

EXPLANATION

The first form essentially acts like a database; every Sport/Team record you add will become available as options in the second form. Suppose you added the following records using the "Add Team" form, as shown in the "All Teams" view:

SPORT TEAM
Soccer Arsenal
Soccer Manchester United
Hockey Anaheim Ducks
Hockey Ottawa Senators
Baseball New York Yankees

 

We want to use the unique choices in the "Sport" column (Soccer, Hockey, Baseball) to determine the options presented in the "TeamSelector" drop-down list. Therefore, the "Sport" field will act as the key, and the "Team" field will provide the options. To accomplish this, we need a By Key lookup, that uses the "All Teams" view, and the "Sport" column as the Key Column. Setting the Key Value to "Sport" and checking Key value is a field name instructs the lookup to use the value of the "Sport" field as the key. (An alternative would be to limit the choices to Soccer teams, which you'd accomplish by setting the Key Value to "Soccer" and leaving Key value is a field name unchecked.) Finally, we want the options in the "TeamSelector" drop-down to be values from the "Team" column, so we set Lookup Column to "Team". Selecting Unique removes duplicates (in case you added Arsenal as a Soccer team twice), and checking No cache means that if you add additional teams after initially setting the lookup, they will be included.

NOTE: Only in a By Key lookup do you need to add a sort to the key column.

 

Many thanks to Ted Imboden for writing up this solution! clip_image001

--abbott

Link to this article: https://blogs.technet.com/groove/archive/2007/06/12/how-to-relate-two-different-drop-down-lists-on-the-same-form.aspx

Comments