8 Replies Latest reply on Dec 9, 2009 1:24 PM by philmodjunk

    Ideas for tricky conditional value lists

    mmccarty

      Title

      Ideas for tricky conditional value lists

      Post

      Hello,

        I've been working with FileMaker for little less than a year and have been building a database for my company as I learn.  My database tracks products.  Each product has two components lets just call those fields C1 and C2.  Now there is a standard list for both and restrictions on which components can be paired together.  I want to create pop up menus for C1 and C2 whose value lists change depending on the users selections.  For example, the user enters a new product and specifies C1, then the list for C2 changes to only those components that can be paired with C1.  I also want the restrict the list for C1 when the user specifies C2 first.

      If I could guarantee which component the user chooses first then I know how to create the conditional value lists.  Unfortunately the user enters C1 and C2 on the same layout and can do so in which ever order they want.  Another issue is that my users access the database through IWP, so script triggers are out and we have to manually commit the record.

       

      Any ideas on how to pull this off would be greatly appreciated!

       

        • 1. Re: Ideas for tricky conditional value lists
          ninja
            

          Howdy mmccarty,

           


          mmccarty wrote:

           

          If I could guarantee which component the user chooses first then I know how to create the conditional value lists.  Unfortunately the user enters C1 and C2 on the same layout and can do so in which ever order they want. 


           

           

          A simple, not very tricky way to do this would to put a validation on the C2 field that C1 is not empty.  This would then force C1 to be entered first.


          • 2. Re: Ideas for tricky conditional value lists
            mmccarty
               Thanks, but I don't want to restrict which field has to be entered first.  I want the user to choose either C1 or C2 and then the other value lists adjusts.
            • 3. Re: Ideas for tricky conditional value lists
              philmodjunk
                 As we discussed in another thread, this is for a web published database and you have to commit the record before the conditional value list updates. Since you can't use a script trigger to force the commit, we've got a problem here...
              • 4. Re: Ideas for tricky conditional value lists
                mmccarty
                  

                If we were in FileMaker and not IWP would it be possible to create these value list? 

                • 5. Re: Ideas for tricky conditional value lists
                  philmodjunk
                    

                  Don't see why not, with two mutally conditional value lists you have to work out some details carefully, but it can be done.

                   

                  I assume that if you drop down c1 and c2 is blank you want to see all c1 values?

                  I assume that if you drop down c2 and c1 is blank you want to see all c2 values?

                   

                  The trick here is to use calculation fields as your key fields in your relationships linking your main table to the table of values.

                   

                  Example:

                  c1Key  :  If ( IsEmpty (c1) ; "All" ; c1 )

                   

                  In your value table:

                  c1Key :   c1 & "¶All"

                   

                  Relationship for c2 values:

                  MainTable::c1Key = ValueTable::c1Key

                   

                  For the c1 value list, repeat this process, but substitute c2 for c1.

                  • 6. Re: Ideas for tricky conditional value lists
                    mmccarty
                       Thanks!  This works great in FileMaker.  I will keep working on a way to commit the records without script triggers.
                    • 7. Re: Ideas for tricky conditional value lists
                      mmccarty
                        

                      Figured out a way around the IWP commit problem:

                       

                      Make both fields C1 and C2 buttons on the original script.  Duplicate the layout twice, once where only C1 is a button and the other when C2 is a button.  Connect all the buttons to the following script with different parameters depending on if it is C1 or C2:

                      Commit Record/Request

                      If (Get(ScriptParameter)=1)

                      Go to Layout[layout where C1 is not a button]

                      Go to Field [C1]

                      ElseIF(Get(scriptParameter=2)

                      Go to Layout[ layout where C2 is not a button]

                      Go to Field [C2}

                      End

                      Open Record/Request

                       

                      The problem with making a field a button is that you can never modify the field.  But by switching to a new layout the user thinks they just entered the field.

                       

                      For example, the user makes a selection for C2 then they go to choose C1.  When they click C1, they actually click a button which commits the record and then sends them to a layout where C1 is just a field and not a button.

                      • 8. Re: Ideas for tricky conditional value lists
                        philmodjunk
                           Clever! Since I am on the bring of creating my first FMP 10 IWP database, I'll keep that trick in mind for my own projects. :smileywink: