6 Replies Latest reply on Dec 29, 2009 5:09 PM by PROFT

    Nested conditional value list



      Nested conditional value list


      I'm trying to construct a database to use with an index I'm creating.  There are other fields but the relevant ones for this question are Book, Chapter and Verse.  


      I want the folks entering the data to be able to input the book and then the chapter and verse fields would be dynamic.  Given the selection of book, the pop up would have the correct chapters available for selection.  Then given the selection of book and chapter, the verse pop up would have the correct verses available for selection. 


      I set tried to adapt the instructions found in the How to Create a Condition Value List (#5833) but am stuck on the second one.  I correctly get the first conditional list, the chapter listing.  The second one, the verses, however, displays all the verses and does not correctly limit based on the chapter.  


      Any help would be appreciated! 

        • 1. Re: Nested conditional value list

          I helped another user with this issue. In the other thread, I finally figured out that we had some relationships set up wrong. You'll need to pay close attention to your relationships and you'll likely have to set up a relationship just to enable the "nested" conditional value lists.


          For 2 conditional value lists, you'll need something like this structure:

          Tables: ParentTable, Chapters, Verses


          ParentTable::BookID = Chapters::BookID


          ParentTable::BookID = Verses::BookID AND

          ParentTable::Verse = Verses::Verse


          For a list of chapters for a given book, specify a list of related values from Chapters starting from ParentTable.

          For a list of verses for a selected chapter and book, specify a list of related values starting from Verses.

          • 2. Re: Nested conditional value list

            Thanks for the help.  I'm still not quite settled.  I set up three tables:  Index, Chapter data, Verse Data.  I have the following relationship established:

            Index::Book = Chapter data::Book

            Index::Book = Verse Data::Book AND Index::Verse=Verse Data::Verse


            My value lists are as follows:


            Index-Book value list = a custom value list with all the book names

            Index-Chapter value list - Use values from field, First Field = Chapter Data::chapter, Include only related values starting from Index

            Index-Verse value list - Use values from field, First Field = Verse Data::Verse,


            Here's where I'm stuck.  The Include only related values starting from does not allow me to select Verse Data.  I can select Chapter Data or Index.  For both of these options I get "no values defined" in the field when trying to input a record.


            I must be close, but no cigar yet! 

            • 3. Re: Nested conditional value list

              What you've set up looks right so we've got a detail wrong somewheres.


              There are data design issues in the specific fields you've defined and in your first value list, but they shouldn't keep what you've set up from working.


              First, Index should be the "starting from" table occurrence name in the last value list you describe.


              Let's check the fields out and see if there's an issue there...


              What fields and from what table are you formatting with these value lists.


              You should have some fields in Index that work like this:


              Index::Book (name of book)

              Index::Chapter (format with first conditional value list)

              Index::Verse (format with second conditional value list)


              Is this what you have?


              If so, try this test:

              Select a book.

              Select a chapter.

              click on a blank area of the screen

              Try to select a verse.


              The third step forces Filemaker to commit the data from the first two fields and I'm having you do this to see if that's the issue.



              • 4. Re: Nested conditional value list

                You've helped me think this through and I might just have it figured out.  It's in the relationships.   


                Here's the scenario I had: 


                Index::Book - a text field formatted as a drop down listing from Custom values

                Index::Chapter - a number field formatted as a pop up From Field.  The first values used are from the Chapter Data::Chapter field and the related values start from Index

                Index::Verse - a number field formatted as a pop up From Field.  The first values used are from the Verse Data::Verse field and the related values start from Index.


                The chapter value list shows correctly, but the verse value list did not.  In the course of my experimenting, I had found that if I delete the Index::Verse = Verse Data::Verse relationship, all the verses for the given book show.  This seems to be the opposite of what's happening now.   I just tried changing the relationship configuration and the following seems to work.  


                Index::Book = Chapter Data::Book

                Index::Book = Verse Data::Book AND Index::Chapter = Verse Data::Chapter 


                Now the verses show correctly.  I have been using just a small number of records in the Chapter and Verse data tables until I got this all sorted out. With an apparent success, I made sure to add duplicate chapter arrangements between multiple books (e.g. multiple books with chapter 3, but with different verses in chapter 3.)  Now I get just the verses from the chapter and the verses match those belonging to the selected book.  Both criteria work now!


                Thanks for you help.   

                • 5. Re: Nested conditional value list

                  Now that you've got it working....


                  I'd take a look at modifying the design of your first value list (the book names) and also use an ID number in place of the book's title in your relationship. Book titles may not be unique and are vulnerable to typographical errors when you first enter a new book's name where a serial number field can be set to auto-enter and are by definition, unique.


                  You can set up your first value list as a two column value list with the book Id number in column 1 and the book's title in column 2. Then the user refers to the title to select a book, but the system enters and ID number to establish the relationship between the tables and for your conditional value lists.

                  • 6. Re: Nested conditional value list
                       Thanks.  That's an excellent solution to make sure we keep the list clean.  Ultimately the index being created is for a publication, so the cleaner we can keep it, the less editing need later.  
                    Many thanks!