How to sort alphanumerically
Hello there,
I have only only recently started using Ninox and I'm finding the user manual a little difficult to understand.
I have created a database to manage a gardening associations membership.
I have 2 tables, 'members' and 'plots'. The Plots are numbered alphanumerically and some are split, some are not, so we have the following plot numbers;
1
2a
2b
3a
3b
4
5 and so on to 47b with around 90 full or half plots.
when I apply the sort ascending I can see the plots sorted as
1
10a
10b
11
12
instead like my first example above.
I couldn't add the plot numbers as a number field because of the lettering so it is a text field but there is no alphanumeric way of sorting these logically.
please could you advise how I achieve this?
I am using the free version as we are not a business, only a community group.
TIA
Su
5 replies

Hi,
You can insert an additional column with the following formula:

let myD := number(extractx('plot number', "\d+"));
let myT := extractx('plot number', "\D+");
cnt((select 'your table')[number(extractx('plot number', "\d+")) < myD]) + if cnt((select 'your table')[number(extractx('plot number', "\d+")) = myD]) > 1 then
cnt((select 'your table')[number(extractx('plot number', "\d+")) = myD and extractx('plot number', "\D+") <= myT])
else
0
end + 1
'your table' and 'plot number' should be replaced by your names.
If you sort by this column, it should work fine
Leo

Thanks Leonard,
that has sort of worked, but not quite and it's probably that I don't follow what the code was doing so I can't troubleshoot it.
heres what I can see: (I hope the image turns out right  it looks squashed in my window)
And this is the revised code you kindly provided: I have made the names bold in this example to show what I changed.
let myD := number(extractx('Plot No', "\d+"));
let myT := extractx('Plot No', "\D+");
cnt((select 'Plots')[number(extractx('Plot No', "\d+")) < myD]) + if cnt((select 'Plots')[number(extractx('Plot No', "\d+")) = myD]) > 1 then
cnt((select 'Plots')[number(extractx('Plot No', "\d+")) = myD and extractx('Plot No', "\D+") <= myT])
else
0
end + 1so, if I sort on the formulae column the records are all jumbled up

Actually, no, I think it has worked but I'm missing some records  I may have been too judicious when I deleted some records earlier in a tidy up.
thank you so much for your help
best wishes,
Su

Hi Su,
you should sort by the column Formula.

let myD := number(extractx('Plot No', "\d+"));
let myT := extractx('Plot No', "\D+");
cnt((select Plots)[number(extractx('Plot No', "\d+")) < myD]) + if cnt((select Plots)[number(extractx('Plot No', "\d+")) = myD]) > 1 then
cnt((select Plots)[number(extractx('Plot No', "\d+")) = myD and extractx('Plot No', "\D+") <= myT])
else
0
end + 1
Leo

Hello,
I have exactly the same issue using the same numbers in front of text to order my questionnaire (questions field). When I put either formula in the f(x) field for the Question field (which is the one I want to sort by) red alerts show for certains lines and the "d+". How do I fix the formula to sort correctly?
Content aside
 2 yrs agoLast active
 5Replies
 2466Views

1
Following