Well, we’ve reached part 4 in this series that has been going on in fits and starts since September, and we have just one demo file today: Thinking About Value Lists, part 4
As a reminder, in part 3 we looked at an expense submission system, with each portal row representing a receipt that the submitter would like to be reimbursed for. The user first specifies the department via a popup menu, and then chooses an account via a filtered dropdown list, as per this composite screen shot:
And while this is certainly functional, I think I’d prefer:
- a single value list (a.k.a. VL), rather than two
- to stop storing the department in each receipt entry; what we really care about is the account number… the department is just a filtration device
Wouldn’t it be nice if we could use type ahead to drill down through the department and into the correct account via a single dropdown list? What if we were to concatenate the department, account name and account number into a single VL, with the account number at the right? And use a fixed-width font to make it look nice and neat? (Yes, I know, Courier is an ugly font, but it is both fixed-width and cross-platform… if I were deploying on just one platform, I’d find a prettier fixed-width font.)
Since we’ve collapsed what would normally be a two column VL into one, we could also enable auto-complete (which only works on single-column value lists), and drill down surgically…
And then once the selection has been made, parse the account number from the right hand side of the entry, like so.
And, of course, it would need to work smoothly in Find mode, as well as Browse mode. (And if the user already knows the account number, and wants to simply type or paste it in, that needs to work too.)
There are a few hoops to jump through, which we’ll discuss in a moment, but it certainly can be done. I don’t use this technique very often, but once in a while it comes in handy, and users love it. Also, I’d like to thank Dana Alan Perry for the idea of putting the ID on the right of a single-column VL and then parsing it off using the RightWords function, which he showed me at DevCon ’99.
At any rate, here’s the Relationships Graph.
The value list will come from all records in the Accounts table… here are some of them:
…and note that Department is a text field in this table, which obviously is not optimal from a database normalization standpoint — correct would be a Departments table with one record per unique department, and a standard primary/foreign key link to Accounts — but our single-column, auto-completion-enabled VL needs to be based on a single indexable field, so the architecture is intentionally de-normalized.
Here’s the value list:
And here’s the calculated field definition:
The value list is attached to a special instantiation of account_number, configured thus…
…and conditionally formatted to be invisible, except when the VL is dropped, using the 500 point conditional formatting trick.
Also, there are three script triggers attached, and their names indicate whether they are are triggered in Browse mode or in Find mode.
Remember, we only store the account number in the field, so the OnObjectEnter trigger ensures that if the user clicks into the field for an already-populated entry, the account number will be translated into the corresponding value list item (this is accomplished with the help of the ValuePosition custom function). Here’s how it looks in browse mode…
…and here’s what’s going on behind the scenes:
The other two triggers go in the other direction, and make sure that when the user exits the field only the account number remains. The Browse mode version doesn’t need to do very much…
…but in Find mode we populate the Department and Description fields, which, while not actually necessary, provides some nice visual feedback for the user.
Conveniently, RightWords will ignore the square brackets, and pluck the enclosed “word” from the end of the string.
Given that the combination of department + account description is unique, one might rightly ask whether it’s necessary to display the account number at all. Why not use department + account description as a temporary key, and then use the temporary key to derive the account number?
First off, from a developer laziness standpoint, one of the advantages of having that account number dangling off the right edge is that RightWords(Receipt::account_number;1) is a dead easy way to translate the VL item into the account_number… in both Browse and Find modes. But, more importantly, you may discover that your supposedly unique temporary key isn’t 100% foolproof.
I was discussing this with Chris Cain at Extensitech recently, and he pointed out that, occasionally, something common sense says will “never” happen, nonetheless does. For example you might think that LastName + FirstName + PostalCode + StreetAddress would guarantee uniqueness, but then you discover a father and son with the same name living in the same house. By including the primary key in that temporary key, both entries will show up in the value list, and you might have to do a bit of research to figure out which ID corresponds to whom, but at least you will have a way to uniquely identify them.
As I said at the beginning of part 1, while there might be many ways to skin the VL cat, I only intended to examine a few of them. At any rate, I’m thinking it’s time to bring this series to a close.
But we’re going to end with a bang. In the comments section of part 1 I wrote “I am contemplating a future posting re: this age old dilemma: how can we apply custom sorts to field based value lists?” And then, as if on cue, two days later Marcelo Piñeyro of Soliant Consulting produced a masterpiece entitled Extending FileMaker Pro’s value list sort capabilities using the Char() function, which I heartily commend to your attention. Unfortunately type-ahead does not work with this technique because of the invisible characters inserted in front of the visible text. Nonetheless, it is a superb example of outside-the-box thinking, and well worth adding to your developer toolbox.
©2012 by Kevin Frank & Associates. All rights reserved.