I'm trying to build a value list that accomplishes all of the following 3 things...
- displays a human-readable list of items (i.e. names of items, key field is not displayed)
- sorts those items in a non-natural order (i.e. based on a human-specified numerical sort order field, not an alpha-numeric sort of the human-readable values)
- when the user makes a selection from the list, the associated key field value is entered into the data field
... such that the users can select an item in a value list and see their choice in the field based on the names of the items in that list, but the database uses the non-human-readable key field behind-the-scenes to do what it needs to do with that data.
I have been able to do this with other lists that can be alpha-numerically sorted on the displayed values by...
- making the key field value the invisible second field in the value list and having the text color set so that the key field is invisible on the layout
- having a display field on top of the key field with a dual-purpose:
- uses a script trigger to send the user to the key field and associated value list drop-down menu
- uses the key field to display the human-readable name of the item selection
Now I want to accomplish the same basic thing, but with a custom-sort of the value list based on the sort order field. The only way I have been able to work this out is to concatenate the sort and human-readable value (i.e. n_sort_order & t_item_name ) as the first field in the value list and set the second (hidden) field of the value list to be the key field as above.
In a perfect world, I'd prefer to NOT show the sort field OR the key field but I know sorting on an invisible field is not allowed in the value list definition. I'd settle for at least displaying the sort AFTER the human-readable item names, but having the second field be the sort rules out using that spot in the value list definition to hide the key field.
Filemaker doesn't support the 2 ways I can think of to accomplish this:
- Value lists referencing fields across relationships respect sort orders as defined in the relationship graph
- Have a 3rd field in the value list definition: human-readable name, sort order, and key field (invisible)
Does anyone have another suggestion I can explore for accomplshing this?
Thanks for any and all tips!!