8 Replies Latest reply on Mar 8, 2017 11:04 PM by philmodjunk

    Filter Value List of the Dropdown





      Filter a dropdown value-list by current year.



      I have two tables, each of them has a layout:


      1. School Dates

      2. Signups


      The relationship looks like this:


      School Dates : PK_School_Dates <-----> Signups : FK_School_Dates


      On the Signup layout, I have a dropdown to select the school date. That works fine. BUT, when 2018 comes up, that dropdown will double in size and will continue to year after year.


      What I would like to do is filter the dropdown to only show records for the current year.


      There's more details.......


      The value list is setup like this:


      - Use values from field: School Dates : PK_School_Dates
      - Also use values from Second field


      The second field is located in School Dates, and has the Date, Track, and Type combined. This is so the dropdown shows: 3/17/2017, Laguna Seca, 2-Day Camp


      If it's worth mentioning, the "Track" field on School Dates is a related filed to a table called Tracks.


      The dropdown works great, but I'll need to filter it.


      What I don't want is a solution that forces the user to click more fields than necessary. Something like this:

      Dropdown: "Select Year"

      (User selects 2017)

      Dropdown: "Select Schol Date"

      (User selects 3/17/2017, Laguna Seca, 2-Day Camp)


      Any ideas who to go about this?



      Would you create a calculation field in School Dates, have it merge Date, Track, Type, but filter out by "Year ( Get ( CurrentDate))," and use that for the dropdown values? Somehow it would have to pass the ID along, yeah?? Just a wild guess, haha.



        • 1. Re: Filter Value List of the Dropdown

          You need a calculation field in School Dates that extracts just the year: Year ( your date field here ).


          Then you add a new occurrence of School Dates so that you can link this occurrence to SignUps by year fields. You can then set up your value list from this new occurrence and then select: Include Only Related Values, starting from SignUps.


          This is is called a conditional value list.


          You can find working examples of such a basic conditional value list as well as a number of other approaches to creating conditional value lists here:


          Adventures in FileMaking #1--conditional value lists

          • 2. Re: Filter Value List of the Dropdown



            The file you attached will come in handy for sure.


            I did exactly what you put in the post and it didn't work. (no data displays in the dropdown)


            I either (a) didn't understand a step and misapplied it, or (b) you miss-read what I wrote. Judging on your contributions to this forum, I'd say it was more like "(a)"


            I'll be specific with what I did to follow your instruction:


            1. I created a new field in School Dates called calc_year. This pulls just the year. I confirmed by putting it on the layout.

            2. Setup a new TO of School Dates.
            3. I didn't have a year field in Signups, and no use for one. Confused now (but going with it anyway), I created it--so I could create a new relationship as you suggest. So now I have: Signups : year <---- > School Dates : calc_year.

            4. I created a new value list. I set the table for "Use Values from First Field" to the new TO and selected the PK. Then I selected the new TO for "Also Display Values from Second Field," and selected the field that I want to show in the dropdown (the field that combines Date, Track and Location). The I selected "Include only related values starting from" and the TO was not there, because we had selected it in the first two options.



            I must be taking your instructions too literally, but I feel for my level, the instructions were not complete enough. I then looked at your file and tried to reverse-engineer it, but couldn't figure it out. I think it's a bit above my level. Plus, the way you have your data doesn't match how our data works.


            I appreciate the help though.

            • 3. Re: Filter Value List of the Dropdown

              Okay...I got this to do what I wanted it to do, but in a different way, using a calculation.


              Table: School Dates
              Fields are: Date1, Track, School Type


              I created a field called Date_Track_Combine, that combines those fields, as I mentioned in my first post. So the result looks like this: 3/17/2017, Laguna Seca, 2-Day Camp


              I then created a calc field called: calc_year. The formula is:


              Case ( Year ( Date1 )   ≥   Year ( Get ( CurrentDate )) ; Date_Track_Combine )


              As you can see, this field will only show the current year, which is all I wanted in my dropdown. The value list is then set to calc_year in the section "Also Display Values from Second Field."


              This works. Do you see any problem with the way this is done? It tested it to make sure previous year records don't disappear from the Signups table, and they remained intact.





              • 4. Re: Filter Value List of the Dropdown

                SignUps should be your starting from TO.


                You then have to select a year in the year field in sign ups before values appear in the value list. If you check your original post, you said:


                Drop Down Select Year user selects 2017.

                This is the new field for year that I had you add to SignUps and link to the new TO of SignUps by Year fields.


                Think of it this way: if you were to set up a portal to school dates on your SignUps layout, how would you do it? The portal's TO would be the same as that used for your value list and the layout's TO is your "starting from" TO. (This portal comparison to conditional value lists is also demonstrated in the Adventure file. )

                • 5. Re: Filter Value List of the Dropdown

                  That all makes sense.


                  I'll have to be more careful the way I word/structure my posts.


                  Using a dropdown to select year, is not what I wanted to do. I wrote that in my post and gave an example, but the way it was worded, it looked like the dropdown was what I wanted.

                  • 6. Re: Filter Value List of the Dropdown

                    Then what DO you want?

                    • 7. Re: Filter Value List of the Dropdown

                      I wanted the user to only see current years, without needed to select the current year. It would just be one less step for data input and eliminate the possibility of signing up a student to a school that already happened.


                      Turns out they wanted to be able to also search the dropdown. So I created popover, added a portal with a search field. The portal is set to filter only the current year with: Year ( T33e_signups_SCHOOL_DATES||id_school_dates::Date1 )  ≥ Year ( Get ( CurrentDate ) )


                      So it's working just they way they want it, and I learned a lot along the way. Cheers for your guidance.


                      This is what it looks like:

                      Screen Shot 2017-03-08 at 9.55.06 PM.png

                      • 8. Re: Filter Value List of the Dropdown

                        Just because there's a field for specifying the year does not mean that the user has to be the one to put that year in place. A script can do that or it can be an unstored calculation that calculates the year from the current date.


                        If you go back to the adventure file, take a look at the "hard wired conditional value list" examples and you'll see two methods for setting up a CVL that do not require the user to select the filter value used to limit the values in the value list. The first method could be adapted to what you need here.