3 Replies Latest reply on Oct 29, 2013 1:49 PM by philmodjunk

    How to make more flexible conditional value lists?

    LD

      Title

      How to make more flexible conditional value lists?

      Post

           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.

      The problem:

           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!

        • 1. Re: How to make more flexible conditional value lists?
          philmodjunk

               I don't see any advantage to a single combined table of the drop down data. One table for each address component, state, city, zip would seem to be an easy way to simplify the relationships used.

               That said, what you have described is definitely a challenge. I'd be inclined to play with OnObjectEnter script triggers that pop up a new window instead of a value list. A list of records in that window or in a portal on that window could then be clicked to select a value. With that approach, the script performed by the script trigger can use what values are or are not present in the different fields to determine which layout and which data on that layout to pop up in the floating dialog.

          • 2. Re: How to make more flexible conditional value lists?
            LD

                 Hi Phil,

                 Thank you for your response. I ended up using OnObjectEnter to switch between visually identical layouts that have different fields/value lists. It seems a bit overly elaborate but it achieves the right behavior more or less.

                 The reason I have all the dropdown data in the same table is because it allows me to use scripts that change the value a relationship is based on, which then allow me to use one value list in multiple contexts. Overall it reduces the number of relationships I need, although I agree it might be logical to shift back to different tables for each address component (which is what I started with).

            • 3. Re: How to make more flexible conditional value lists?
              philmodjunk

                   because it allows me to use scripts that change the value a relationship is based on, which then allow me to use one value list in multiple contexts.

                   You can use one value list in multiple contexts without needing to use this approach. To me, the added complexity doesn't justify the reduction in relationships--especially when ExecuteSQL is also available to reduce the number of needed table occurrences in the relationship graph.