Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Autonumber that Repeats Every Year

Topic Labels: Formulas
2366 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