Further testing reveals that setting a formula field in the parent table equal to number(children.Id) and then sorting on that formula field results in an alpha sort, but sorting on number(formula) produces the correct numeric sort.

Ok, so to get a numeric sort, you HAVE to use number() even when the field is already a number, but that only works for a field in the table being selected, i.e. the parent table in this case. Using the same number() coercion on a number field (not just Id) from the child table results in an alpha sort.

This is wrong. Using 'order by' number(field) should ALWAYS produce a numeric sort, no matter where that field is.

This is preventing me from doing what I need to do. I HAVE to be able to list those parent records ordered NUMERICALLY by the child.Id. The order is all important.

Don't suppose anyone from Ninox bothers to read these forums nowadays. ☹️

I got around the problem by selecting on the child table, ordered by its own number(Id) which produces the correct numeric sort. 🙂

However, that doesn't take away from the fact that it should be possible to select on a parent table and order numerically by a child.number(field). Ninox is doing that wrong.

Thanks guys, I do understand that Id is not truly a number field, but in this context it would seem to make sense that it were treated as a number. However…

That aside, as I pointed out, irrespective of what Id actually is, although number(Id) should produce a numerically sorted result, it does not when the Id is in a child table. As I discovered, it does when it is the Id of the table which is the subject of the 'select', but when it's from a child table the result is an alpha sort order.

I struggled with this for some hours before realising it just would not work and switched to selecting from the child table, ordered by its own number(Id) and although producing the exact same data, the resulting list was numerically sorted as required.

This was completely repeatable and I tried with other numeric fields, not the Id. Well, they were formula fields set to number(Id) which should mean they are numeric data, but in every case, if selecting the parent and 'order by' the child.field or even child.number(field), the results are alpha sorted, but if the select is on the child table, either of those 'order by' statements (i.e. using its own field or number(field) )  produce a numeric sort.

This is the Mac app in MaOS 26. Maybe the cloud version is different. Wouldn't be the first time.

Thinking further, the child.Id will be an array since there could be many child records pointing to the same parent. In my case there were not, as additional code ensures it is a 1 to 1, but in the context of this selection, Ninox doesn't know that.

So trying to numerically sort by the child.Id is actually trying to sort by an array and even child.number(Id) is then probably not truly a number. This situation does not arise when the select is on the child table and 'order by' on its own single Id, no array in the mix.

This did occur to me before, hence why I tried to throw a first() in there, but maybe I didn't try hard enough. Maybe there is a way to do it with the correct combination of number() and first() and I just moved on before finding that particular combination. I may have only tried first(number()), whereas number(first()) would probably be the right way.

Or maybe there is some inherent reason why Ninox cannot deal with coercing an array of child.Ids to a number. However, it does seem to me that this difference between the 2 methods is the most likely cause of the problem.

As I said though, by selecting on the child table it does all work perfectly and in truth is probably the better way to perform the select, whatever the ordering required, so am happy to keep it like that and try to remember this potential issue so as not to waste time on it if it ever crops up again.