How to make more flexible conditional value lists?
I'm working on a solution that uses conditional value lists to suggest information to speed up data entry.
What I have now:
It's easiest for me to describe the issue using addresses. I have a table (Dropdown_Data) full of all the different possible pieces of address data (street address, city, state, zip code) that the person doing data entry needs to choose from. Each record in this table has a data point, a unique primary key for that data point, a foreign key (to show what other data that primary key is related to), and a key to identify what type of data it is (is this a street address, or a zip code, etc.).
So in the layout for the Address_Entry table if someone enters the State field, they get a dropdown list of all the states in the Dropdown_Data table. Once they choose a state, the cities field shows a value list of all the cities in that state (all the cities whose foreign key matches the state's primary key). Once you choose a city, you choose from the zip codes in that city, and then choose a street address in that zip code. This cascading conditional value list works fine, as long as you fill in the fields in order.
Where I am stumped is how to make it non-hierarchical. So for example, what if you want to start by entering the zip code, so you enter that field and it shows all the zip codes. Once you choose one if you enter the street address field I want it to only show street addresses in that zip code. Or you could start by entering the city, and then the zip code value list will be limited to all the zip codes in that city, even while the street address and state fields are blank. Or you could just enter the street address and have all the other fields limited to ones that contain that street address.
Is this possible? I've thought of using some sort of workaround using multiple fields/value lists/relationships and conditional formatting, but that means I need a value list for each possible combination of filled in/empty fields. I've also looked at the Virtual Value List concept here: http://www.modularfilemaker.org/2013/05/virtual-value-list/ but I'm afraid i don't understand it well enough to know if it would solve my problem. Perhaps I need to structure my Dropdown_Data differently? The way it is organized now is based on advice from a non-filemaker developer who has a good understanding of database management, and we're trying to choose the most efficient method of structuring the data, maximizing performance while minimizing data replication.
Thank you for your help!