1 Reply Latest reply on Feb 5, 2014 3:37 PM by philmodjunk

    FMP 12 Sort Function

    RishiTrikha

      Title

      FMP 12 Sort Function

      Your post

           I have a question about the sort function. I have my key field as and ID number as a number field. However, the ID number can also contain a letter, for example E3 or B79. So if I have a data set {14, 1, B1, 2, 5, 23, 109, B4, PRECISE 1}, and I try to sort ascendingly it will sort as {PRECISE 1, 1, B1, 2, B4, 5, 14, 23, 109}, however I would like it to sort as {1, 2, 5, 14, 23, 109, B1, B4, PRECISE 1} or {1, 2, 5, 14, 23, 109, PRECISE 1, B1, B4}, how can I make this happen.

           Best,

           Rishi

        • 1. Re: FMP 12 Sort Function
          philmodjunk

               You'll need to use a calculation field to combine the digits and text in a format that will produce the correct order when sorted by text. Something like:

               Right ( "00000" & GetAsNumber ( YourFieldHere ) ; 6 ) & Filter ( YourFieldHere ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

               That would produce the text:

               { 000001, 000002, 000005, 000014, 000023, 000109, 000001B, 000001PRECISE, 000004B }

               This field need not be placed on any layout, just specfy when sorting your records.