8 Replies Latest reply on Feb 22, 2011 10:00 AM by Davisms

    sort -- number with letters

    firth5

      Title

      sort -- number with letters

      Post

      I've got a field that always has numbers, but also occasionally has letters.

       

      I'd like it to sort so that the numbers are always in order, but that when the letters appear, those sort as well.

       

      for example:

       

      101, 100, 103, 101A, P103, 101C, 101b, 103A

       

      would sort as follows:

       

      100

      101

      101a

      101b

      101c

      103

      P103

      103a

       

       

      If I make the field a NUMBER field, it ignores all the letters completely.

      If I make it a TEXT field, it sorts everything correctly, but puts the "P" prefixes at the end of the list. 

       

      I invite your thoughts.

       

      Thanks,

      eric 

        • 1. Re: sort -- number with letters
          mrvodka
             Well if you cant split those off, you could create two calc fields that capture the number and text. Then you could sort by the number first and then the text.
          • 2. Re: sort -- number with letters
            philmodjunk
              

            Define calculation fields that separate your number and text portions into separate fields and sort on these fields instead.

             

            cNumberKey: getasnumber (AlphaNumberField)

            cLetterKey : Filter ( Right ( AlphaNumberField ; 1 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" )

             

            Your sort order:

            cNumberKey

            cLetterKey

            • 3. Re: sort -- number with letters
              firth5
                

              If I'm understanding the cLetterKey correctly, it would just take 1 digit starting from the Right (if it's a letter).

               

              With the understanding that the main number is always 3 digits, 

              We sometimes have the following come up:

               

              A) 

              101AA 

               

              or

               

              B) 

              101A1

              101A2

               

               

              In the case of A) I would guess that we could do  

               

              cLetterKey : Filter ( Right ( AlphaNumberField ; 2 ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) 

               

              correct? 

               

               

              But for B), would I need to write an elaborate IF script?  I presume so.

               

               

              (Currently, there is too much history with the database before I came on to separate the AlphaNumberField into 3 or 4 separate fields (Prefix / Number / Suffix, or something to that effect.  However, if it becomes too cumbersome, I may lobby for it.) 

              • 4. Re: sort -- number with letters
                philmodjunk
                  

                But for B), would I need to write an elaborate IF script?  I presume so.

                 

                Not necessarily. Have you now posted all the possible permutations to your numbers or are there other combinations possible?

                 

                PS. I'd lobby and lobby hard for replacing the current set up with separate data fields.

                • 5. Re: sort -- number with letters
                  firth5
                    

                  Ok, here are the various options:

                   

                  N = Number  (Always 4 digits)

                  A = Letter

                   

                  NNNN

                  NNNNA

                  NNNNAN 

                  NNNNAA

                   

                  ANNNN

                  ANNNNA

                  ANNNNAN  

                  ANNNNAA

                   

                  AANNNN

                  AANNNNA

                  AANNNNAN  

                  AANNNNAA 

                   

                   

                  Thanks in advance! 

                  • 6. Re: sort -- number with letters
                    philmodjunk
                      

                    So preceding letters are ignored, first number section is always 4 digits followed by 0 to two digits of either letters, or one letter and one number....

                     

                    The first field, NumberSection, could be evaluated as Left ( Filter ( AlphaNumeric ; "0123456789" ) ; 4 ) /* left 4 digits drops out any trailing digits */

                     

                    For the second portion:

                     

                    Middle ( AlphaNumeric ; Position ( AlphaNumeric ; NumberSection ; 1 ; 1 ) + 4 ) ; 2 )

                     

                    Assuming you want values to sort in ascending order like this:

                     

                    1001

                    1001A

                    X1001A1

                    1001AA

                     

                    That should work for you.

                     

                    • 7. Re: sort -- number with letters
                      firth5
                        

                      A true wizard indeed....

                       

                      :)

                       

                      • 8. Re: sort -- number with letters
                        Davisms

                        We've been without a Filemaker Pro tech support person for quite a while, and this particular issue has been troublesome for me.  I plugged the fields into the program and *voila* they worked!  Thank you!