1 2 Previous Next 26 Replies Latest reply on Feb 14, 2011 10:28 PM by ericjlindholm

    Dynamic Value List



      Dynamic Value List


      Would like to have a value list of say the 5 most recent values.

      The purpose:
      The field in question has a Value List of all values. That list is huge, but only a few are used often. Would like to be able to quikly select from one of the most recently used values.

        • 1. Re: Dynamic Value List

          How would you track that a value was "most recently used"?

          There are approaches that might be able to set up a conditional value list for this, but I'm curious as to how you define that phrase and how the system might identify them in the first place. I'm thinking in terms of "most frequently used over the last x days" as something that might serve here, but don't know if that matches what you have in mind.

          • 2. Re: Dynamic Value List

            I guess just the 5 most recently used/selected

            • 3. Re: Dynamic Value List


              there is no practical way to modify the sorting behavior of a value list other than use a Custom Value List.

              • 4. Re: Dynamic Value List

                No, but we can limit the list to values from all records with modification dates up to x number of days older than the current date, which is what I had in mind.

                • 5. Re: Dynamic Value List

                  Yes, i am not looking to sort the value list, but show the most recently used values

                  • 6. Re: Dynamic Value List

                    What I'm thinking of isn't "5 most recently used" but rather "all values used in the last specified time period". Will that serve or do you really want to limit this to a specific number of "most recently used" values?

                    I think that's also possible, but the approaches I am thinking of makes that a much more complex solution--so I hesitate to go there unless we really have to.

                    • 7. Re: Dynamic Value List

                      sure that works too

                      • 8. Re: Dynamic Value List

                        Let's say you want all values used in the last 5 days.

                        First you have to capture the modifcation dates each time a value is selected. Let's say your table that serves as a source of values is called "Values". Define a date field in values and use a script trigger set on your value list formatted field to enter the current date into this field each time you exit, modify, or save on the field (most appropriate trigger depends on the type of value list here.)

                        Then you can define an unstored calculation field, cValueDate, in the table where you have your value list formatted field: Get ( CurrentDate ) - 5

                        Now you can define a relationship:

                        MainTable::cValueDate < Values::LastUsedDate

                        Now you can set up a conditional value list that draws values from the Values table, use only related values starting from MainTable.

                        • 9. Re: Dynamic Value List

                          I have a similar situation but its slightly different.

                          I have a Sound Equipment Table.  It has fields Name, Category, Description.

                          I want to create a values list that contains Names that are in a particular category.

                          For example, I was a values list that only lists Names that are marked as amp racks.

                          I know I could make a separate table for every category of equipment but i would like to be able to easily add new categories.  

                          Thank you.

                          • 10. Re: Dynamic Value List

                            Phil - can we not have a Sticky on the front page of the Forum that links to your Conditional Value List Tutorial?

                            • 11. Re: Dynamic Value List

                              I have seen the tutorial.  What I want is slightly different in that i want to use the first pop up menu set.  I want the category to be determined by the specific field I am selecting my item from.  In the menu example it would look like this.  (assume you had to choose all your meals for the day to make it work for my needs)

                              Breakfast  *drop down list with only breakfast items

                              Lunch *drop down list with only lunch items

                              Dinner *drop down list with only lunch items

                              • 12. Re: Dynamic Value List

                                Is that not just 3 Value lists - one for breakfast, one for lunch, one for dinner, each one tied to the appropriate field?

                                • 13. Re: Dynamic Value List

                                  but if they are all taking from the same table, how can you make the values list only show items categorized as one type?

                                  • 14. Re: Dynamic Value List

                                    With regards to Eric's request, You certainly could just define three value lists, but I'm guessing you might have some values that are common to two or more of the lists. (A hamburger, for example could be in both the lunch and dinner menus.)

                                    This can be done. Define a category field with a checkbox group of the three category values: Breakfast, Lunch, and Dinner. In your main file, define three calculation fields to serve as a key field for each category. cBreakfastKey would just have the value "Breakfast", cLunchKey: "Lunch", cDinnerKey: "Dinner" to match one of these values in the value table's category field.

                                    Now define three relationships, one for each value list for your breakfast, lunch, and dinner fields:

                                    MainTable::cBreakfastKey = BreakfastValues::Category
                                    MainTable::cLunchKey = LunchValues::Category
                                    MainTable::DinnerKey = DinnerValues::Category

                                    BreakfastValues, LunchValues, and DinnerValues would all be table occurrences of the same data source table. To assign a value in this table to both Lunch and Dinner, you'd simply click both the Lunch and Dinner checkboxes in that record's category field.

                                    You'd then define three conditional value lists, each drawing their values from a different "values" table occurrence but all with the same "starting from" table occurrence.

                                    If "table occurences" and "data source table" are unfamiliar terms:  Tutorial: What are Table Occurrences?

                                    For those looking for my tutorial on conditional value lists: Custom Value List?

                                    There's also a knowledge base article that's pretty useful: 


                                    I have suggested in the past that FileMaker add a "Tips and Tutorials" section where forum members can submit such posts and then, if approved, they would be added to that section as a "read only" thread. So far, that hasn't happened, though.

                                    Links to threads I find useful to recommend to others are listed in my Known Bugs List database on a separate "Tutorials" layout for those who care to search them out. There's no button to that layout, but you can find it pretty easily if you pull down the layouts menu.


                                    1 2 Previous Next