6 Replies Latest reply on Jun 13, 2017 3:29 PM by philmodjunk

    conditional value list

    octane352

      Hello....Scratching my head on this one.

       

      I have a table that has US and Canada, State / Province and cities.

       

      Trying to have 3 drop downs (Country, State or Province and then city).

       

      Example: Want to first select the country (United States) with defines only USA states to be shown in "State" drop-down. Then based off the state it would only show cities in that state in the "City" field.

       

      Can't seem to get this to work.

       

      Any help is appreciated...

        • 2. Re: conditional value list
          philmodjunk

          When you get a "chain" of such value lists where the value in each field filters the value list of the next field, I call them "Hierarchical Conditional Value Lists".

           

          They typically use relationships that are not what people intuitively anticipate that they need so this is the most likely source of your trouble--the relationships that you've set up.

           

          Here is a teaching file on conditional value lists that include several working examples of Hierarchical Conditional Value lists. Each comes with very detailed documentation on how they were set up and how they work.

           

          Adventures in FileMaking #1--conditional value lists

           

          And please note the "geek busking" info on the introductory layout.

          1 of 1 people found this helpful
          • 3. Re: conditional value list
            octane352

            ok.....The examples i have seen have multiple tables to link each one to.

             

            I have one table that has all US/Canada, States/Provinces and there corresponding cities?

             

            Do I have to break everything up into separate tables and cross reference with PK and FK keys?

             

            Thought there would be an easier way to get these few drop downs to work ?

             

            All I want is pick country first (US or Can)....2nd drop down would show either US states or Canada provinces depending on what country was selected. Then in the cities drop down it would show only cities from the State or province selected in the previous drop down.

             

            Isn't there an easier way to do this?

            • 4. Re: conditional value list
              philmodjunk

              You can use a single table for this data, but the relationships will require multiple occurrences of that data. Or you use one of the ExecuteSQL driven examples for a conditional value list.

               

              A "Table occurrence" is what we call a box on the relationships graph. You can create multiple boxes that all represent the same table we call those different "occurrences" of the same table.

              • 5. Re: conditional value list
                octane352

                Hi Philmodjunk.... Are you able to give a small example of using one table as I described?

                 

                Really trying to get my head around this.

                • 6. Re: conditional value list
                  philmodjunk

                  I assume that:

                   

                  your table of values has at least three fields:

                  Country

                  StateOrProvince

                  City

                   

                  You have three fields with value list formats:

                   

                  Country: Non conditional value list with "use values from field" specifying TableOfValues::Country. No relationship required for this one.

                   

                  StateOrProvince: Conditional value list filtered by value selected in Country. Put an Occurrence of TableOfValues on your relationship graph with this relationship:

                   

                  LayoutTableOccurrence::Country = TableOfValues|Country::Country

                   

                  Your Use Values from field value list will specify TableOfValues|Country::StateOrProvince as the field, with "include only related values, starting from LayoutTableOccurrence specified.

                   

                  City: Conditional value list filtered by the StateOrProvince field value. Add a second occurrence of TableOfValues to your relationship graph:

                   

                  LayoutTableOccurrence::StateOrProvince = TableOfValues|SoP::StateOrProvince

                   

                  Your Use Value from Field value list will list values from TableOfValues|SoP::City with "include only related values, starting from LayoutTableOccurrence specified.

                   

                  Your relationship graph should look very similar to the one in the Adventure file for the first Hierarchical CVLs example.

                   

                  Note that the "include only..." setting is the same for both conditional value lists. In place of "LayoutTableOccurrence" use the name of the table occurrence selected for your layout, or the table occurrence of a portal on that layout if these value lists are used to edit portal records.

                   

                  It is also not a bad idea to use two field value lists here where the first field is an ID number or UUID, and the "name" field is the second field. This protects your data integrity should: a) you make a mistake entering the name into your table of values and don't catch the mistake until after you've selected that name in multiple records in your layout's table or b) the geographical entity's name is changed.

                   

                  b) may be very unlikely, but then my regular employer is located in a city once named differently from the name you would find on it's freeway exit signs today so even geographical entities do get renamed on very rare occasions....

                   

                  Tutorial: What are Table Occurrences?