5 Replies Latest reply on Jan 30, 2012 7:21 AM by comment

    Value List Sorting


      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!!

        • 1. Re: Value List Sorting

          It would probably be better to use another selection method, e.g. a portal or a list view.


          How many values does your list have?

          • 2. Re: Value List Sorting

            If you are open to using plug-ins, I would highly recommend CNSMenu.




            I use this extensively to build drop-down menus with (a) cutom sort orders, (b) either one-, two-, or three-levels of sub menus (when needed and appropriate), and (c) the ability to store some value completely different than the sort key and/or the displayed menu item name in the target field. Takes a very small amount of scripting, but once you use this plug-in, you'll find a ton of uses for it. It allows adding a whole new level of productivity to the user interface.

            • 3. Re: Value List Sorting

              This list is used for several different things, so the number of items displayed changes accordingly:


              1) Users logging their time to different activities

              2) Users logging usage of internal resources and inventory such as Edit System Hours or Blank DVDs

              3) Users adding goods/services that we purchase from Vendors to Purchase Orders

              4) Users adding items to Estimates when working out $$ for proposals to our clients


              The full Item list is currently 232 active items. There are different value list definitions that draw on the full list of Items (via join tables such as Role for the staff or Offering for the vendors) for the various usages. In the case of Estimates, the value list is filtered based on a Category list, such that the user selects "Creative Development" or "Film and Video Production" first, and then sees only Items categorized as Creative Development or Film and Video Production repsectively.


              In all cases, the entries consist of multiple fields (e.g. Quantity, Item, Client, Project, Notes, etc...) so they users prepaer an entry in a set of global fields before committing them to the database with an "ADD" button/script. As you'd imagine, several of the global fields have multiple choices and are currently displayed using drop-down menus. Using a portal or pop-up window with a list view for just one of the fields would break the interface convention, and I feel that switching to portals or lists for all of the necessary fields would make for a very cluttered interface.


              In other situations, a portal or list would make sense. I think that in these cases I need to stick with a value list in a drop-down menu.

              • 4. Re: Value List Sorting

                This looks promising. I'll explore the demo and example files.

                • 5. Re: Value List Sorting

                  fciadmin wrote:


                  The full Item list is currently 232 active items.


                  I am afraid the workaround I was thinking about is limited to around 100 values.