Reset Number Sequence After the End of a Month

Hello,

May I ask how to reset a number sequence after the end of a month? So I create this Coda doc to keep track invoice number.

This is the number sequence:

Then I concatenate it with the Invoice No. Date.

But I need it to reset every 1st day of the month so I can always know how many invoices I send each month. May somebody help me? Thanks!

There are different ways of doing this, but you can try my sample below. The lower part of the page shows what the default table it would look like after hiding rows and titles - it is the same 1 line table. Your thisRow,this table in the function will not work (see other threads about row numbers).

Thanks! I guess this is really helpful. I’ll make this my plan B. But currently I’m still seeking a way to make this automated without having to click any button (because I make this for other people who isn’t really familiar with Coda).

Here is my current doc (See the Invoice No. column in the Invoice Masterdata table:

You can have an automation reset the invoice number once a month. You have to set the automation everyday at 00:01 AM (maybe 00:00AM works too - you have to test). Then add a 2nd condition to make sure the current date is the first day of the month)

The other button is needed to make a new invoice - something has to trigger making a new invoice. Of course you can hide the column with the invoice number, less confusion there. Adding the month in the invoice number can be done with another helper column, with the value today() to extract the month number. Make sure you use two digits for the month (01, 02, …,11,12) to keep the invoices correctly ordered - it looks like you are now using one digit for the month,

I actually use your idea and added if(today-1=endofmonth(today,-1), 1, Invoice No. + 1) on the increment button to reset the sequence at the end of every month. It’s almost perfect. But I still have to find a way to reset if a new invoice is added on date after 1st automatically.

And yes I hide all the columns so it’s neat and simple.

Thank you so much for your help!

@Aldis_Tannos,

But I still have to find a way to reset if a new invoice is added on date after 1st automatically.

For that reason I recommended to use an automation - because you can make it trigger the button just once at the very beginning of the month. Instead of triggering the button you can also use the automation to set the value back to 1.
Automations can be used to set values (change row) or to push a button. I am not sure of the button should be taken out of the table but that is easily found out.

@Aldis_Tannos

Below is another possible approach. See the blue columns in Quotation Masterdata.

3 Likes

This is so simple yet so elegant. Thank you so much!

1 Like