Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Autonumber that Repeats Every Year

Topic Labels: Formulas
2009 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Karim_Badra
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi guys, 

I am using a concancente function along with autonumber to get something like this X-2022-1  as a unique reference field for a record.

However, I am wondering how we can do something similar (sequential numbering) that loops back to 1 at certain intervals such as year. 

3 Replies 3
AnitaHallberg
5 - Automation Enthusiast
5 - Automation Enthusiast

Would you mind sharing the formula you are currently using? I am looking to do something similar. 

JonathanB
8 - Airtable Astronomer
8 - Airtable Astronomer

This is possible, but it involves having a new table for each year.  It will still create all the records in the same table, however, using an automation.

Set up as follows:

You need a table for your records, and a table for each year (or period of time after which you want to reset your numbers.  In this "Records" table you need a formula for "Current year", e.g. DATETIME_FORMAT(TODAY(), "YYYY")

In each of your year tables, set the primary field as an autonumber field.  Then in the 2023 table, have a formula like "2023" & "-" & {Autonumber}  Do the same in the 2024 table, with a formula "2024" & "-" {Autonumber} and so on.  In each of the year tables, also have a single line text field.  This is just so the automation has something to fill in, it's not doing anything else.

Now your automation works like this:

Trigger: When a record is created in the "Records" table, take the following conditional action:

If {Current year} is 2023 > Create a record in the "2023" table where {Single line text field} contains {Airtable Record ID}
then, Update record in the "Records" table so that the {Name} field contains the content of the formula from the 2023 table.  This will be your reference number.

You then add on a second conditional action so that If {Current year} is 2024...and repeat.  

It might take a video (or a lot of screenshots!) to demo properly but hopefully this makes sense?

 

If this is a yearly thing, I'd just make a new autonumber field and call it a day honestly.  Add some logic in the formula field you're using to concat everything together and you're done.  You could future proof it by making 5 new autonumber fields heh