Dropdown Filtering

[dcgall]

I'm trying to do some simple dropdown filtering and running into problems.

 

As and example:

I have an address database (DB1). I want the first dropdown to list the states. Click a state and the second dropdown shows all cities in that state (I would love to have this as a list, but I'm using 8.5 adv and don't seem to have options for a listbox). From this second list I want a 3rd list to show all names in that city (this 3rd list should show only unique records as well - e.g. Mr. Smith in CityXXX, not all the Smiths).

 

I have done:

State dropdown uses a defined list of only related records to DB1 (this works, I get only unique states). The 2nd dropdown (city) is a copy of DB1 with a relationship from STATE in DB1 to STATE in DB2. This all seems to work visually, but when I look back at the data, I see that records seem to be updated with strange data from other fields.

 

I would think this is a common approach to filtering but I can't me it work. Does anyone have an example of this? I really don't want to chop up my data in to various DB's because this is only the tip of a very large iceberg. thanks, much