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

# Sorting Vacuum Tube Numbers

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

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

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

12B4 .> 124

12SA7 .> 127

19T8 .> 198 ...

• ###### 2. Re: Sorting Vacuum Tube Numbers

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

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

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

12BZ6

12DE6

• ###### 5. Re: Sorting Vacuum Tube Numbers

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

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

Thank you very much.

• ###### 7. Re: Sorting Vacuum Tube Numbers

Sorry, but did you miss my post?

• ###### 8. Re: Sorting Vacuum Tube Numbers

This is a great solution. I will try it.

• ###### 9. Re: Sorting Vacuum Tube Numbers

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

• ###### 10. Re: Sorting Vacuum Tube Numbers

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

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.

Steve

• ###### 12. Re: Sorting Vacuum Tube Numbers

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

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

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