Help

Reset Autonumber each year

Topic Labels: Automations Formulas
489 1
cancel
Showing results for 
Search instead for 
Did you mean: 
John_M
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello - I could use some help with a formula or automation to get what I'm trying to achieve to happen.

We have job numbers that are coded as follows:

23 = year

1 or 2 = office identifier

0001 = job number for the year

Example: 2310023

 

What I need to happen is the next sequential 4-digit job number to be added to the year and office identifier each time a form is submitted to create a new record, and it needs to reset each year so that on the first '24 job, it starts over at 0001. Does this make sense?

1 Reply 1
Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi John! Here's how you'd do it. 

1. Have a field that get the current year of the record as a formula using DATETIME_FORMAT
2. Dropdown select field for office identifier
3. Job number of the year, just use my script here: https://community.airtable.com/t5/show-tell/auto-number-that-resets-every-year/td-p/49934
4. Create a new formula field, and use the CONCATENATE function to merge these 3 values together. That's your job number!