0

Autonumber a filtered list

Hello!
I got an table named Citas.

I need to select a date range from it, sort the resulting list by date, and then number it starting from some number.

I used to use the solution provided here on the forum, but now it jumps over numbers in the process, in other words, it numbers the list like 2,3,5,6,11,12,....

The actual solution is this:
First, because i dont know how to select the date range in such a formula, i flag the needed records with a flag.

Then, i created a 'Counters' table, with a field named 'Counter' there.

 
Then i made a button in 'Citas' table with a code:

for i in (select Citas where Flag > 0) order by 'Date field' do
record(Citas,i).('Number' := text(record(Counters,1).Result));
let c := record(Counters,1).Counter + 1;
record(Counters,1).(Counter := c)
end

So, pressing that button it numbers the list. But it does it bad.

Can you offer a better solution or tell me where is the problem that provokes that jumping mistake?

Thank you very much!
Agassi

10 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    Second line: text(record(Counters,1).Result)

    Doesn't it work with this?:

    i.(Number := text(record(Counters,1).Counter));

    Steven

    • Agassi
    • 3 yrs ago
    • Reported - view

    Ill try tomorrow'. Thank you!

    • Agassi
    • 3 yrs ago
    • Reported - view

    Hello!

     

    I tried it, but it still jumps over consequent numbers:

    191, 192, 195, 195, ...

    What can be a problem?

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    I suppose that the purpose of the "Counter" field in the first record of the "Counters" table is to keep the last value between executions of the script. There is no need to update it at each step, so I would use a variable to keep the intermediate values of the counter:

    let c := record(Counters,1).Counter;

    for i in (select Citas where Flag > 0) order by 'Date field' do
    i.('Number' := text(c));

    c := c + 1;
    end;

    record(Counters,1).(Counter := c)

    A stupid question: the script numbers the records in "Date field" order. When you look at the results, are the records sorted in the same way?

    • Agassi
    • 3 yrs ago
    • Reported - view

    Steven, i searched through the forum and found the tread where i got my solution. Its actually YOURS  )).

    Here's the link if someone needs the original thread: https://ninox.com/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/create-tables-with-autonumbering-with-ease.-5d5ee82ab6ba1f2dab0664b4

    Thanks a lot!

    It worked before, but now it jumps over numbers. Even as you have suggested here. And by the way usng the "Counter" field instead of "Result" field i loose the formatting like 000, so when i sort the table by the "Number" field it does it like 1, 10, 100, 101, 102... 

    • Agassi
    • 3 yrs ago
    • Reported - view

    Alain, you're right - the reason of the "Counter" field is to keep the last value between executions of the script. And also to keep the last value until the next time i need to number new records and start from that vaue.

     

    Your solution with the variable works well - it numbers the rows correctly and does it little faster then the first one.
    So thank You very much for your help!!!

     

    Regarding your NOT stupid question - yes it sorts the same, so the absent (or jumped over) numbers are really absent. It just sorts like 1, 10, 100, 101, 102... 

     

    I got some questions, will write them in the next post.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    "Number" being a text field, the way it sorts is normal. I just happened to explain it in detail in another thread this morning. Either make it a number field, or format it with leading zeroes to get a correct sort.

    • Agassi
    • 3 yrs ago
    • Reported - view

    So, my questions are these:

    1) can you format a variable to have nubers like 001, 002? I tried
    format(c, "000") := c + 1;
    but it gives an error.

    2) can you autonumber using "Update multiple records..." feature?
    Its much faster to update with that then with select function, and also, i normally have to filter that table by various fields to work with it, not only by date, so when all is filtered, its left only a little to number that resulting list with that feature.

    • Agus
    • 3 yrs ago
    • Reported - view

    out of topic but Agassi, is your profile picture Gordon Freeman?

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    For your field named "Number", there are two options:

    1- define it as a true number field. Ninox will store the numbers using its internal way to code numbers (this sentence would be fit for the physician exam in Molière´s "Malade imaginaire"). You then just put numeric values in it, they will sort correctly.

    2- define it as a text field. In order to get the "correct" sort order, you will need to format the numeric values with leading zeroes before putting them in the field. Don't forget to put enough zeroes to cope with the largest number you will ever want to store in the field. For this option, the correct syntax in the script would be:

    i.('Number' := format(c,"0000"));

    For your second question: I have not yet found a way to do it. Maybe one of the resident experts has a trick to propose...

Login to reply

Content aside

  • 3 yrs agoLast active
  • 10Replies
  • 1104Views