5 Replies Latest reply on May 12, 2017 9:46 AM by piaccounting

    CONDITIONAL DROPDOWN VALUE LIST

    piaccounting

      Hello all

       

      Is it possible to programatically determine which values list should be used as a dropdown list?

       

      I have three fields (A, B and C) each of which I want a dropdown list presented to the user.  However I want more flexibility than field A being a full list; field B being determined by what was chosen in field A and then field C being determined by what was chosen in both field A and B.

       

      What I would like is the user to be able to choose any field A, B or C. On choosing a field the list made available to them will depend upon what is already selected in the two other fields (if indeed anything has been selected in either of them).

       

      If I have calculated correctly there are twelve possible permutations, which suggests a Case statement might be a solution.  My thoughts are that on selecting a field I check the contents of the other two fields and then determine which value list to present. I am also wondering whether the value list could be generated with an SQL statement.

       

      If anyone could advise as to whether this is possible and pointers to where it is achieved. For example I cannot see an option to generate an SQL statement from within the “Manage Value List for” dialogue box therefore presume it will go in a script which is called when the field is selected.

       

      Many thanks

       

      Dave

        • 1. Re: CONDITIONAL DROPDOWN VALUE LIST
          SteveMartino

          I don't think conditional value lists are going to work like that for you.  Remember, once you pick a value in the first dropdown, the second dropdown will only every have the values related to the first dropdown, and so on.  So after a user goes thru A, then B, then C, you'll never be able to get anything different for B or C until you pick a different value in A.

          • 2. Re: CONDITIONAL DROPDOWN VALUE LIST
            philmodjunk
            I don't think conditional value lists are going to work like that for you.

            Actually they can. See the "Hierarchical Conditional Value Lists II" example found in this teaching file:

             

            Adventures in FileMaking #1--conditional value lists

             

            Each example in this file comes with detailed documentation on how they were set up and how they work. And please note the "geek busking" info on the intro layout.

            1 of 1 people found this helpful
            • 3. Re: CONDITIONAL DROPDOWN VALUE LIST
              piaccounting

              Hi philmodjunk

              Yes apologies I had forgotten that I had downloaded that; especially as there are some very good example in there!

              I trust I suitably responded to the "geek busking" info at the time .

               

              I think the "Execute SQL Controlled Value List" could be the one closest to my needs but I will explore in more depth.

              As it might not be obvious to me - where should the ExecuteSQL statement go. Is it part of a script or calculation field - or I guess it depends ?

               

              Thank you.

               

              Dave

              • 4. Re: CONDITIONAL DROPDOWN VALUE LIST
                philmodjunk

                I didn't recommend the ExecuteSQL method here. You might try the non SQL version first to see how it works for you.

                 

                ExecuteSQL is used in that file to produce lists of IDs separated by a return.

                 

                I used unstored calculation fields with the ExecuteSQL plus other logic functions to handle the cases where filter fields are empty. A script could be used to set a text field to the results of this query if you want as long as you can get the scripts to perform in a timely fashion.

                 

                Also, the SQL examples in this file are "simple but brittle" versions that I no longer use (but which are easier for the newbie to understand). If you want to see how I work with this function today, check out Button Bars as Value Lists example and its Flexible SQL tab in the Adventures #2 file.

                • 5. Re: CONDITIONAL DROPDOWN VALUE LIST
                  piaccounting

                  Thanks philmodjunk

                   

                  You've pointed me in the right direction - which is what I wanted. I have built myself a working example starting with a named value list associated with each dropdown field. I will start introducing the "conditions" and reference back to your examples, both non-SQL and possibly SQL examples as I know the "conditions" will become a little more complex. It might take me a little while but it should prove invaluable for the training.

                   

                  Thanks again.

                  Dave