Oct 30, 2021 09:19 PM
(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:
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 )
Oct 30, 2021 09:19 PM
Next, create an automation for when a record is created:
Add a script action:
Paste in the script in the following pastebin: //If you have any questions about this, reach me at devon[-at-]playfrey.tech// - Pastebin.com
Next, we need to configure this step a bit:
Oct 30, 2021 09:20 PM
Do the same with the other two:
Next, we need to add the following input from the record that was created:
It’s vital that it’s the “Trim” of the record ID
For Date, just the date field is fine:
Next, click finish in the top right and turn on the automation:
Oct 30, 2021 09:21 PM
Finally, go ahead and create a record:
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 😞
You’ll see that the counter reset back to one.
You can even backdate records and they’ll keep count in that specific year.
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.
Nov 01, 2021 05:45 AM
Thanks for sharing! It is very helpful!
Nov 01, 2021 12:02 PM
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”.
Nov 05, 2021 09:31 AM
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.
Dec 21, 2021 12:05 PM
Thanks for this Devon. Wondering how I can edit this to change based on Fiscal year, rather than calendar year?
Jan 06, 2022 07:18 AM
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?
Jan 07, 2022 07:54 AM
@Brian_Podnos We’ll need more details about how you’re attempting to implement the script, including the types of fields in your table.
Mar 02, 2023 01:55 AM
@Devon_Godfrey please help this is not working with latest Airtable version
it creates 001 all the time for all the records.
please help
Aug 17, 2023 08:47 AM - edited Oct 20, 2023 11:00 AM
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