1 2 Previous Next 17 Replies Latest reply on Dec 4, 2016 4:20 AM by Tom_Droz

    Sorting Vacuum Tube Numbers

    elliott6621

      I have a db that keeps track of my vacuum tube inventory. When I sort by tube number, the order is not really by tube number.

      Example:

      0Z4

      12AD6

      12B4

      12SA7

      19T8

      1V2

      24A

      35W4

      3AL5

      50L6GT

      5CL8A

      6146

      6AB4

      8032

       

      1V2 should be just below the 0Z4, and the 3AL5 would be next. The 6146 should be next to last.

       

      Tube numbers will start with 1 or 2 digits, followed by letters, or be 4 digits. I tried unicode sorting but no effect.

      1 record for each tube, 1 field containing the tube number. Other fields in the record are for location, condition, cost, etc.

      Finding records by tube number is of course easy, but printed out lists are not correct.

      I could use a script but not sure where to start. There are about 300 different numbers in this database.

      Any help appreciated.

       

      Thanks

        • 1. Re: Sorting Vacuum Tube Numbers
          David Moyer

          Hi,

          it looks to me like you want to sort by the numbers while ignoring the text.  If this is the case, you can create a calculation field.  Let's call the new field sort_TubeCode and make it equal to GetAsNumber(Table::TubeCodeText).  You can sort by that.  It would convert your first few rows this way:

          0Z4 .> 4

          12AD6 .> 126

          12B4 .> 124

          12SA7 .> 127

          19T8 .> 198 ...

          • 2. Re: Sorting Vacuum Tube Numbers
            tudor

            So you want to sort by the numbers before the first alpha character? In that case create a calculated field that parses that out and sort by it.

             

            Someone else can probably come up with something more elegant, but here's one option. Substitute all letters with a special character like a hyphen, then grab everything to the left of the first occurence. In this example I forced the value to upper case so I didn't have to account for both cases.

             

            Let (

            theTube = Upper ( Tube Number ) ;

             

            Left ( theTube ;

             

            Position (

             

            Substitute ( theTube ;[ "A" ; "-" ] ;[ "B" ; "-" ] ;[ "C" ; "-" ] ;[ "D" ; "-" ] ;[ "E" ; "-" ] ;[ "F" ; "-" ] ;[ "G" ; "-" ] ;[ "H" ; "-" ] ;[ "I" ; "-" ];[ "J" ; "-" ] ;[ "K" ; "-" ] ;[ "L" ; "-" ] ;[ "M" ; "-" ] ;[ "N" ; "-" ] ;[ "O" ; "-" ] ;[ "P" ; "-" ] ;[ "Q" ; "-" ] ;[ "R" ; "-" ] ;[ "S" ; "-" ] ;[ "T" ; "-" ] ;[ "U" ; "-" ] ;[ "V" ; "-" ] ;[ "W" ; "-" ] ;[ "X" ; "-" ] ;[ "Y" ; "-" ] ;[ "Z" ; "-" ]) ;

             

            "-" ; 1 ; 1 ) - 1

             

            )

             

            )

            • 3. Re: Sorting Vacuum Tube Numbers
              erolst

              You could try this to create a new calc field:

               

              Let ( [

                tn = yourTubeNumberField ;

                s = Middle ( tn ; 2 ; 1 ) ;

                sorter = Case ( GetAsNumber ( s ) = second ; s + 40 ; Code ( Upper ( second ) ) - 55 )

                ] ;

                Replace ( tn ; 2 ; 1 ; sorter )

              )

               

              Untested.

              1 of 1 people found this helpful
              • 4. Re: Sorting Vacuum Tube Numbers
                elliott6621

                My example was not very good. This would work if not for tube numbers like:

                12AD6

                12BZ6

                12DE6

                • 5. Re: Sorting Vacuum Tube Numbers
                  tudor

                  So after sorting by the numeric portion, you want it sorted by the alpha portion? Perhaps you could substitute the letters with numbers (A becomes 1, B is 2, etc.) in your calculated sorting field.

                  • 6. Re: Sorting Vacuum Tube Numbers
                    elliott6621

                    Oh, I like that. I'll try it.

                    Thank you very much.

                    • 7. Re: Sorting Vacuum Tube Numbers
                      erolst

                      Sorry, but did you miss my post?

                      • 8. Re: Sorting Vacuum Tube Numbers
                        elliott6621

                        This is a great solution. I will try it.

                        • 9. Re: Sorting Vacuum Tube Numbers
                          elliott6621

                          I did miss it. This seems to be a more elegant solution. I'll give it a try.

                          Thanks for your help.

                          • 10. Re: Sorting Vacuum Tube Numbers
                            David Moyer

                            Now I'm starting to think that you just lead a leading zero when the there's a single number followed by letters.  If so, you can use this for sorting:

                            Let(

                              [

                                code = UPPER("3a5");

                                letters = Filter(code; "ABCDEFGHIJKLMNOPQRSTUVWXYZ");

                                start = Position(code; letters; 1; 1)

                              ];

                              If(start = 2; "0" & code; code)

                            )

                            • 11. Re: Sorting Vacuum Tube Numbers
                              elliott6621

                              Another great option. I'm not that good at deciphering the more complicated

                              statements, but I will try everything and then will unpack it to understand

                              it better.

                              Thank you for your help.

                              Steve

                              • 12. Re: Sorting Vacuum Tube Numbers
                                David Moyer

                                if the leading zero does, in fact, work, then I'd combine erol's answer in #3 with my unreliable answer in #10.  I added some comments to help ...

                                Let(

                                  [

                                    code = TO::TheCode;

                                    secondDigit = Middle(code; 2; 1)

                                  ];

                                  // if the second character in the string is a number, leave as is

                                  // else it's a letter, prepend with a zero

                                  If(secondDigit = GetAsNumber(secondDigit);

                                    code;

                                    "0" & code

                                  )

                                )

                                • 13. Re: Sorting Vacuum Tube Numbers
                                  erolst

                                  elliott6621 wrote:

                                  I'm not that good at deciphering the more complicated

                                  statements

                                   

                                  For what's it worth, here's my calculation again, with an error corrected (wrong var reference) and some comments to explain the logic behind it:

                                   

                                  Let ( [

                                    tn = yourTubeNumberField ;

                                    s = Middle ( tn ; 2 ; 1 ) ; // grab the second char

                                    isNumber = s = GetAsNumber ( s ) ; // store the fact of its char-ity / number-ness in a variable

                                    sorter = Case ( isNumber ; s + 40 ; Code ( Upper ( s ) ) - 55 )

                                    /* create a sorting aid by shifting nums up, and alpha Code() value down (two chars in both cases)

                                    eg Z becomes 35, and 0 becomes 40, so 7Z = 735 sorts before 70 = 740 */

                                    ] ;

                                    Replace ( tn ; 2 ; 1 ; sorter )

                                    // replace the original char (one char at second position) with the calc'ed sorter (two chars)

                                  )

                                   

                                  The longish comments and the fact that I used a variable to store the result of the number test, instead of using it directly, makes this look more verbose and complex than it is. Don't fear the parser!

                                   

                                  EDIT: oops, I did it again - used two names for the same variable corrected.

                                  • 14. Re: Sorting Vacuum Tube Numbers
                                    user19752

                                    You may need sort it on max 4 of parts, numbers, alphabets, numbers, alphabets. So the answer is concatenate 4 parts, each have same length in all data (or easier way is using 4 fields for each part). First part have 4 as max length, others may also have 4, is this correct?

                                     

                                    erolsts' idea is very cool, but I think it is depend on the limited example in 1st post.

                                    Seeing here, List of vacuum tubes - Wikipedia

                                    There is very rare (only one?) case that has 2 digit 3rd part

                                    6ME10

                                    it is sorted after

                                    6ME5

                                    in the list.

                                    But I don't know how it should be sorted in PO's table

                                     

                                    erolst, there leaves a wrong reference (second should be s).

                                    1 2 Previous Next