[Return to contents]

Limiting the options in a drop down list using an Alias table

Last updated: 24 Sep 2010

You can use an Alias database table to limit the list of options in one dropdown list by the option that have selected in another dropdown list. An "Alias" is when Approach opens two connections to the same database table which can then be used in separate joins.

You can download a working version of the following example at http://www.johnbrown.com.au/approach/state-city.exe

Create a database (in this example called St_City) containing the desired fields (in this example called State, City).

Then create an Alias of the database table (automatically named St_City:2, which the original database table becomes St_City:1. In v97 you create an alias by opening the Join dialog box, selecting the database table, and clicking the ALIAS button). Join the Alias and the original database tables by their City fields.

Then go into design mode, select the State field, make it a Field Box & List and select the 'Create list automatically from field data' option. Then select the City field and similarly make it a Field Box & List and select the 'Create list automatically from field data' option, but before you leave that dialog box click on the OPTIONS button, and select the options such that the dropdown list is created from the City field in the St_City:2, filtered on the State fields in St_City:1 and St_City:2.

Now go to Browse mode and start entering records. Once you have entered a few Cities in different states, you will find that once you have selected a state, the options in the city list are limited to those previously entered in that state. Pretty neat really!