AnsweredAssumed Answered

Value List Sorting

Question asked by fciadmin on Jan 27, 2012
Latest reply on Jan 30, 2012 by comment

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:
    1. uses a script trigger to send the user to the key field and associated value list drop-down menu
    2. 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:


  1. Value lists referencing fields across relationships respect sort orders as defined in the relationship graph
  2. 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!!