Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 05, 2023 10:06 AM
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.
Jan 06, 2023 05:56 AM
Would you mind sharing the formula you are currently using? I am looking to do something similar.
Jan 06, 2023 06:15 AM
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?
Jan 08, 2023 11:48 PM
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