13 Replies Latest reply on Apr 25, 2017 9:52 PM by philmodjunk

    Conditional Value Lists


      Having followed the steps found on this forum on building conditional value lists, I continue to have problems when trying to expand this function into multilevel lists.

      Below is the layout I'm using to test the lists.

      Grade Level and Artistic Process lists seem to work fine.

      Process Component and Core Art lists either show wrong values or no value at all.

      Value list 01.png

      The following shows the table structure I set up.

      Value list 02.png

      I would appreciate any feedback, thoughts, etc. on how to achieve the multilevel value lists I need.

      Hope this makes sense.

      Thank you for your time and consideration.

        • 1. Re: Conditional Value Lists

          Welcome algae592002.


          Your posts will typically get more views ( and responses ) if you post to the Discussions space instead of the Community Feedback space. Community Feedback is for issues related to the use of this website. Discussions space is for discussion about FileMaker and asking questions.


          Fortunately, TSPigeon or one of the other TS people are very good about moving posts for you.


          Happy File-making!!

          • 2. Re: Conditional Value Lists



            Thank you for your post!


            As jormond suggested, I'm going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!



            FileMaker, Inc.

            • 3. Re: Conditional Value Lists

              You appear to be setting up what I call a "hierarchical conditional value list" there are several very different approaches that can be used for that and I may be misunderstanding that you want them to be hierarchical (Value list A, filters list B and B filters list C...)


              It would help to show the value list set up dialogs for the ones that don't work for you.


              You might also compare what you have set up to the examples in this file:

              Adventures in FileMaking #2-enhanced value selection


              It has several working examples of Hierarchical CVLs along with detailed documentation on how they work and how they were set up. If you downloaded a copy of this some time in the past, I recommend downloading again as I've updated it to include some new examples--though none of the new ones are relevant to this particular question.

              • 4. Re: Conditional Value Lists


                I've been going through your suggestions and trying to figure out how they could apply to my database needs.

                So far nothing makes sense. Maybe I'm just pushing myself way over my head.

                In any case, yes you are correct: I'm wanting a hierarchical Conditional Value List.

                When you say "It would help to show the value list set up dialogs for the ones that don't work for you.", I'm not sure what you mean. ( Most likely cause I'm getting too dizzy with it all)

                Here are some things I think you are asking for:

                Value list 02.png

                Value list 03.png

                So if I choose a grade, then that chooses in the next list the Core Art number values that follow

                Value list 04.png

                Then these values determine a list of substandard numbers & letters

                Value list 05.png

                Does this help to understand what I need?

                The grade list and Core Art list seem to work, but beyond that it doesn't.

                Your examples so far are very complicated and I don't get very well how the data flow gets conditioned.

                • 5. Re: Conditional Value Lists

                  As I understand it, you want to select a "Grade" value and that should then set up a value list of "sub standard letters". Is that correct?


                  If so, you need two value lists where I currently see only one. The first value list is used to select a value used to filter the values for the second value list.


                  Next needed bit of info: On the layout where you've set up your value lists, on what table occurrence is it based? (A "table occurrence" is what we call the "boxes" in your relationship graph, such as "ICLP_Grade_List".) To determine that, open layout setup and tell me what you have selected in the "show records from" drop down. This name also appears to the right of "Table:" next to the layouts drop down when your layout is in layout mode. It's the relationship linking that table occurrence to the "starting from" table occurrence in your "specify fields for value list..." dialog that control any filtering of that list of values that takes place. Much of the time, your layout's table occurrence is the same as the "starting from" table occurrence that you specify for your value list. (Or sometimes it's that of a portal placed on the layout...)


                  And last bit of info, what is the name of the fields that you are formatting with these conditional value lists?


                  From what I see here, you need to open my file and work through the basic conditional value list example first before trying to set up anything Hierarchical.

                  • 6. Re: Conditional Value Lists

                    I select "grade", which gives me "Core Art" value, which in turn, gives me a "substandard number & letter" value.


                    Grade ---> Core Art ---> Substandard # & letter


                    In terms of layouts and tables, I am using a layout based on the "ICLP_Grade_List" table. In this table, I'm using the following fields for the CV lists:

                    b IMLPC_Grade

                    c MLPCS_CoreArt

                    c MLPCS_SubStandard_Number_Letter


                    I will review the Basic CVL again.


                    Also, just in case this is important, I'm using 2 tables:

                    y_BM_National Core Music Content Standards Main Menu & ICLP_Grade_List. This second table has 2 more table occurrences with slightly different names, as I show on the table chart.


                    Table y_BM_National Core Music Content Standards Main Menu contain the values that the CVL's use.

                    ICLP_Grade_List & it's copies get the values chosen from the first table.

                    This is probably self evident, but in any case I feel if restated, it may help. Sorry if it doesn't.

                    Thanks for your support.

                    • 7. Re: Conditional Value Lists

                      Knowing which table occurrence is the basis for your layout was a critical detail. Restating the other details is also helpful.


                      If you look at the relationships used for the Hierarchical CVL examples in the Adventure file, you'll see that they are not set up like you have set up here. Each of the table occurrences that are set up as value sources for a value list are all directly linked to the layout's table occurrence.


                      You only have one TO linked to the layout's TO and thus you don't have the relationships that you need. Note also that all but the last field formatted with a value list is one of the match fields used in a relationship to one of those TO's.


                      b IMLPC_Grade   ---> matches to Y_BM_National.... as you have set up

                      c MLPCS_CoreArt   ---> should match directly to ICLP Core Art List

                      c MLPCS_SubStandard_Number_Letter


                      Depending on the data in this table, the relationship to ICLP_Core_Art_List may also need to match by Grade.


                      The conditional value list used with c MLPCS_CoreArt would then list data from Y_BM_National...


                      The conditional value list used with c MLPCS_SubStandard_Number_Letter would list data from ICLP_Core_Art_List


                      Both of these value lists would specify: "Include Only related values, starting from ICLP_Grade_List


                      As I have pointed out in the Adventure File #1, There's a lot in common between a basic conditional value list and how you set up a portal. If you can set up a portal to list the values you want in your value list as rows in the portal, you have a relationship that can be be used for a conditional value list. The portal's TO would be the same as the one used as the source of values in the top of your Specify Fields for Value List dialog and your layout's TO would be the same that you'd use for your "starting from" TO.

                      • 8. Re: Conditional Value Lists

                        OK. I think I've discovered that I'm looking a t tables in reverse order, for starters.

                        Looking at your HCVL layout I find that I'm only using two tables, with multiple TO's of one of them (NCMS for short). NCMS provides the values I need to populate the value lists.

                        I alo see that you have PK_ID's for each category, which I haven't been able to identify in my NCMS table, except for one, which is a PK_ID for every single record in that table.

                        The other subcategories don't have unique ID's, unless in combination with other fields.

                        I've tried the second HCVL you show, but still can't get anything to work beyond the second set of VL's, which is accessible through the first relationship between TO's.

                        This feels very over my head and frustrating.

                        I appreciate your support.

                        • 9. Re: Conditional Value Lists

                          Try using Portals as a model for setting up the value lists. If you can get the correct values listed in the portal, the same relationship can be used for one of the conditional value lists.

                          • 10. Re: Conditional Value Lists

                            OK. I've tried emulating your set up for HCVL, drawn diagrams to understand data flow, tried setting up portals and nothing works.

                            The only difference between your set up and mine, is that I have 3 occurrences of the table where all the VL data comes from to the layout I work from, vs yours that has 3 separate tables that feed VL data to the layout you work from. Is this the problem?

                            What I've understood about the trigger scripts, is that they only avoid choosing more than one item per field, but that doesn't interfere with the CVL's themselves.

                            Am I wrong?

                            • 11. Re: Conditional Value Lists

                              I spelled out what you needed to do in reply #7. It tells you what you need to do. The relationships in your screen shot of #4 are not set up correctly for the CVL's that you are trying to create.

                              • 12. Re: Conditional Value Lists

                                Base Proyect


                                I’ve gotten to have CVL’s that seem to work well up to the last level.

                                I’ve also set up a portal that shows records for the first level CVL. The rest of the CVL’s do not seem to affect records in the portal.

                                Here’s the structure I have:

                                TO structure



                                This is the test layout I made with a portal.


                                VAPA Portal 2 Layout.png


                                If I choose a different grade, the content of the portal changes to reflect the grade level content. When I choose Artistic Process, Core Art, SubStandard # and finally the Letter, portal content does not change.



                                Here are more shots of my structure:


                                First level of CVL (Grade) Seems to work well.

                                VAPA Grade List.png



                                Second level of CVL (Art. Process) seems to work for individual record, but does not show change in portal.

                                VAPA Art Process List.png


                                Third level of CVL (Core Art) seems to work for individual record, but does not show change in portal.

                                VAPA Core Art List.png



                                Forth level of CVL (Sub Standard #) seems to work for individual record, but does not show change in portal.

                                VAPA Sub St. #.png


                                Finally, Fifth level of CVL (Sub Stand. letter) Does not work for individual record nor for the portal.

                                VAPA Sub St. Letter.png


                                I don’t find my error/mistake. I’ve reviewed the sample CVL’s but can’t figure out what I’m still doing wrong.

                                • 13. Re: Conditional Value Lists

                                  This is the test layout I made with a portal.

                                  And this would be a portal to what table occurrence? And the layout on which you placed this portal is based on Lesson Plans? I would guess that your portal is to y_BM_NCMS|Grade. And yes, changing the value of the other fields: ICLP_007..., ICLP_009... and ICLP_011... will not affect what appears in such a portal. Those fields aren't part of the relationship between LessonPlans and y_BM_NCMS|Grade. Thus the portal is doing what you set it up to do, even if it is not doing what you want it to do.


                                  If you want the values of all four fields to control what appears in the portal, you have two options:


                                  1) Create yet another relationship with another table occurrence and use all four fields as match fields to corresponding fields in the related table occurrence. Base your portal and the portal's fields on this 5th table occurrence. This is pretty simple to add to what you have here, but the portal will not show any records until you have selected values in all four fields in LessonPlans.


                                  2) Keep your portal as is, but add a portal filter to it that filters on all four values. This can produce a portal that updates with each new value selection to show fewer and fewer matching records in the portal, but the portal filter expression for 3 fields is pretty complex.


                                  ( IsEmpty ( ICLP_007_Artistic_Process01 ) Or

                                  ICLP_007_Artistic_Process01 = y_BM_NCMS|Grade::Artistic_Processes ) AND


                                  ( IsEmpty ( ICLP_009_Core_Art ) or

                                  ICLP_009_Core_Art = y_BM_NCMS|Grade::Core_Arts ) AND


                                  ( IsEmpty ( ICLP_011_Substandard_Number ) Or

                                  ICLP_011_Substandard_Number = y_BM_NCMS|Grade::Sub_Standard Item Number )


                                  You'll probably find that you need to commit records after each menu selection if you try this option. You can click a blank area of your layout to commit records or you can use a script trigger on the value list formatted fields to make this happen automatically.