2 Replies Latest reply on Nov 29, 2012 11:52 AM by kashaev

    Sorting issue Numbers with Letters



      Sorting issue Numbers with Letters


           I have a DB of equipment items on a project.  Each item is assigned a number 100-999...sometimes there are "accessories" to the item and the number becomes 100a...100b...100c.  The field is actually set up as a text field to allow for this.  When printing and viewing on the screen the items are sorted by project number and then item number, no problem.  SO..I have my first project that goes above 999 I have 1000 and up to 1105.  Now when it sorts it mixes the 1000s and 1100s in with the 100s. Example:   The order no goes  100, 1000, 1002, 1003.....1009, 101, 102, 103...1100, 1101, 1102...

           I assume this is becuase FM is seeing this information as text instead of a number.  But if I switch to the field being a number I cant use 100a,  100b.....

           Is it possible to have the best of both worlds here?


        • 1. Re: Sorting issue Numbers with Letters

               SImplest would be to separate the number and letter into two separate fields. Then you can sort first on the number field and then on the letter field.

               If you keep this data in a single field, you'll need to sort on a calculationfield that appends some leading zeroes to keep the numerical part of the number to the same number of digits.

               Let ( [ T = TextField ;
                         N = GetAsNumber ( T ) ;
                         L = If ( N ≠ T ; Right ( T ; 1 ) ; " " )
                         Right ( "000" & N ; 6 ) & L

               The calculation assumes that no more than one character is used as a letter and will fail when the numerical portion exceedds 999,999.

          • 2. Re: Sorting issue Numbers with Letters