14 Replies Latest reply on Jan 10, 2017 3:17 PM by philmodjunk

    Sorting in reverse order in a value list

    lselsick

      Good day everyone. A quick question:

       

      I have a value list that shows records from a related table which includes 2 fields: A job number and a job description.

       

      I want to sort the drop-down list by the job number, but in reverse order so that the most recent jobs are on top. The dialog box allows one to sort by either the first field or the second field but not in reverse order. The only seemingly available option is to use the "Custom Values" option, but, as you can imagine, this would require me to create a carriage returned list of thousands of numbers covering me will into the future (and seems very clumsy).

       

      So, is there a simple way to sort a value list in reverse order?

       

      Many thanks in advance.

       

      Lee

        • 1. Re: Sorting in reverse order in a value list
          keywords

          I know of no way to overcome this hurdle within a Value List. However, I also reckon that Value Lists with more than a handful of values ought to be avoided. Fortunately there are alternatives to VL for selection, perhaps the simplest and best being a portal which is set to show a reducing list of related records depending on the contents of a global field into which you type—see attached screenshot.

          Screen Shot 2017-01-09 at 6.55.56 PM.png

          • 2. Re: Sorting in reverse order in a value list
            Suresh

            Thought me too have the same question!

             

            Link:- Stop getting Field Input values that Auto Ascending Order in Value List?

             

            But me too have less response for this question!!

             

            - Suresh

            • 3. Re: Sorting in reverse order in a value list
              philmodjunk

              THere are ways to reverse the sort order or get an arbitrary sort order, but not when you need both fields of the value list for data. You can set up a calculation field that returns a varying number of space characters, make it the second field and sort on it. But as I already noted, that doesn't work here as you need the second field. (There's a calculation that can be used to take a number and return the same number of space characters as the number.)

               

              Another trick if using FileMaker 15 is to use a button bar in place of the value list with merge variables used as button labels. A script can assign values to the variables and "un hide" a button bar and you can set up a regular button to run that script to "toggle" the deployment/hiding of the button bar. This has the added advantage of working exactly the same way on iOS devices.

              • 4. Re: Sorting in reverse order in a value list
                okramis

                Multi-column value-lists are possible with the MagicValueList-technic. There're two great articles in the german Filemaker Magazin (http://filemaker-magazin.de) issues 201301 and 201302 by Arnold Kegebein on this topic. Combined with the BOM-trick for sorting this should work for the OP.

                 

                best regards

                Otmar

                1 of 1 people found this helpful
                • 5. Re: Sorting in reverse order in a value list
                  lselsick

                  Beatrice

                   

                  Thanks. Regrettably, my German is non-existent and I cannot find (never mind understand) the article.

                   

                  You assistance is nevertheless appreciated.

                   

                  Lee Selsick

                  Director of Strategy & Design

                   

                   

                   

                   

                   

                   

                  Next Brand · Brand Strategy & Design

                  Unit 107, 3 Male Street, Brighton VIC 3186

                  T +61 3 9008 8988 · M +61 424 253 716

                   

                  www.nextbrand.com.au

                  • 6. Re: Sorting in reverse order in a value list
                    okramis

                    you could ask arnoldkegebein direct, maybe he has a english version or can provide the sample file?

                     

                    Otmar

                    • 7. Re: Sorting in reverse order in a value list
                      lselsick

                      Thanks

                       

                      I agree about the value list having only a few records. However, this is a variable issue for me. The process is as follows:

                       

                      First we select a client.

                      Then we select an active job from that client ('show only related records’ filters only the open jobs through)

                       

                      In most cases there are only 4-5 active jobs, no problem

                       

                      But in some cases, we might have 20-30 open jobs in which case, listing them in reverse order would be useful as the most active are likely to be the most recent.

                       

                       

                      Cheers

                       

                       

                       

                       

                       

                      Lee Selsick

                      Director of Strategy & Design

                       

                       

                       

                       

                       

                       

                      Next Brand · Brand Strategy & Design

                      Unit 107, 3 Male Street, Brighton VIC 3186

                      T +61 3 9008 8988 · M +61 424 253 716

                       

                      www.nextbrand.com.au

                      • 8. Re: Sorting in reverse order in a value list
                        erolst

                        I would like to reiterate Phil's first suggestion: use a popover with a portal pointing at the Job table inside that has a filter on active status (relationship or portal).

                         

                        You can sort that 'list' of related records by any field(s) you like, show however many fields you like, and have a much richer user experience than a somewhat 'cheap'-looking popup (which, admittedly, is a subjective criterion ...)

                        • 9. Re: Sorting in reverse order in a value list
                          user19752

                          Here is a custom function.

                           

                          1. Make a calculation field using this function, with storage option "unicode" index.
                          2. Use it instead of 2nd field of value list.

                           

                          You want descending sort on "job number" field, so it looks like this if job number has max value as 999999

                          VLSorter ( 1000000 - job number ; 6 ; job description )

                           

                          /*

                          use for 2nd field of valuelist 2 field, sorted by valueToSort. Use "unicode" index.

                          This wouldn't work on arabic (or any RtoL) language.

                           

                          parameters

                          valueToSort:        integer value for sort ascending

                          maxDigitOfValueToSort:    max digit of valueToSort, should be <=100

                          valueToShow:        2nd field to show

                          */

                           

                          Substitute ( Replace ( 10 ^ maxDigitOfValueToSort + valueToSort ; 1 ; 1 ; "" ) ;

                          [ 0 ; Char(8203) ] ;

                          [ 1 ; Char(8204) ] ;

                          [ 2 ; Char(8205) ] ;

                          [ 3 ; Char(8206) ] ;

                          [ 4 ; Char(8207) ] ;

                          [ 5 ; Char(8234) ] ;

                          [ 6 ; Char(8235) ] ;

                          [ 7 ; Char(8236) ] ;

                          [ 8 ; Char(8237) ] ;

                          [ 9 ; Char(8238) ]

                          ) & Char(8237) & valueToShow

                          • 10. Re: Sorting in reverse order in a value list
                            lselsick

                            That looks very sharp.

                             

                            I will give it a go.

                             

                            Many thanks

                             

                             

                            Lee Selsick

                            Director of Strategy & Design

                             

                             

                             

                             

                             

                             

                            Next Brand · Brand Strategy & Design

                            Unit 107, 3 Male Street, Brighton VIC 3186

                            T +61 3 9008 8988 · M +61 424 253 716

                             

                            www.nextbrand.com.au

                            • 11. Re: Sorting in reverse order in a value list
                              philmodjunk

                              The calculation that user19752 recommends is the one that I alluded to earlier. The main draw back is that you are now limited to only one field as an actual source of data for your value list. If you are trying to set up the typical field 1 is an ID and Field 2 is a name/description type value list, you can't use this method as you'll lose the use of one of your two fields--this, BTW, is a ridiculous and unbelievably primitive limitation where other applications have far outclassed FileMaker for decades.

                               

                              You could concatenate two fields into one via a calculation field and then parse the ID back out of the entered text to force two fields of data into one via script or auto-enter calculation, but if you are going to go to that much trouble, it makes more sense to me to put a selection portal inside a popover instead.

                              • 12. Re: Sorting in reverse order in a value list
                                user19752

                                The calculation is variation of concatenating BOM (zero width space) & 2nd field, so there is no such limitation.

                                Probable draw back is on the illegal? use of control characters for bidi.

                                1 of 1 people found this helpful
                                • 13. Re: Sorting in reverse order in a value list
                                  lselsick

                                  Thanks to everyone

                                   

                                  I suspect the selection portal is going to simply be the best way to go.

                                   

                                  I will mark this as “answered” but is has provoked some interesting discussion!

                                   

                                  Cheers

                                   

                                   

                                  Lee Selsick

                                  Director of Strategy & Design

                                   

                                   

                                   

                                   

                                   

                                   

                                  Next Brand · Brand Strategy & Design

                                  Unit 107, 3 Male Street, Brighton VIC 3186

                                  T +61 3 9008 8988 · M +61 424 253 716

                                   

                                  www.nextbrand.com.au

                                  • 14. Re: Sorting in reverse order in a value list
                                    philmodjunk

                                    User19752,

                                     

                                    I stand corrected. After a closer look, your calculation is a different option than the one that I had in mind. It does seem to be limited by the total number of characters explicitly listed for use with the Replace function.

                                     

                                    The calculation that I've used before is:

                                     

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

                                     

                                    This produces a nonvisible string of spaces = to the value specified in the SortOrder field. It has the limitation I described, that it takes up one of the two fields available to us for this purpose.