7 Replies Latest reply on Aug 27, 2010 11:07 AM by philmodjunk

    need dynamic value list not displayed alphabetically



      need dynamic value list not displayed alphabetically


      I have a drop-down list I'd like to populate dynamically from a list table (of over 200 records). List must be displayed in one of two orders (either of two indexed serial number fields) but drop-down displayed option needs to be calculated field (2 other fields in table).

      I'd first tried using value from 1st field but this is not adequate for informing a choice. If I choose to show value from 2nd field, FM sorts by this field. That's my problem--I need to order non-alphabetically. Am running into a wall.

      Any suggestions?

        • 1. Re: need dynamic value list not displayed alphabetically

          If you show two columns of data, you can sort by either column...

          Since FileMaker builds these lists from field indexes they come out automatically in ascending order--which is a problem when that's not the logical order.

          If you can display the data in just one column, there is a trick for using the second column to sort values in a different order using spaces. It has some significant limitations and I'm not sure it would work for this situation.

          Can you describe a bit more fully what your "non-alphabetic" order is for your data?

          • 2. Re: need dynamic value list not displayed alphabetically

            the order is a combination of historical (how things once were viewed) and a more dynamic instance--those about which information is currently expressed. The preferred "order" will almost always be those about which information now expressed with sub-set of the historical order. Other option will be simply "old historical order"

            Was that sufficiently explanatory? thanks!

            • 3. Re: need dynamic value list not displayed alphabetically

              Not really, that doesn't provide enough information to explain how you would sort these values if you could. Can you give an example?

              • 4. Re: need dynamic value list not displayed alphabetically

                sorting thru to an answer to your, ultimately my question, may need to begin with what seems, at least, out of reach. You'd initially mentioned the indices FM builds to generate an ascending order. Ideally, my sense has been case'd be closed if I could obtain access to one or both of those indexes, the ability to define their building order.

                suppose I wanted to display a set of records in one of two different orders. Establishing what in my ParadoxDOS days I called a key field, I'd build an order via text serial number. Then displaying by the preferred order would be as simple as expressing a sort request.

                I've got a preliminary set of sorting fields, called, let's say, LISTsort1 and LISTsort2. A third field identifies (via serial #) a record (LISTserial), a fourth describes it with a text label (LISTlabel). I need my drop-down to display a calculation of LISTserial & LISTlabel, but be sorted either by the text-serial value in LISTsort1 or LISTsort2. What happens instead is FM alphabetically sorts by LISTlabel. I need access to the indexes FM builds, or at least a way to define them.

                I'm kind of grasping at straws here. Was that any better?

                • 5. Re: need dynamic value list not displayed alphabetically

                  Knowing you have a field which you can sort on to get the desired order--that's what I was looking for. I've long wanted to be able to specify such a sort order in FileMaker without giving up a value list column and you can't really.

                  Here's a description of the "spaces" based trick Comment and others have described here:

                  Let's say you have these values sorted by an "order" field:

                  Orange      1
                  Tangerine  2
                  Apple         3
                  Peach        4

                  You can display your values in exactly that order, if you select the value field for column 1 and the sort order field for column 2. You then have an option to sort the values by column 2, but now you see this for a value list:

                  Orange 1
                  Tangerine 2
                  Apple 3
                  Peach 4

                  You can hide the numbers by setting up a calculation field that turns the sort values into a matching number of spaces:

                  Substitute ( 10^sortField - 1 ; 9 ; " " )

                  Specify this field in place of the number field  as the column 2 field and sort on it instead.

                  This has two key draw backs, You can't use column 2 to display additional data about your list of values and it's only practical for relatively small groups of values.

                  Sometimes the best approach in FileMaker is to rethink the issue and use a different method to display the values. Conditional Value lists that break your list into meaningful sub groups is one option. A scrolling "click list" of values displayed in a portal--which can now be sorted by your sort fields is another.

                  • 6. Re: need dynamic value list not displayed alphabetically

                    Thanks Phil, your explanation makes sense, especially those drawbacks. Had already started re-thinking my issue. Am particularly interested in idea of a conditional value list (wd you please expand on that for me a bit?).

                    Am certain you've seen on web how a subject/link or group is expanded to display a sub-group. That would really float the boat.

                    Thanks so much!

                    • 7. Re: need dynamic value list not displayed alphabetically

                      Hierarchical menus would be an awesome new feature for FileMaker.

                      You can set up a conditional value list where you select a category value in value list one and then the values in a second list are limited to members of that category.

                      There's a knowledge base article on that approach:  http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list/session/L3NpZC83enlCdXc4aw%3D%3D