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 )

[Devon_Godfrey]

Can you create a test base with this and share it? 

[Gabriel_Galindo]

Hi Atusr,

My organization has disabled scripts, so your solution would be a great help to me.  Can you go into a little more detail on your process?  If I'm reading your post correctly, I would want a 2nd table with a column that contains incremental numbers: 2024, 2025, 2026, etc.

Then what do I enter for Find Records?
- **Find Records** in "Time Periods" that match your period formula.
- **Update Record** to link the new record to the identified time period.
As a novice AirTable user this isn't clear to me.

Look forward to any help you might provide.

[Devon_Godfrey]

If you only need incremental numbers, then the auto-increment field should work nicely by itself. This solution is for if you need that counter to reset on a regular basis. 

You can read about auto-numbers here: https://support.airtable.com/docs/number-based-fields-in-airtable

If you need it to start at a different number, set up a formula field and add the auto increment number to it (For instance, {auto-number-field} + 2000)

[Gabriel_Galindo]

Thanks for your reply Devon.

I'm actually looking for exactly what was described in the original post in this thread.  To have tickets numbered 2024_001 and on, then in January switch to 2025_001.  For now I'm using the autonumber field until I'm granted access to scripting or Atusr gives me some additional details for his suggestion.