7 Replies Latest reply on Jan 4, 2012 11:00 AM by philmodjunk

    Order of drop down value list out of order

    dzittin

      Title

      Order of drop down value list out of order

      Post

      I have a value list constructed from the first field of a table. Users create records in this list so it is not static - it grows over time.

      It is my desire to create a pull down menu from this value list which includes all member of the table's first field, plus I want to add one value at the top of the pull down, for example, "Use all values..." followed by the values created by the user.

      I implemented this by creating a temp value table. When appropriate, I delete all records, add my "special" first field and then create records for each of the user supplied values. This all works fine with one problem. The order which the values appear in the pull down list. When I look at the temp value table, all is correct - order is perfect.

      I did a manage value lists (via the file button) and de-selected "re-sort values". I guessed that this would cause the order in the pull down to be exactly as I supplied them in the table, but instead they come out sorted in alphabetic order ruining my tailored order in the temp table. What's up?

       

        • 1. Re: Order of drop down value list out of order
          philmodjunk

          Values in value lists drawn from a table in a field are always displayed sorted in ascending order. If you want use all values to appear at the top of the list, you might use something like *Use All values* or -Use All values-, which will list that entry first in the sorted list of entries.

          PS: you can also define a calculation field in this table as List ( ValueField ; "-Use All Values-") and then draw your values from this list. Such a value list will then always include the use all values entry.

          • 2. Re: Order of drop down value list out of order
            dzittin

            PhilMJ wrote: *Use All values* or -Use All values-, which will list that entry first in the sorted list of entries."

            I tried this - it does not work letter "a" entries sort before anything beginning with "-". In fact, the first alphabetic seems to be use as the first significant character in the sort and the leading "-" or "*" is ignored. the list: {Apple, Balloon, *Cat*, Echo} sorts as you see it in this list, *Cat* does not appear first in the sorted list.

            I will try to implement your second idea, but I am not sure I understand. Is valuefield a repeating field?

             

            Thanks!

            • 3. Re: Order of drop down value list out of order
              dzittin

              FWIW, it seems to be to be too rigid not offering a means to override the implicit sort when the list is displayed in a menu. There are many common examples of non-sorted lists, the most common being a list of countries where the country of use is usually displayed early in the list. I can think of others where the programmer would want to order the list non-alphabetically. I am still working on the solution above, but I don't understand why there isn't a check box associated with the manage->value list that says don't sort this value list, use order supplied in table or perhaps have the ability to pass a dynamic list into the machinery that produces the menu which would eliminate the need for a table in this case.

              Thanks for any insights.

              -D

              • 4. Re: Order of drop down value list out of order
                philmodjunk

                When I test this in a sample data base, I get -Use All values- listed first ahead of entries where the text does not start with -.

                http://www.4shared.com/file/fVsvSau7/ValueListDemo.html

                I agree that this limitation can be frustrating.

                • 5. Re: Order of drop down value list out of order
                  dzittin

                  Ok, I think I understand now. Dissecting your example helped. However, I think my head is going to explode - DUCK! :-)

                  Wow, this is complex. The thing that still mystifies me is that when I put the same list, one field, many records along with my added "-First Value-", the sort does not work (at least -First Value- does not appear at the top of the sort). That's where my earlier comment came in. However if the pull down is constructed from a calculated field defined as List(UserList; "-First Value-"), then the sort works correctly.

                  Is there anyway you can explain the difference between these two, that is feeding an ordinary text field to the menu machinery vs. the Calculated field? This is not at all intuitive.

                  Thanks,

                  -D

                  • 6. Re: Order of drop down value list out of order
                    dzittin

                    Another problem that cropped up using PhilMJ's calculated field idea. The calculated field concept works. The problem is in custom functions. Background: in the name of maintainability I decided to use a custom function to define "--First Value--". If this name needs to be changed, a custom function allows for a change in one place, not many. So I specified the calculated value as:

                             List(ValueList;  FirstValueString())

                    Where the 2nd parameter is my custom function which is defined as "--First Value--". For fun, I changed the definition of FirstValueString() and it changed throughout my code except in the definition of the calculated field which renders my maintainability concept near useless. I gather that my custom function is evaluated only once and then its definition is hardwired into the menu machinery. Is this the case?

                    • 7. Re: Order of drop down value list out of order
                      philmodjunk

                      Whether I put -First Value- in the field and base the value list just on the field or in the calculation and base the value list on the calculation field, it sorts to the top of the list. (That's the purpose of the - character at the beginning of this text.)

                      I don't see the need for a custom function. If you needed to change this value in the calculation field, you would change this in one place and all the records would update--producing an updated value list. Try it with just the quoted text like I had it and see for yourself what it takes to change the -First Value- text. The only reason for the custom function that I can see is if you have multiple value lists and you want all to have the same -first value- entry in the value list.