0

Dynamic Choice "order by Id" gives incorrect order

I'm trying to use a Dynamic Choice field for the first time.  My "Dynamic Values" formula selects values from another table and uses the "order by" statement to sort the values by the table's standard "Id" field:

(select 'Status_List')['Status' = "Active"] order by Id

Instead of the values being ordered by "1, 2, 3, 4 ... 9, 10, 11, 12", I'm getting "1, 10, 11, 12, 3, 4...".  It seems to be sorting based on the 1st digit, rather in proper numeric order.

Anyone experienced this before?

6 replies

null
    • UweG
    • 2 yrs ago
    • Reported - view

    Is Id a specially created text field?
    Then it is also sorted like a text field.
    order by number(Id) should work
     

    • Fred
    • 2 yrs ago
    • Reported - view

    You don't need to order by Id. Ninox does that by default.

      • Peter_Amies.1
      • 2 yrs ago
      • Reported - view

      Fred Thanks!  No idea why I over complicate these things for myself. 😄

      • Fred
      • 2 yrs ago
      • Reported - view

      join the club. 😀

    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    All systems will order text fields in this way, even Excel. The data is seen as alphabetic and sorted accordingly.  To get a true numeric order you need to convert to a number field.

      • Peter_Amies.1
      • 2 yrs ago
      • Reported - view

      Paul Chappell Thanks Paul.  I never would have guessed that a field that is only ever populated by numbers would be stored as alphanumeric.  Anyway, as both you and UweG suggest, adding a number('Id') got things working.  Even easier though, as per Fred was just to remove the "order by" statement altogether.

Content aside

  • 2 yrs agoLast active
  • 6Replies
  • 152Views
  • 4 Following