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 )
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.
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 = "" ; "" )
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
Time 2 1.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...
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.
The result of the calculation based on that CF must be text type.
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.
hahaha. OK that should have been obvious, sorry I was being dumb! Yes now it works of course.