8 Replies Latest reply on Mar 3, 2010 8:58 AM by johnhorner

    sorting value list based on field values

    johnhorner

      Title

      sorting value list based on field values

      Post

      i have a set of records which contains a number formatted "year" field.  i also have a global "year_G" field that i use to filter related records for any given year i enter into it.  to make the process of filtering a little mroe streamlined, i made a value list based on the contents of the "year" field, which i could use as a pop-up menu from which i could select from the existing values (as opposed to manually tying in a year).  this works fine, except that i would like the current year to appear at the top of the list rather than at the bottom.  i have sorted the relationship in descending order based on "year" but it doe snot change the order in which this value list displays.  is there a way to fix this or another simple way to generate a value list that would contain only existing "year" values and sort in descending order?  any thoughts would be greatly appreciated.

        • 1. Re: sorting value list based on field values
          comment_1

           


          johnhorner wrote:

          i would like the current year to appear at the top of the list rather than at the bottom.


           

          Do you also have data from future years - or is the current year always the highest year?

           


          • 2. Re: sorting value list based on field values
            johnhorner

            no, there are no records for future years so the highest year would always be the current one.

            • 3. Re: sorting value list based on field values
              comment_1

              And what's your earliest year? (sorry, I should have asked that too from the beginning).

              • 4. Re: sorting value list based on field values
                johnhorner

                currently the oldest year is 1999.  ideally the solution would accommodate the import of new records from a previous period (perhaps even with some gaps where there would be no records for a year somewhere in the middle of the list) and, it's probably asking to much, it would even work with a new clone file where the user starts from scratch inputting their own records.  so, ideally, it would be just like the value list which shows only the existing unique values in the year field, but sorted in descending order.  does that make sense?  is this a crazy request?  thanks for your willingness to help with this.

                • 5. Re: sorting value list based on field values
                  comment_1

                  As a rule, a value list based on a field is ALWAYS sorted in ascending order. There is a workaround, but it only works for approximately 100 values - i.e. for about a century worth of data in your case. So let's say you picked 1980 as the earliest year:

                   

                  1. Define a calculation field (result is Text) =

                   

                  Let (
                  n = 2080 - YourYearField
                  ;
                  Substitute ( 10^n - 1 ; "9" ; " " )
                  )

                   

                  Note that the replaceString in the Substitute() function is a space.

                   

                   

                  2. Change your value list to also display values from the calculation field, and to sort values using second field.

                   

                   

                  Now you should be set for the next 70 years...

                   


                  • 6. Re: sorting value list based on field values
                    johnhorner

                    thanks comment.  that is awesome.  that is the kind of trick that i would NEVER come up with on my own!  not that i am still going to be around in 70 years, but is there a reason you don't use an even higher number than 2080?  why not 3000?  i notice that it makes the pop up menu is much wider than normal to accomodate all those spaces (is that why you limit it to 2080, or will that window max out at some predetermined width?).  i might try to tinker with the calculation to reduce the number of spaces necessary to make this work to a minimum.  thanks again!

                    • 7. Re: sorting value list based on field values
                      comment_1

                       


                      johnhorner wrote:

                      why not 3000? 


                       

                      Because Filemaker has a limit on the number of characters it will index. An entry with 120 spaces will be indexed the same as one with 150 spaces - and if you use the spaces as the sort field, only one of these will show up in the value list.

                       


                      • 8. Re: sorting value list based on field values
                        johnhorner

                        thanks comment.  that makes sense.  you have a truly encyclopedic knowledge of filemaker!  i am at once impressed, but at the same time disheartened by the sheer volume of things yet for me to learn.