4 Replies Latest reply on May 14, 2014 10:41 PM by AdinBurroughs

    Conditional Value List with indirect relationship

    AdinBurroughs

      Title

      Conditional Value List with indirect relationship

      Post

           Apologies, I know this is pretty basic, but I'm still trying to wrap my head around the "filemaker" way of doing things. 

            

           I'm trying to get a conditional value list with an indirect relationship to work, but I'm having some issues.

           Scenario: (refer to image)

           3 tables: questions, sources, and chapters

           relations: questions.sourceID<—sources.sourceID—>chapters.sourceID

           My value list is two fields, chapters.chapterID & chapters.chapterTitle. As a condition I tried including only related values from the questions table. (And once, for the heck of it, the sources table)

           result: "no values defined", if I include all values, I do see all entered chapters, for all sources.

           I also tried creating a second instantiation of chapters, chapters 2 and creating a direct relation questions.chapterID—chapters 2.chapterID and a value list based off of chapters 2.chapterID & chapters 2.chapterTitle) and the related values from the questions table with the same result.

           Help! I've created other conditional value lists with no issues, but they were all based off of simple direct relationships.

            

           Two followup questions (if I may):

           -Is there any way to sort a two field value list based off of the first field if only values from the second field are shown?

           -Is there any way to include three fields in a value list? i.e. chapterID (hidden), chapterNumber, and chapterTitle? 

            

           Thanks for your help!!

      Screen_Shot_2014-05-07_at_12.25.56_AM.png

        • 1. Re: Conditional Value List with indirect relationship
          philmodjunk

               Which field from questions are you trying to format with this value list? SourceID or ChapterID?

               Conditional value lists of this type most often use TWO value lists. Example:

               You might format SourceID with a value list of SourceID's and format ChapterID with a conditional value list of chapters where the sourceID selected in SourceID controls what list of chapters appear in the conditional value list set up for the ChapterID field.

               What you described as your first attempt, values from Chapters::ChapterID and Chapters::chapterTitle with "include only related values starting from questions" would appear to be exactly the conditional value list needed for my example. But note that you first have to select a source in the SourceID field (and you may need to commit records by clicking the layout background), before your value list will list the right values for ChapterID.

          • 2. Re: Conditional Value List with indirect relationship
            AdinBurroughs

                 wow, thanks for answering phil!

                 

            Which field from questions are you trying to format with this value list? SourceID or ChapterID?

            I'm trying to fill in ChapterID, with a value list of ChapterID's (hidden) & ChapterTitles (Shown) based off an earlier selection of the SourceID.

                  

                 

            Conditional value lists of this type most often use TWO value lists. Example:

                 

                      You might format SourceID with a value list of SourceID's and format ChapterID with a conditional value list of chapters where the sourceID selected in SourceID controls what list of chapters appear in the conditional value list set up for the ChapterID field.

                 That's exactly what I did - sources is a value list of all sources SourceID (hidden) and SourceTitle (Shown). Reference the value lists in the attached image.
                  
                 
            What you described as your first attempt, values from Chapters::ChapterID and Chapters::chapterTitle with "include only related values starting from questions" would appear to be exactly the conditional value list needed for my example. But note that you first have to select a source in the SourceID field (and you may need to commit records by clicking the layout background), before your value list will list the right values for ChapterID.
            That's what I thought! But I can't seem to get it to work, even selecting a source and refreshing the record. I have the conditional value list pair for answers/correct answers working just fine in the same layout. 
                  
            I screencapped my current layout and setup. Is there something in the relation that I need to define to get Filemaker to recognize the relation between sources and chapters in the related values conditional? Or am I just better off trying to write some sql somewhere to act as the datasource for the questions.chapterID field?
            • 3. Re: Conditional Value List with indirect relationship
              philmodjunk

                   Make sure that ChapterTitle is of type Text and not type Number. Make sure that indexing is enabled for ChapterTitle.

              • 4. Re: Conditional Value List with indirect relationship
                AdinBurroughs

                     Figured it out. I didn't have the primary key of the chapters table (ChapterID) set to autofill on enter, so the field was empty on the test records i had entered.

                     Thanks for your help & time Phil!