0

How to sort alphanumerically

Hello there,

 

I have only only recently started using Ninox and I'm finding the user manual a little difficult to understand.

 

I have created a database to manage a gardening associations membership.

I have 2 tables, 'members' and 'plots'. The Plots are numbered alphanumerically and some are split, some are not, so we have the following plot numbers; 

 

1

2a

2b

3a

3b

4

5 and so on to 47b with around 90 full or half plots.

 

when I apply the sort ascending I can see the plots sorted as 

 

1

10a

10b

11

12

 

instead  like my first example above.

I couldn't add the plot numbers as a number field because of the lettering so it is a text field but there is no alphanumeric way of sorting these logically.

 

please could you advise how I achieve this?

I am using the free version as we are not a business, only a community group.

 

TIA

Su

5 replies

null
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    Hi,

    You can insert an additional column with the following formula:

    ---

    let myD := number(extractx('plot number', "\d+"));
    let myT := extractx('plot number', "\D+");
    cnt((select 'your table')[number(extractx('plot number', "\d+")) < myD]) + if cnt((select 'your table')[number(extractx('plot number', "\d+")) = myD]) > 1 then
    cnt((select 'your table')[number(extractx('plot number', "\d+")) = myD and extractx('plot number', "\D+") <= myT])
    else
    0
    end + 1

    ---

    'your table' and 'plot number' should be replaced by your names.

    If you sort by this column, it should work fine

    Leo

    • chair
    • 6 yrs ago
    • Reported - view

    Thanks Leonard,

     

    that has sort of worked, but not quite and it's probably that I don't follow what the code was doing so I can't troubleshoot it.

     

    heres what I can see: (I hope the image turns out right - it looks squashed in my window)

     

    And this is the revised code you kindly provided:  I have made the names bold in this example to show what I changed.

    let myD := number(extractx('Plot No', "\d+"));
    let myT := extractx('Plot No', "\D+");
    cnt((select 'Plots')[number(extractx('Plot No', "\d+")) < myD]) + if cnt((select 'Plots')[number(extractx('Plot No', "\d+")) = myD]) > 1 then
    cnt((select 'Plots')[number(extractx('Plot No', "\d+")) = myD and extractx('Plot No', "\D+") <= myT])
    else
    0
    end + 1

     

    so, if I sort on the formulae column the records are all jumbled up 

    • chair
    • 6 yrs ago
    • Reported - view

    Actually, no, I think it has worked but I'm missing some records - I may have been too judicious when I deleted some records earlier in a tidy up.

     

    thank you so much for your help 

    best wishes,

    Su

    • Leonid_Semik
    • 6 yrs ago
    • Reported - view

    Hi Su,

    you should sort by the column Formula.

    ---

    let myD := number(extractx('Plot No', "\d+"));
    let myT := extractx('Plot No', "\D+");
    cnt((select Plots)[number(extractx('Plot No', "\d+")) < myD]) + if cnt((select Plots)[number(extractx('Plot No', "\d+")) = myD]) > 1 then
    cnt((select Plots)[number(extractx('Plot No', "\d+")) = myD and extractx('Plot No', "\D+") <= myT])
    else
    0
    end + 1

    ---

    Leo

    • jel888
    • 2 yrs ago
    • Reported - view

    Hello,

     

    I have exactly the same issue using the same numbers in front of text to order my questionnaire (questions field).  When I put either formula in the f(x) field for the Question field (which is the one I want to sort by) red alerts show for certains lines and the "d+".  How do I fix the formula to sort correctly?

Login to reply

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 2466Views
  • 1 Following