12 Replies Latest reply on Dec 22, 2015 6:27 AM by gilo

    sort value list

    gilo

      Hi

       

      I have a value list, which contains the month and year of records. So I have 10/14 11/14 12/14 01/15 02/15 03/15 04/15....

       

      Now I have a dropdown list which shows these values. unfotunately, FM is sorting by the first letters so i have

      10/14

      10/15

      11/14

      11/15

       

      Is there a way to have FM sort them so that i get

      10/14

      11/14

      12/14

      01/15

      02/15

      03/15.....

        • 1. Re: sort value list
          arehkopf

          create a second field that calculated the date in the format: YY & "/" & MM

          Show only one of them in your valuelist and use the other one to sort the values.

          • 2. Re: sort value list
            gilo

            thanks but does not work as wished...

             

            If I use the first field as sorting criteria and the second to show the date I get

             

                           1410     10/14

                           1411     11/14

                           1412     12/14

                           1510     10/15

                           1511     11/15

                           1512     12/15
            which looks not so good.

             

            If I then select "only show second field", I can only sort with the second field. Then I get

             

                           10/14

                           10/15

                            11/14

                            11/15

                           12/14

                           12/15

             

            :-(

            • 3. Re: sort value list
              pjanssen

              I think a solution to your problem can be found here:

               

              http://filemakerhacks.com/2013/11/22/its-sorta-a-value-list-thing/

              • 4. Re: sort value list
                gilo

                Thank you for your reply

                 

                 

                Unfortunately, this example goes way over my filemaker know-how. Especially because in the example they use years and I have month/year combination. Furtheron, they use single records, i have multiple records with 10/15 for ex.

                 

                So I don't see how I can apply this to my DB

                 

                 

                But thanx anyway

                • 5. Re: sort value list
                  gilo

                  Can someone tell me what

                   

                  Replace ( 10 ^ repeatCount - 1 ; 9 ; text )

                   

                   

                  for what stands the sign ^????

                  • 6. Re: sort value list
                    jbante

                    The carat works in FileMaker calculations as the exponentiation operator; it's saying "Raise 10 to the repeatCount power". The effect of the whole calculation is a sneaky way to repeat "text" repeatCount times.

                    • 7. Re: sort value list
                      ch0c0halic

                      This process only works up to 10^8. When the result of the exponential value is represented in notation it fails completely.

                       

                      10^9 = 1.0e+9

                       

                      So you cannot rely on this to generate the RepeatCount for the Substitute() if the number is over 8.

                      • 8. Re: sort value list
                        jbante

                        Are you sure? Yes, 10^9 = 1.0e+9, but 10^9 - 1 = 999999999. In my testing just now, it works all the way up to 404. (This surprised me; I expected it to fail after 399, due to FileMaker's documented limit of 400 digits.)

                        • 9. Re: sort value list
                          gilo

                          ok thanx guys I get the ^

                           

                           

                          but this gives me something else to wonder about:

                           

                          I have a function called repeat, with text and repeatCount as var:

                          // Suggested by Nils Waldherr:

                           

                           

                          Austauschen ( 10 ^ repeatCount - 1 ; 9 ; text )

                           

                           

                           

                           

                          // The recursive way...

                          // Case ( repeatCount > 0 ; text & Repeat ( text ; repeatCount - 1 ) )

                           

                           

                          Then I have a field with a formula, to invert the order:

                           

                          Repeat ( Char ( 65279 )  ; SetVar ( [a = LeftValues ( mois ; 2 );b =rightValues ( mois ; 2 ); c = "300"]; c - a * b) ) & mois

                           

                           

                          If c is bigger than 300, this field gives me back ?mois. why is this? LeftValues ( mois ; 2 ) = 10 and rightValues ( mois ; 2 ) = 15.

                          with c < 300, it works perfectly

                          • 10. Re: sort value list
                            gilo

                            are there any other helps with sorting value lists?

                            • 11. Re: sort value list
                              beverly

                              make the values, as I often do to be YYYY_MM and then they sort 'alphabetically'. You can always "parse" if needed. I create an auto-enter (text) field to be this base on any date field:

                               

                              yyyymm = GetAsText( Year( myDateFld ) & "_" & Right( "00" & Month( myDateFld ) ; 2 )

                               

                              you may optionally choose to not include the underscore (YYYYMM) or use a space (YYYY MM). These all sort correctly - for 'readability', I prefer the underscore. Teach your users to select based on the year being before the month.

                               

                              I use these for charting and then have the labels be parsed out to show JAN 2000 instead of 2000_01, for example.

                               

                              These fields are also handy when needing to sort for reports that need month & year sub-summaries. As well as the use in value-lists.

                              beverly

                              • 12. Re: sort value list
                                gilo

                                hello Beverly

                                 

                                Thank you for your response, but it still sorts my list ascending instead of descending

                                 

                                2014/01

                                2014/02

                                ....

                                2015/11

                                2015/12

                                 

                                I would like to have the most recent on top

                                 

                                2015/12

                                2015/11

                                ....

                                2014/02

                                2014/01

                                 

                                 

                                I saw a file from filemackerhack, but it's way to hard for me to understand and to make it work for me. If there is a tutorial out there on how to built this on my own....