0

# 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.

I am using the free version as we are not a business, only a community group.

TIA

Su

5replies Oldest first
• Oldest first
• Popular
• 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

---

If you sort by this column, it should work fine

Leo

Like
• chair
• chair
• 4 yrs ago
• Reported - view

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 + 1

so, if I sort on the formulae column the records are all jumbled up

Like
• chair
• chair
• 4 yrs ago
• Reported - view

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

Like
• 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

Like
• jel888
• jel888
• 1 mth ago
• Reported - view

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?

Like
Like Follow
• 1 mth agoLast active
• 5Replies
• 2429Views
• 1 Following