0

Running number issue

Hello everyone.

I've setup running numbers for our stock, invoices etc.
That does work as intended.

However, if any previous record is deleted, the next number created will be a duplicate.

Example of current numbers
001
002
003
004
If I delete the record "002" and then create a new record, the next running number will be "004" again. How can I avoid this and force it to continue from the last running number created?

The code I use as below.

let prefix := "PV";
let y := year('Issue Date');
let m := month('Issue Date');
if 'PV-Number' then
    void
else
    let c := cnt(select Accounts where year('Issue Date') = y) + 1;
    'PV-Number' := prefix + y + "-" + m + "-" + format(c, "0000")
end

Thanks for any help.

3 replies

null
    • Digital Sonar
    • Alexandros_Finomenos
    • 2 yrs ago
    • Reported - view

    replace count with max 
    count counts for records so if you delete one, is normal to be count = 3 + 1 = 4 .
    max will take the maximum number which is 4 + 1 = 5.
    Just make sure that you use max on a numeric value and not a text.

    • NetSol Co., Ltd.
    • Bernd_Krell
    • 2 yrs ago
    • Reported - view

    Thanks a lot for the explanation Alexandros Finomenos
    Now I understand better. Since I use a prefix of letters, I am not using a numeric field. I'll need to think how to do that. Thanks again.

    • Digital Sonar
    • Alexandros_Finomenos
    • 2 yrs ago
    • Reported - view

    Happy to have helped. You can use another numeric field to keep the numeric value of the order or you can use some functions with regex to extract the number from the text field. If you google regex extract number you ll find thousands of answers.

    Then you can use this:
     

     

    extractx(string, string, string, string)

    extractx(myText, regex, flags, extract) To extract a string from a given text using a regular expression. Flags can be added to the regular expression. The string can be further processed with a given extract.

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 48Views
  • 2 Following