5 Replies Latest reply on Sep 30, 2014 5:50 AM by MalcolmGarbett

    Drop-down list linked to a second drop-down list

    MalcolmGarbett

      Title

      Drop-down list linked to a second drop-down list

      Post

      Background. Me.

           New job, inherited a business-wide FileMaker deployment, created over many years by at least two people. In amongst my skillset I have databases and SQL and spend a lot of my working time in MS SQL. Through tutorials, training manuals and google been able to sort everything so far required in FM, but a little stuck on something, so thought I’d post here.

      Background: My question.

           There are a series of linked tables handling test/quality and there is an entry form for users to add “issues.” Each issue must be linked to a code.

           All of this is already set up and is working.

           Now, in order to better analyze issues, I have been asked to add a “top level” or “parent” code.

           To do this I have created a new table and linked the existing code table to it, entering data as appropriate. So every existing code now has a “parent.”

           Now I’m “stuck” on the form.

           Form has a box where the user clicks and the drop-down list displays all of the existing codes. The user selects the correct code and that updates the “issues” table with the correct code.

           What I need to do is: Have a drop-down box that lists the (new) “parent code”, and when the user selects the appropriate code a second drop-down list display the (existing) “child” codes that are linked to the “parent” code

           Example:

           Parent Code = AD (Administration)

           Child codes =

           RC (Route Card)

           NF No Fault

           PP Part Purchasing

           User would choose AD from the drop down, then is presented with a drop down of NF and PP, and this selection is what updates the “issues” table.

            

           Hopefully this makes sense and I haven’t gone overboard on detail.

        • 1. Re: Drop-down list linked to a second drop-down list
          philmodjunk

               Better too much detail (not the case here) than not enough. Then I have to dig out my crystal ball and the darn thing just isn't reliable...

               What you describe is called a conditional value list. I have a lot of links on the subject, what you describe is fully covered in the first 3 links--once of which is a demo file.

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Drop-down list linked to a second drop-down list
            MalcolmGarbett

                 Thanks Phil.

                 I'm out of the office for a few days but will try this out as soon as I'm back.

                 Really appreciate the fast response.

                  

            • 3. Re: Drop-down list linked to a second drop-down list
              MalcolmGarbett

                   Been reading the threads and feel the light shining through, but a question before I actually try anything…

                   I should have mentioned that I have a “test” database – a copy of the production dB – so I can make as many mistakes as I like in a safe environment.

                   Just to recap on current situation:

                   Table1 – which I populate with a new record from…

                   Table2 – which has the unique codes and descriptions.

                   One relationship Table1:code to Table2:code

                   Before this post I had created a third table – Table3 for the parent code.  Relationship with Table2 only.

                    

                   My question is, reading the examples, do I actually need this third table, or am I better to add new fields in Table1 and Table2?

                    

                   If I were doing this in SQL I would create the new table as described, but I’m not sure which is the best method in FileMaker?

                    

                   I don't actually need the parent code in Table1, but from the examples I don't think that would work.  That is, from a form looking up a code from Table3, with no relationship to Table1, that once selected offers a choice of codes from Table2 that, when selected, populates the field on Table1

                    

                   Being new to FM not just looking for the solution but also "best practice"

              • 4. Re: Drop-down list linked to a second drop-down list
                philmodjunk

                     Since my last post, I've released the first in what I hope will be a series of Freeware instructional database files. The first is on conditional value lists and covers all of the above plus additional variations--with working examples of each all in a single file and with very complete descriptions of how each is set up and how they work. You are welcome to visit my FaceBook page (see link below) where you can download a free copy.

                     With regards to you last question, in an SQL based DB or FileMaker, there is no simple rule to answer your question as you have to look at the data you would put in that added table and consider all the possible ramifications that may ensue if you do or do not use that table. (And FIleMaker's design is easily enough changed that if you set up your design one way and then realize that your other option is better, you can modify your design accordingly.)

                Caulkins Consulting, Home of Adventures In FileMaking

                • 5. Re: Drop-down list linked to a second drop-down list
                  MalcolmGarbett

                  Apologies for not coming back sooner.  Several projects on the go at the moment.

                  Thanks for all of the help.  My Hierarchical Conditional Value lists are now working.  :)