Skip to main content

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?

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! 

Reply


Terms & Conditions