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?

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • UweG
    • UweG
    • 3 mths 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
     

    Like 1
    • Fred
    • Fred
    • 3 mths ago
    • Reported - view

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

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

      Like 1
      • Fred
      • Fred
      • 3 mths ago
      • Reported - view

      join the club. 😀

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

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

      Like
Like Follow
  • 3 mths agoLast active
  • 6Replies
  • 62Views
  • 4 Following