Demo file: filtered_VL_with_all.zip – – 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.).
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.
There’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.