Help

Re: Auto Number that Resets Every Year

3905 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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:
2021-10-30 23_51_23-Auto Number Base_ Projects - Airtable — Mozilla Firefox
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”:
2021-10-30 23_53_26-Auto Number Base_ Projects - Airtable — Mozilla Firefox
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 )

15 Replies 15
Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

Next, create an automation for when a record is created:
2021-10-30 23_54_45-Auto Number Base_ Projects - Airtable — Mozilla Firefox
2021-10-30 23_55_10-Auto Number Base_ Projects - Airtable — Mozilla Firefox

Add a script action:
2021-10-30 23_56_04-Auto Number Base_ Projects - Airtable — Mozilla Firefox

Paste in the script in the following pastebin: //If you have any questions about this, reach me at devon[-at-]playfrey.tech// - Pastebin.com

image

Next, we need to configure this step a bit:

  • Change “configTable” to be EXACTLY what the name of your table is (In this case, “Projects”)
    2021-10-31 00_02_48-Auto Number Base_ Projects - Airtable — Mozilla Firefox
Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

Do the same with the other two:
2021-10-31 00_03_49-Auto Number Base_ Projects - Airtable — Mozilla Firefox

Next, we need to add the following input from the record that was created:
2021-10-31 00_05_04-Auto Number Base_ Projects - Airtable — Mozilla Firefox

It’s vital that it’s the “Trim” of the record ID
2021-10-31 00_06_01-Auto Number Base_ Projects - Airtable — Mozilla Firefox

For Date, just the date field is fine:
2021-10-31 00_07_05-Auto Number Base_ Projects - Airtable — Mozilla Firefox

Next, click finish in the top right and turn on the automation:
image

Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

Finally, go ahead and create a record:
2021-10-31 00_10_48-Auto Number Base_ Projects - Airtable — Mozilla Firefox
You’ll notice that the Number field auto increments, however if I convert the date field to a regular date field, then set up a form view and submit a project for next year ( the only way you should use this if you’re not using a created time field 😞

2021-10-31 00_12_55-Auto Number Base_ Projects - Airtable — Mozilla Firefox
You’ll see that the counter reset back to one.

You can even backdate records and they’ll keep count in that specific year. [EDIT: Don't backdate records, things get weird, and I don't know if I'll ever get around to fixing it]

Hopefully this was helpful. I don’t check here often, but if you want some help with this script don’t be afraid to shoot me using the email in my pastebin.

Thanks for sharing! It is very helpful!

Welcome to the community, @Devon_Godfrey! :grinning_face_with_big_eyes: Thanks for chiming in on this topic! I’m in the middle of creating my own script to append to the thread of mine that you linked above, but I’ve been swamped by other stuff and haven’t been able to wrap it up yet. I’ll hold off on checking out your solution until I finish mine. However, I will edit the category for this thread, as this belongs in the “Show and Tell” group instead of “Ask the community”.

Appreciate you Justin!

I’ve been getting emails asking about resetting daily and monthly, I just updated the pastebin so there’s now a option in the config variables to reset it daily and monthly.

Thanks for this Devon. Wondering how I can edit this to change based on Fiscal year, rather than calendar year?

Brian_Podnos
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you for this! I keep getting an error that says:

“Error: Field “fldlEQVsveTqEliYH” cannot accept the provided value.
at main on line 92”

Any ideas here?

@Brian_Podnos We’ll need more details about how you’re attempting to implement the script, including the types of fields in your table.

AshishMore
4 - Data Explorer
4 - Data Explorer

@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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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.

SpelaU_0-1705140967882.png

 

Thanks for your help.

Spela

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. 

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!