Auto Number that Resets Every Year
(So, since I’m a “New” user even though I’ve used airtable for years, I can only post 5 images per post, so I’m breaking this up into multiple responses. )
Hi guys! I encountered a need to have this solved, and the nature of the problem just doesn’t mesh well with creating a formula to fix it.
Here’s a topic that was looking for a good answer on this, and one that gives an answer that I think can be improved upon:
This does require an automation, but only requires one table and two fields: a created time field and a single line text field. It doesn’t matter whether they’re primary or not.
Here’s how it works:
First, create a new base, just to play with it and get a handle on it:
Here, I’ve created a single base and renamed the first table “Projects”, as I will use this to create a project numbering system that resets each year.
Next, I’m going to add two fields: “Date” and “Number”:
They MUST be a date and single line text field, respectively! ( I would highly suggest that we make the date field a created time field )
Next, create an automation for when a record is created:
Add a script action:
Paste in the script in the following pastebin: //If you have any questions about this, reach me at devon[-at-]playfrey.tech// - Pastebin.com
Next, we need to configure this step a bit:
- Change “configTable” to be EXACTLY what the name of your table is (In this case, “Projects”)
Do the same with the other two:
Next, we need to add the following input from the record that was created:
It’s vital that it’s the “Trim” of the record ID
For Date, just the date field is fine:
Next, click finish in the top right and turn on the automation:
Finally, go ahead and create a record:
You’ll notice that the Number field auto increments, however if I convert the date field to a regular date field, then set up a form view and submit a project for next year ( the only way you should use this if you’re not using a created time field 😞
You’ll see that the counter reset back to one.
You can even backdate records and they’ll keep count in that specific year. [EDIT: Don't backdate records, things get weird, and I don't know if I'll ever get around to fixing it]
Hopefully this was helpful. I don’t check here often, but if you want some help with this script don’t be afraid to shoot me using the email in my pastebin.
