Sort properly - 12 is less than 2. I don't think so!
I have a column which is sorted assending: A/1, A/10, A/11, A/2, A/3.
Can you see what's wrong!
The column is a formula - 'folder' + "/" + 'page' - in case that's causing my trouble. In any case I want to see A/1, A/2, A/3 ..... A/9, A/10, A/11 etc.
How!
Thanks for your pointers or help in advance.
6 replies
-
You are running into the issue of how Ninox sorts text vs. numbers. Your formula is considered text by Ninox so it sorts all the 1's then 2's.
To fix this you need to add a zero in front of single digit numbers. You can try something like:
if page < 10 then
folder + "/" + 0 + page
else
folder + "/" + page
end
So you should see A/01,A/02...A/10,A/11...A/20,A/21...etc.
Good luck and let us know how it goes.
-
Ah, yes, thank you for the code. I was hoping to retain my original A/1 style but if I can't I can't. Meanwhile I've been sorting by ID but if I add an earlier page number later - well you know that will go wrong too.
Could / should Ninox be able to treat or sort some columns as numbers not text? Something to suggest?
Meanwhile I'll use your code! Thanks again.
-
You have text in your formula so I don't see how Ninox can treat it otherwise. Excel does the same thing.
I have a formula that puts together a series of numbers that look like normal numbers, i.e. 100,045,45.11, but when I try converting it into a number it gives me a zero. I think Ninox should be able to see that a field looks like numbers and allow me to convert it to numbers. But that is another issue.
-
Thanks Fred the formula works for me. But... I have a couple of folders with just over 100 pages so I may have to modify it to take that into account. (I see how, very simple.)
"You have text in your formula so I don't see how Ninox can treat it otherwise. Excel does the same thing."
I can't say I'm an Excel user but I've never really noticed this before using Apple Numbers for example. I also can't say I'm a programmer but if an application was to look at a string and see Letter, Letter, Number, Number could it not decide to treat two numbers as a single number and sort acordingly? AB12: sort A, then sort B then, oh look two numbers don't sort 1 sort 12. Seems simple to me so it must be wrong!
"I think Ninox should be able to see that a field looks like numbers and allow me to convert it to numbers."
My recolection of Numbers.app is I can change a field to be text or a number. I'd have to go and look as you tend to take some things for granted until they go wrong.
-
"but if an application was to look at a string and see Letter, Letter, Number, Number could it not decide to treat two numbers as a single number and sort acordingly?"
Well now you are thinking like a human and not like a computer. :)
I guess we haven't come up with a way to tell computers to think like humans and be able to sort text and numbers together. It makes logical sense from a computer point of view how it sorts "text" data. It is doing exactly what we tell it to do, i.e. first sort by the first character, then the 2nd, then the 3rd, etc.
Maybe one day computers will be smart enough, but maybe we don't want them that smart. :)
-
If you prefer to keep the original style of your references, you can define another field for sorting. This field can be a formula producing correctly sortable values from your original references. When I encounter such a problem, I use a formula like:
let nd := 4;replacex(replacex(Reference, "([^0-9])([0-9]{1," + nd + "})(?![0-9])", "g", "$1" + lpad("", nd - 1, "0") + "$2"), "0*([0-9]{" + nd + "})", "g", "$1")"Reference" is the name of the original reference field. The purpose of this formula is to replace all occurences of integers occuring in the original reference and consisting of one to four digits by four-digit, zero-prefixed numbers. If the original reference may contain longer numbers, the formula must be adjusted by changing the value affected to "nd".
Content aside
- 2 yrs agoLast active
- 6Replies
- 222Views