Repeaters, sort by tone

[UncleYoda]

Sort repeaters by tone, list is out of order.   Same on both in and out.

 

By the way, why dark gray background and black text?  (maybe I missed a theme setting, but I can override if needed)

 

[Socalgmrs]

Why would you want to sort by tone.  Seams like zero usable info.    

[UncleYoda]

1 hour ago, Socalgmrs said:

Why would you want to sort by tone.  Seams like zero usable info.    

An explanation is kind of overkill for this.  If it's a sortable field, it should sort correctly.

 

But for curiosity sake, the tone sort was related to my continuing search for a repeater that could be a match for what I'm hearing.  It was just to make it easier to see if I had tried the likely ones.  I gave up on the CW ID approach because without a tone I keep getting the ID from the repeater I already know not the one the unidentified conversation is coming from.  Tone scan also fails on the weak signal.  So I'm back to getting the user IDs copied correctly.

[hxpx]

That's a perfectly normal sort order... for a text/string field. It's comparing by character, your descending sort looks for Z-A then 9-0. "9" is greater than "12345".

[UncleYoda]

Yes, if you ignore the numerical value of the numbers.  Not as useful or what people would expect.  But it is a less used search type so maybe not worth improving the code.

[SteveShannon]

45 minutes ago, UncleYoda said:

Yes, if you ignore the numerical value of the numbers.  Not as useful or what people would expect.  But it is a less used search type so maybe not worth improving the code.

Only the CTCSS tones are numeric values.  Because that field also includes DTCSS values as well as words and abbreviations “Unlisted”, “No Tone”, “Hz”, “DPL”, etc, the field is alphabetical text field. But you could run a report and handle the extract however you wanted.

[hxpx]

3 hours ago, UncleYoda said:

Yes, if you ignore the numerical value of the numbers.  Not as useful or what people would expect.  But it is a less used search type so maybe not worth improving the code.

I don't know radios but I do know software, so I'm gonna nerd out for a minute:

No, as you noticed, sorting numbers stored in a text field does not always return useful results. Fields in a database have a specific type - numeric fields can only have numbers, text fields can only have text, then you've got stuff like dates, timestamps, booleans (true/false which is sometimes stored as 1 or 0). You can't have combinations of types in a column in most databases so if you have text and numbers in a list, everything is stored as text and the sorting has to go by character value, which is when you wind up with "111" coming before "9" just because of the first character in the string. And if you do manage to squash two lists together and end up with text and numeric values (like [67, 77, "Unlisted"]), the software is probably going to complain that your array can't be converted to a common type or it'll do something weird like calculate a numeric value for "Unlisted" based on the numeric value of each character or turn the numbers back into strings, and then you're back to a weird sort order. It's dumb sometimes and we know it's dumb, but that's how this stuff works.

That said, there's three common ways to implement a sort that does what you want it to do:

• One way is to pad out your numbers so they're always a fixed number of digits ("77 Hz" becomes "077.0 Hz", "251.4 Hz" can stay as is) so that the hundreds digit is always there to be sorted first, making string sorting work correctly. This lets you use pull values straight out of the database without doing anything extra behind the scenes to sort it, but it's still a little janky because most people don't use leading zeroes when writing out numbers and you'll end up with DPL and CTCSS tones mixed in because "071 DPL" and "077.0 Hz" both come before "251.4 Hz". You could flip stuff around like 'DPL 071' and 'Hz 077.0' or put a prefix like 'CTCSS 077.0 Hz' to enforce grouping, but that doesn't always look right, either.
   
• Second way is to store your numeric values in separate fields - CTCSS becomes its own numeric column, DPL becomes its own column, and then you use something like 0 for "No Tone" and null for Unlisted. You can tack on ' Hz' or ' DPL' in the display. This works if you don't mind having multiple columns instead of a single one - would probably work for RX/TX tones as long as you don't mind having four columns instead of two (CTCSS Rx, CTCSS Tx, DPL Rx, DPL Tx instead of just Rx and Tx).
   
• Last way (and probably the best if you want a single display column) is having a hidden index for the values that is numeric, and then you sort by that index - you could map CTCSS frequencies directly ("77.0 Hz" becomes 77) and assign text values obviously high/low values to force them to be at the top or bottom of the list. Unlisted would end up as 999999 if you want it to be last when you sort by ascending, or -999999 if you want it to be first. That gets stored in a separate table and then you do a little magic behind the scenes to sort the list by index before being displayed.
  
  This also lets you do do stuff like grouping - you can stick a numeric prefix on things so they're always together. If you pad out all the numbers so they're 6 digits, you can make your CTCSS tones start with 100 (so "77.0 Hz" becomes 100077) and your DPL tones start with 500 ("071 DPL" becomes 500071), when you sort, your CTCSS and DPL tones will always be in their 100xxx and 500xxx groups. Plus you can do stuff like custom sort orders - giving "No Tone" an index of 0 and "Unlisted" an index of 999999 lets you put them at the start and end of the list, with all the numeric values in between.
  
  This looks a lot cleaner and you have more control over your sort order but now you have to maintain a table of index/display values, so if you have a column with values A, B, C and indexes of A = 1, B = 2, C = 3, and someone wants to add a record with value D, you have to remember to add D = 4 to the the index list or you'll end up with a null index value and it gets displayed in an unexpected spot in the list (usually first).

No idea what the software currently supports, but implementing any of the solutions are going to have trade-offs. The good thing about GMRS tones is its a fixed list of values so there wouldn't be much maintenance of the index, but implementing it in the first place is probably a bit of a hassle.

Dealing with this kind of stuff is why they pay me the big medium bucks.

Anyway. That's a lot of text to say "yes, storing numbers in a text field gets weird when you sort them and we know it sucks and there are ways around it but it might not be worth dealing with it". Thank you for coming to my TED Talk and letting me rant about sorting instead of working.