3 Replies Latest reply on Mar 2, 2011 9:56 AM by philmodjunk

    Sorting alphanumeric strings



      Sorting alphanumeric strings


      We use Filemaker Pro in our libarary, and the call numbers always start with text and sometimes end with text.  The number of letters and numbers varies from one to four.   For example:

      C 1001 A

      C 1001 B

      W MS 209

      M 317

      C MS 3

      I've been successful at getting it to sort everything but the suffix.  I can get it to sort the suffix properly if all of the prefix letters are the same (i.e., C 1, C 1 A, C 17, C 29, etc.)

      Is there a way to sort text-number-text?

      Thanks for your ideas!


        • 1. Re: Sorting alphanumeric strings

          Use fields to split the three values into two text and one number field. Sort on these fields instead of this combined field.

          You can either have three fields for the three values and use a fourth calculation field to combine them into your current format for display and search purposes or you can keep this in one field and define three calculation fields that extract the data.

          Calculations that extract the data:

          Prefix:  LeftWords(combinedfield ; 1 )

          Number:  GetAsNumber(combinedfield)

          Suffix:  RightWords ( combinedField ; 1 )

          A calculation that combines the data from three separate fields:

          Prefixfield & " " & Numberfield & " " & Suffixfield

          • 2. Re: Sorting alphanumeric strings

            Well, that works as long as there is only one prefix letter in the call number.  This works for the prefix and will return the exact letters: 

            Left(Left(call_number; Length(call_number)-Length(GetAsText(GetAsNumber(record number)))-1);4)

            I need something like this for the suffix word, perhaps?

            • 3. Re: Sorting alphanumeric strings

              Well, that works as long as there is only one prefix letter in the call number.

              You have confused the function LeftWords with the function Left.

              LeftWords( field ; 1 ) will return all characters up to the first space. So this will work with any number of letters in that first part.