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.
Pop-up menus in Filemaker Go are great, but have an annoying behaviour. If, after selection from the menu, you tab automatically to a text field, the on-screen keyboard is displayed. This is ugly. You can, of course, remove the pop-up field from the tab order, but this has the effect of leaving the menu displayed after item selection – also ugly. The solution is to have an OnObjectModify on the pop-up field, which just does a Commit Record. This has the effect of removing the pop-up menu, which, combined with removing the field from the tab order, gives the calm, elegant behaviour that we seek! (Thanks to Mike Mitchell on Filemaker Technet for pointing this out.)
But there’s one problem with the technique, which is this: because the key field is the one that the user actually enters data into, it’s possible for the user also to enter invalid data. The intention is to limit the selection to items that are in the value list, but if the user double-clicks in the field and starts typing, invalid data may be left in the field. (Of course the user may, by chance, enter a valid key value – just as a chimpanzee, left to its own devices, may eventually type the Complete Works of Shakespeare. But it’s unlikely, to say the least. And I’m not for a minute implying that my users are in any way like chimps…) So anyway, we need to use a triggered script to make sure that this doesn’t happen.
On the key field, I’ll add an “OnObjectModify” script. This will run either when the user selects a key value from the drop-down list, or when s/he types in the field. The pseudocode for the script is:
Check whether the field holds a value that exists in the value list.
If so, do nothing.
If not, display an error message and offer the drop-down list again.
A couple of pre-requisites for this script;
- It has to be as portable as possible. (I don’t want to have to write a new iteration of the script for every drop-down in the system.)
- Once the script has detected invalid data in the field, I want to throw the user back into the field, complete with drop-down list displayed (without the user having to click into the field again).
So this is how it turned out in FileMaker:
Note that the script uses two custom functions, both of which can be found on Brian Dunning’s site.
- “GSP” – which gets the specified parameter from the parameters passed to the script.
- “FilterList” – which checks for the presence of a value in a given list. (It does a lot more than this if you want it to, but that’s what it does here.)
Note also that you need a “dummy” field to go to, either during or after validation. I have the dummy field (a global) parked just off the displayed area of each layout in the system – it often comes in handy.
So, the first parameter is a comma-separated list of all the (key) values in the value list that is attached to the field. (They have to be comma-separated, rather than cr-separated, so that the list will be treated as one parameter.)
The second parameter is the OBJECT NAME (not field name) of the field being validated. We need this in order to get back to the field if validation fails. So, for the script to work, you must give the field an object name.
So the script checks whether the contents of the active field exists in the list of values. If it does, fine – just go to the dummy field, and end validation.
If the value doesn’t exist, then something’s obviously wrong, so show the custom dialog, initialise the field, then go back to the field in question. Note the need to go to the dummy field first – if you don’t (i.e. if you go straight to the field being validated), the drop-down list will not be displayed – you haven’t actually left that field yet, hence the need for the little round-trip.
Note also the need to use “Go to object” rather than “Go to field”. Although we can pass the field name as a parameter to the script, we can’t use that information once we get here, because FileMaker haven’t yet come up with a “Go To Field By Name” script step.
And that’s it. A nicely portable validation script to help keep the solution free of invalid data.
There’s no perfect solution when it comes to enabling users to select an item from a value list. Usually it comes down to a choice between a drop-down list,or a pop-up menu. These are similar, but crucially different. These are the pros and cons of each, as I see it.
- Easy to implement,
- Easy to use – IF there are only a few items in the value list,
- If the Value List is made up of 2 fields, it’s easy to show the resulting text value (field 2) but store the key value (field 1).
- No type-ahead functionality, at least on Windows – so, to go from A to Z, user has to scroll,
- The menu will fill the available space – this can look really ugly.
- Look tidy, as only some values are displayed (about 15?),
- User can type one or more characters to go the required part of the list.
- If the Value List is made up of 2 fields, it’s not possible to say “show the text value, but store the key value” (as you can with the pop-up).
Until now, I’ve gone for the tried and tested technique of using a drop-down menu to display the available values, with another field, holding the key value, UNDERNEATH the displayed field showing the text value. (I documented the technique here.) By setting the hidden key field to allow data entry, and setting the text field NOT to allow data entry, you get the best of both worlds … to a point. One shortcoming of this technique is that, if you want the user to see an arrowhead as an indication that this is a drop-down, you have to make the text field on top a bit less wide than the key field beneath in order to show Filemaker’s native arrowhead. In the wonderful world of FM12’s layout themes, this all gets a bit a bit messy, and has always been fiddly.
I’ve now realised there’s a better option, and this is the approach that I plan to use in future (i.e. until something better turns up!):
- Define the key field “underneath”, based, as before, on the 2-field value list. As before, this is a DROP-DOWN menu, allowing field entry (in Browse and/or Find modes, according to the circumstances), but don’t enable the “arrow to show and hide list”.
- Add the same field on top of the key field. This one, however, is a POP-UP list, with NO field entry. The 2 stacked fields should be exactly the same size.
The result is that the text value is displayed, thanks to the behaviour of the pop-up menu, whilst the type-ahead selection behaviour of the drop-down list underneath makes things more user-friendly. The added bonus is that the arrowhead of the “on top” pop-up menu appears to belong to the drop-down menu, completing the “best of both worlds” solution.
Note, however, there’s still a drawback with this, and it’s a big one – the user is able to double-click in the key field, potentially typing an invalid key value in the “underneath field” – more about this in the next post.