0

numeric order by child table field

This is not about actual families, but just illustrates the structure of the problem I am dealing with and my own actual tables and fields do not obscure the issue.

I have a parent and a child tables and parents have 0 or 1 child. I'm trying to get a list of parents who have a child and sorted by the child.Id field . So with 'children' being the relationship field FROM the child table:-

select parent where children order by children.Id

 

 

Easy enough and at first all looked good, but later I realised not so good when the number of parents with a child > 9.

You might guess where this is going.

I'm currently working with 12 such parents and the sort order I get is 1,10,11,12,2,3 etc … So the sorting is being done alphabetically on the child's Id field. Apart from thinking this being someone bizarre as the Id field is never text, I cannot seem to get over this problem.

I've tried children.number(Id) and number(children.Id) and then throwing a first() in there in case the possibility of multiples (although none) is causing the problem, but nothing makes any difference.

I know the 'order by' clause is being enacted as if I take it out, the order is quite different, probably based on the parent.Id. So it IS ordering the selected records, but I cannot prevent is treating children.Id (i.e. the child table's Id) as text.

The word 'bug' rears its ugly head, but is anyone able to select records sorted (numerically) by the Id of the child table?

5 replies

null
    • UKenGB
    • 13 hrs ago
    • Reported - view

    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. ☹️

    • UKenGB
    • 12 hrs ago
    • Reported - view

    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.

    • UKenGB
    • 12 hrs ago
    • Reported - view

    Should of course have been "someWHAT bizarre…", but not possible to edit a post. 😡

      • UKenGB
      • 12 hrs ago
      • Reported - view

      That last post was in reply to and to correct the first post but which for some incomprehensible reason was placed at the end of the thread and no idea why I cannot edit any posts as I already did edit the first one, but which has now inexplicably stuck 'Copy' and a bunch of blank lines in the middle.

      So Ninox forums working as well as their 'order by' clause.

      Anyone reading this, make what sense of it you can. I give up.

    • Fred
    • 10 hrs ago
    • Reported - view

    It is confusing. Id would seem to be just a number but it actually contains the table code and the record number. If you do:

    debugValueInfo((select parent).children.Id)

    You will see an array of letters and numbers.

    That is why you need to wrap the Id with numbers if you just want numbers.

Login to reply

Content aside

  • 10 hrs agoLast active
  • 5Replies
  • 9Views
  • 2 Following