I am not very good at calculations.  I want to create an invoice number from the date i.e. (210603) and a sequential 2 digit number.   I have been using this forever but I would really like to have it automated.  An example would be 21060321.  Then I will need to add a if then script to start that as of today's date and leave all old invoice numbers alone.  Anyone done this before.  I know it is probably pretty simple.  But not for me.  

You may need to use a sequence table that has no records - if your entire process is scripted then when you wish to create a new invoice you

go to that table if it has no records us the script step to Set Next Serial Value to 001 then create a record which has a field that auto generates serial numbers grab that number into a variable go back to invoices prepend the YYMMDDnnn to it and set your field.

if records exist and the last record date <> today's date then truncate the records and use the same step.

12 hours ago, calancathy said:

I know it is probably pretty simple. 

I am afraid it is not simple at all. As you can see from the answer above, it needs to be scripted - and it needs to be scripted very carefully to prevent duplicates, esp. in a situation where two users create an invoice roughly at the same time. 

A better solution, IMHO, would be to change your numbering scheme.

Note that if (as would seem from your question) you are absolutely sure that you will NEVER create more than 99 invoices in a single day, you could use a simple solution: a calculation that appends the last two digits of an auto-entered serial number to the date. But then those digits would not start from "00" (or "01") on each day. For example, the sequence might look something like:

...
21060295
21060296
21060297
21060398
21060399
21060300
21060301
21060402
21060403
...

But even here you could get a duplicate if you backdate an invoice.

---
P.S. Please fill out your profile to reflect your version, OS and skill level. Upon rereading your question, I am not sure you understand the difference between a script and a calculation.

 

Edited June 4, 20214 yr by comment

Thanks everyone.  I guess I will continue to enter it in manually.  I think a table might work to keep track of where I am at in the number sequence though.  

Thanks again.