Auto Number that Resets Every Year

[Devon_Godfrey]

(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:

Rolling Customer Number Based On Year Base design

I need to create a customer number that includes the year, and an accruing customer number. E.g. 2020-0001, 2020-0002, etc. The thing is, every year, the year has to change in the customer number, and the four numbers after it have to reset. So if the last number of 2020 is 2020-1234, the first number of 2021 will be 2021-0001 again. I can get the year from the Created Time field The problem is the numbers after the year… I figure I can’t use an autonumber because it won’t reset each year. …

Automatic Sequential Numbering of Non-Sequential Items (READ JUN 9 2021 FOLLOW-UP) Show and tell

Summary This setup allows Airtable to automatically number collections of items that may not be in sequential order. Item length can be either fixed or variable, with only minor changes needed to the core system to accommodate the latter. Example To borrow from the thread that inspired this endeavor, say you’re building product codes that have a variant sub-code, and want that sub-code to automatically increment each time an item of a given product code is added in a new record. However, it n…

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 )

[AshishMore]

@Devon_Godfrey  please help this is not working with latest Airtable version

it creates 001 all the time for all the records.

please help

[Devon_Godfrey]

Sorry for the late reply, I just tested it and it still works good. 

I've got it running in this base here: https://airtable.com/apps92mEiuq586J1d/shrBCb9CAL96uvnGv

Link to the form. Feel free to drop some records in here and see how the base responds in the previous link: https://airtable.com/apps92mEiuq586J1d/shrkuPkl67k8AYhdn

Attached are screenshots of the automation. 

The key things are to make sure:

- You edit the configuration variables at the top of the script to match EXACTLY the name of the fields ( configTable, configDateField, configYearAutonumberField )

- You add in input variables that pull in the field data from the created record. The names must match EXACTLY what I have in the screenshot. (createdRecord, recordDate)

As long as you get these correct, everything else should fall into place. 

Edit: Link to the script in pastebin: https://pastebin.com/HiMtb1Yv

Edit: Added an "instructions" table to the example base, that includes screenshots and how to set everything up

[SpelaU]

OMG, thanks @Devon_Godfrey ! This is excatly what I was looking for! 😉 

I solved my problem in 5 minutes but before your post I was banging my head just thinking how will I manage to do that for like a month 🙂 I thought that this will be very stressfull task so I didn't even google it until 2024 came along 🙂

Happy 2024!

[SpelaU]

Hi @Devon_Godfrey!

My script was working great until today. When I create new row, it starts with 1 again (ever if I have 19 rows in 2024). so this should be number 20 and not 1), and also for every new row, it always brings me back 1 .

Do you know why is this happening?

I check automation (your script) and some error was found. 

Error: Could not find a record with ID "rec78zGIBjPdUyQwv".

    Could not find a record with ID
at main on line 98

I created a new one but now I'm getting 1 for every new row.

Thanks for your help.

Spela

[Devon_Godfrey]

If you look back in my instructions for creating this:

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 )

---

---

A lot of it has to do with this exact issue.

So what's going on is that the script sorts the records by date, and uses that to determine the next number. Far from perfect, but if you deviate from that you get the odd behavior like you're seeing. 

The simple solution is to make sure the date field is a created time field (automatically generated), so that there's always a "progression" and you never end up with a later record being earlier than a previous record. If you need to document a specific date for each record, I'd recommend simply having a separate, editable date field for that. 

Having an editable date field is fine for testing purposes, but I wouldn't encourage it for a live environment. 

So to fix your issue:

1. Have 1 field that is the plain text, per the instructions

2. Have another field that is a created time, per the instructions and associate with the script

3. Have another, separate customizable date field that you can use for your purposes. 

[SpelaU]

Thank you for your reply @Devon_Godfrey !

I had date field as a created time field from the beginning. Actually it was a formula field: DATETIME_FORMAT(CREATED_TIME(), 'D. M. YYYY')

Now I created a new field as "created time" and it's working again.

Thank you for your help!

[Dave_Thorne]

I had this working perfectly but yesterday the autonumber stopped being created. There are some posts about AT problems with Automations at the moment, anyone else experiencing this?

[Devon_Godfrey]

The first place I'd look is the run history. If there are any errors there in regards to the script post them here. 

[Dave_Thorne]

Hi @Devon_Godfrey as if by magic or your presence in this thread the script is now working without any changes by me, perhaps AT had a wobble that has been fixed?

[atusr]

Hi there!

I want to share a handy method for automating periodic resets of auto-number fields without needing to dive into scripting. Here’s a quick guide:

### **Set Up Your Tables**
- **Main Table**: Make sure you have a "Created On" date field.
- **Time Periods Table**: Fill it with entries for each time period (year, month, or day) depending on how frequently you need the reset.

### **Create Key Formula Fields**
In your Main Table:
- **Year**: `YEAR(Created On)`
- **Month-Year**: `YEAR(Created On) & "-" & MONTH(Created On)` for monthly tracking.

Adjust these formulas based on whether you're resetting yearly, monthly, or daily.

### **Automate the Linking Process**
Set up an automation:
- **Trigger**: When a new record is created.
- **Actions**:
- **Find Records** in "Time Periods" that match your period formula.
- **Update Record** to link the new record to the identified time period.

### **Testing Your Setup**
Create some test records to make sure they automatically connect to the correct periods in the "Time Periods" table.

**Why This Rocks:**
- **Zero coding required**: Just set up and go!
- **Efficient and scalable**: Handles large datasets smoothly.
- **User-friendly**: Easy for anyone to implement.

Hope this helps streamline your auto-numbering needs! Let me know if you have questions or need further clarification. Happy to help!