10 Replies Latest reply on Jun 5, 2014 2:39 PM by philmodjunk

    Multi Level Conditional Value Lists

    SlicChic

      Title

      Multi Level Conditional Value Lists

      Post

           I've created a layout where we can enrol students in a specific class.  The relationship is as such:

           Enrolments >--------Classes  (Classes as info such as name, type, start date, etc.)

           In enrolment, I have created value lists that relate to the class information.  What I'd like is to first choose "Program Type", and then "Start Year", "Start Month", etc....until I'm presented with only class(es) to choose from based on the previous options.  

           Then problem is that all the fields are only showing related records based on the initial selection in "Program Type".  (ie. selecting "Music A", "2014" is showing all classes of Music A, regardless of the year.  

           I've included a couple screen shots if that helps. 

      Value_List_Selection_Screen_copy.jpg

        • 1. Re: Multi Level Conditional Value Lists
          SlicChic
          /files/272622c4d2/Value_list-manage_list_copy.jpg 1374x339
          • 2. Re: Multi Level Conditional Value Lists
            philmodjunk

                 here are my links on conditional value lists.

                 One of the following threads specifically discuss Hierarchical conditional value lists--what you have requested here. I've included the rest just in case they also prove useful.

                 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.

            • 3. Re: Multi Level Conditional Value Lists
              SlicChic

                   Ok....so have the multiple table occurrences for Class Year, Classes Month,...etc.  And have related them to enrolment like this:

                   Enrolment::ProgramType = Classes::ProgramType

                   Enrolment::DateStartYear = ClassesByYear::DateStartYear

                   ...etc.

                   When making the value lists, I'm understanding that:

                   1- Program Type - Use values from "Classes::ProgramType" - all values

                   2- Year - Use values from "ClassesByYear::DateStartYear" - include only related values starting from "Enrolment"

                   3- Month - Use values from "ClassesByMonth::DateStartMonth" - include only related values starting from "ClassesByYear"

                   I'm having issues with this...now "DateStartYear" gives me a <no values defined>.  I must be missing something...

              • 4. Re: Multi Level Conditional Value Lists
                philmodjunk
                     

                          3- Month - Use values from "ClassesByMonth::DateStartMonth" - include only related values starting from "ClassesByYear"

                     Make that "Starting from Enrolment". Your relationships each evaluate from the same layout and thus the same table occurrence, the one specified for it in Layout Setup | Show Records from and this would be "Enrolment" in each case here.

                • 5. Re: Multi Level Conditional Value Lists
                  SlicChic

                       I must be missing something....I can choose the program type, but Start Year comes up with <no values defined>.

                       I've included a screen shot of my relationship graph.

                  • 6. Re: Multi Level Conditional Value Lists
                    philmodjunk

                         Make sure that Enrollment::ProgramType is formatted with your value list of Program types, not Classes::ProgramType

                         Make sure that Each of your value list formatted fields are from the Enrollment table occurrrence and not the field of exactly the same name from your related table.

                         Make sure that your match fields are the same data type in each pair.

                         One way to make a quick test to see if the relationship is working is to put portals to the different occurrences used here for your value lists. They should update to list the same records as those that provide values for your conditional value lists. If any stay empty after the controlling match field is updated with a value, then you know that relationship has a problem and you can take a closer look.

                         Once your value lists are all working, you can then remove these portals.

                    • 7. Re: Multi Level Conditional Value Lists
                      SlicChic

                           I'm ready to pull my hair out! I have put in the portals to test, and the best I can think is there is a relationship issue.  

                           I have all the TO's related to their match fields on 'enrolment'.  Everything is formatted front he appropriate value lists.  

                           If I do this (below), 'start year' come sup with <no values defined>.

                           1- Program Type - Use values from "ClassesByProgramType::ProgramType" - all values

                           2- Year - Use values from "ClassesByYear::DateStartYear" - include only related values starting from "Enrolment"

                           If I do this (below), 'start year' comes up with all the years, and isn't limited to the years from 'program type'.

                           1- Program Type - Use values from "ClassesByProgramType::ProgramType" - all values

                           2- Year - Use values from "ClassesByProgramType::DateStartYear" - include only related values starting from "Enrolment"

                      • 8. Re: Multi Level Conditional Value Lists
                        philmodjunk

                             And what do you see in the portal to ClassesByYear?

                        • 9. Re: Multi Level Conditional Value Lists
                          SlicChic

                               Nothing if I do the first setup.

                               2nd set up gives me all of the options for that year selected, not filtered at all by the program type choice. Here's a screen shot.  I've selected Music Pups for program type, then for Year Start I can choose 2012, or 2014.  The portal is showing me all the classes available for 2014, not just the Music pups for 2014.

                                

                          • 10. Re: Multi Level Conditional Value Lists
                            philmodjunk
                                 

                                      Nothing if I do the first setup.

                                 Which would seem to confirm that you have a relationship issue. Either the wrong fields are specified as match fields, the match fields have the wrong value or you are referring to the wrong table occurrence.