8 Replies Latest reply on Apr 12, 2010 3:25 PM by DavidPerlman

    Sort by version number?



      Sort by version number?


      In a Filemaker 10 database I have a table with fields that have software-style version numbers; the fields are part of an outline, so they are numbered like:











      If I sort on this field, they come out in the proper order, unless some of the numbers are greater than 9.  So for example I'd see:






      which is not the way I want these to be ordered.  I have been struggling with this for a while but there doesn't seem to be any way to get this to sort properly.  I have been working on trying to calculate a sort key but I can't find any way to get that to work either.


      I do have the ability to work with the individual digits; I originally tried putting them into a return-separated list (rather than using "." as the separator) but that doesn't work at all. I also tried using a space as the separator.  Either way it appears that the sort function ignores everything after the first separator if it's space or return.


      I could convert the digits into letters of the alphabet, which would give me the ability to have it sort properly up to 26 instead of only up to 9, but that's still a pretty small functional limit.


      It seems like what I'm trying to do isn't that outlandish, so I'm hoping this is a familiar question to someone out there!  Thanks very much for any advice!

        • 1. Re: Sort by version number?

          For a range that can go from: n.0.0 to n.999.999 ( IOW if there will be ONLY MAX two dots ) you can try to sort based on a calculated field ( result number ) with calc:


          adj = Substitute ( YourCodeField ; "." ; " " );

          LeftWords ( adj ; 1 )
          Right ( "000" & MiddleWords ( adj ; 2 ; 1 ) ; 3 )
          Right ( "000" & MiddleWords ( adj ; 3 ; 1 ) ; 3 )

          • 2. Re: Sort by version number?

            Hmm, I'd like to be able to have a larger number of dots, which might not be the same from one record to another.

            • 3. Re: Sort by version number?

              So write down some extreme example...


              BTW This recursive Custom Function can handle a code with a generic number of dots and sub-values till 5 digits like:




              SortableCode ( text )


              adj = Substitute ( text ; "." ; " " );
              WordCount ( adj ) ; Right ( "00000" & MiddleWords ( adj ; $cf_SC + 1 ; 1 ) ; 5 ) & SortableCode ( RightWords ( adj ; WordCount ( adj )  - 1 ) ) ;
              Let( $cf_SC = "" ; "" )

              • 4. Re: Sort by version number?

                The table has two self-joins, one for parent node and one for child node.  It's modeled after the employee hierarchy recursive data structure described here:



                So according to that article, it is a "b-tree".


                My application is to keep track of the design of a research study.  So, Study 1 might have 3 different time points: before the treatment, after the treatment, and long-term followup.  Each time point might have several days of testing, and each day of testing might have several different tests being done.  The tests might have sub-sections.  The items at each level are manually numbered in a numeric field: it keeps track of the orders (time1, time2, time3) and (day1, day2) and (test1, test2, test3, test4) and so on.  This information is enough to easily calculate an overall order field in the outline-number format that I described above:


                Study 1         1

                  Time 1        1.1

                    day1        1.1.1

                    day2        1.1.2

                  Time 2        1.2

                    day1        1.2.1

                    day2        1.2.2


                and so on.  The outline numbers in the right column are generated automatically in a calculation field, which uses a recursive calculation up through the hierarchy and only needs to know the number in the name (like the 1 in "day1"; actually I have a separate field for the name and the number but it is the same either way.)

                So, the number of levels of the outline is variable depending on the relationships in the recursive data structure.  In principle this should be enough, since those numbers *could* be sorted, but FileMaker doesn't seem to have a way to actually sort them according to the ordering that makes sense.


                I have been trying to figure out a way to calculate an order number as a single number, instead of the nested outline numbers, but so far it seems like that would be either impossible or incredibly unwieldy.  It would make me soooo happy if there was just a way to get FileMaker to sort my numbers they way they naturally want to be sorted...  

                • 5. Re: Sort by version number?

                  I see that you added more code while I was typing my last reply.  I put in the custom function that you sent, but it doesn't work; when I sort on that code, it puts all the level 1 items first, then all the level 2 items, etc.  In other words it's sorting the tree from root nodes outwards, not in outline order.



                  • 6. Re: Sort by version number?

                    The result of the calculation based on that CF must be text type.

                    • 7. Re: Sort by version number?

                      Just to provide an alternative...


                      You can also define number fields for each "level" of your versions, (1.3.2 becomes field1 = 1, field2 = 3, field3 = 2) and then you would sort on these individual fields.


                      You can do this either by defining several calcuation fields that extract each seperate value or you can have a set of data fields with a calculation field that merges tham all into the combined form.

                      • 8. Re: Sort by version number?

                        hahaha.  OK that should have been obvious, sorry I was being dumb!  Yes now it works of course.