Sorting a Text Column in a Table View At Runtime

[Nigel_King]

Hi

I'm seeing some unexpected results when a user had clicking on a column name in a Table View to sort by that Column.
The Column is a Text column but some of the rows are appearing out of the order I would expect.
I have highlighted one such row in the screenshot below.

I have checked the data in the source sheet for extra characters and spaces etc and found none.

Does anyone know why this might be happening?

Regards

Nigel King

[TeeSee1 · Accepted solution]

It seems that AppSheet interprets numerical digits as numbers and sort numerically.

So it sorts as in this order for example even when they appear within alpha numeric text.

0 -> 1 -> 002 -> 9 -> 10 -> 50 -> 101 etc

[Nigel_King]

Are there any options or other Column types that would make it sort by text/character order only?

[Nigel_King]

I have added a request to the "Feature Ideas" section for detecting Hex numbers in strings now.

[Suvrutt_Gurjar]

If for some reason it is a must to sort by hexadecimal numbers, then as a workaround you could convert the hex numbers to decimal. The expression will be a bit longish but possible to construct. The tip below is to convert decimal numbers to hexadecimal but the reverse is possible too.

Convert RGB to Hex (decimal to hexadecimal) - Google Cloud Community

You can then sort the rows by that decimal number. You could hide that decimal number with show_if constraint but still use that number to sort ascending/descending in the summary view.

[Nigel_King]

Thanks for this @Suvrutt_Gurjar .
In the particular case I have the field does not always contain a hex number though.

[Suvrutt_Gurjar]

Oh okay, thank you for the update. Just to extend the discussion, you could possibly add some logic if feasible to detect hex numbers.

For example if the letters A to F are present , means the  field contains hex numbers. Of course feasibility will depend on the field's string pattern and the expression may become still more complex. Also,  it may or may not be worth the effort if it is not a critical requirement.

[Nigel_King]

Yes, that might work for determining the default sort order in a View.
In my case here, the issue was brought to me by a user who had clicked on the Column heading in a Table View to perform the Sort.

It looks like were just going to have to live with this as is, but at least I can now make the users aware of what is happening and why.

[dbaum]

If you didn't already, I suggest report this to AppSheet support. I consider it a bug that standard alphanumeric sorting conventions are not applied to textual values.

[Steve]

Note that the behavior you're seeing is quite intentional. AppSheet developers thought "no-code" app creators would expect numbers to be handled as numbers and not as characters in a text string. Great for no-code app creators, bad for everyone else.

[Nigel_King]

Yes, the problem is that it only works with decimal numbers and not any other base.

[dbaum]

---

@Steve wrote:

AppSheet developers thought "no-code" app creators would expect numbers to be handled as numbers and not as characters in a text string

---

Even amidst non-numeric characters--as in the OP and the reply marked as a solution? That strikes me as likely confusing to anyone.