Filtered Value Lists, with “All” Option

Demo file: – – downloadable from the “Box” over on the right.

Filtered value lists are great, and very useful when it comes to improving the user interface.  In this food-based example, we have 2 tables, “Courses” and “Items”.  Food Items belong to a Course (e.g. Soup is a Starter, Steak is a Main course, Trifle is a Desert, etc.).

rel_vl1rel_vl2So when the user selects a Course, the Items menu (pop-up) displays all Items belonging to that Course.

This is achieved by a global field, COURSEID, with a relationship from this field to the course_id field in the Items table.  We then have a value list defined as follows, i.e. including only related values in Items from the context of the Globals table.  Standard stuff, well documented elsewhere, and it works fine, as long as the user has selected a Course.


But we often have to deal with exceptions.  What if, for example, we have Items which don’t belong to a course (such as coffee or canapes)?  Or we want to allow the user to skip the Course selection and go straight to the Items list, where s/he expects to see a list of ALL items available?

Well, there are probably many ways to achieve this, but I’ve found it quite difficult to make it really user-friendly.  For instance, one book I read recently suggested having separate pop-up menu for “All Items”, using a value list based on a cartesian join, which would obviously work, but strikes me as potentially confusing to the users, sending them to 2 different places to make the same selection.

In my solution, the principle is the same, i.e. if the user doesn’t select a Course, we want to offer ALL Items, but let’s keep it all in the same pop-up.  To do so, base the relationship from Globals to Items NOT on the COURSEID global, but on a second global field, COURSESELECTOR.  This is an auto-enter calc, defined as:

If ( IsEmpty (COURSEID); ValueListItems ( Get ( FileName ) ; “AllCourses” ) ; COURSEID )

(making sure that “Do not replace existing value of field” is UNchecked).

What the auto-enter is saying is “if no Course has been selected, put all Course ids in the relationship field, as a return-separated list. But if a Course has been selected, just put that id in the field”.  So we’re using the global field either as a multi-key, or as a simple key, and when it’s a multi-key, the result will be that all Items appear in the “Items” value list.

rel_vl3There’s one important proviso though, concerning Items that don’t belong to a Course.  As it stands, these won’t appear in the “Items” value list (because that list includes only those Items which have a “parent” Course record.  So we must create a dummy Course record, with a blank name (or even named “Misc”, “Other items”, etc.), and all Items which don’t have a natural foreign key value must be given this one.  This will force the Items to appear.

As ever, there may be better way to skin this cat – if so, please let us know in a Comment.



11 Comments on “Filtered Value Lists, with “All” Option”

  1. […] Filtered Value Lists, with “All” Option | A Filemaker Miscellany. […]

  2. Denis Somar says:

    This looks super interesting but there is no file actually attached to the post!

    • Dave says:

      Hello Denis – the demo file should be in the “box” over on the right. (It seems that WordPress doesn’t allow one to attach a file directly to a post.) If you can’t see it, let me know and I’ll send it to you directly.

  3. David says:

    I can’t find demo file, pls send to my email, pls

  4. Thong says:

    Hi Dave,

    Can you also please send it to me.

    Thanks a lot in advance.


  5. Ryan says:

    Dave, this is just what I was missing. Combining your solution with one from FM Academy ( gives us dashboards with multiple value filter levels (Country, State, City). THANKS!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s