How to do Custom Record Number - YY for year plus Autonumber that resets every year?

[Markus_Wernig]

Is there a way to automate this system of numbering new records:

Q-19001 - “19” is tells me it’s from 2010 and then it’s followed by a 3 digit autonumber.
Q-20001 - that’s the first record for 2020 and the 3 digit sequence resets itself.

I have two questions -

1. Which formula command “extracts” the 2 digit year (YY) from a “date created” field for example? I can’t seem to figure that one out. I am sure it’s easy…

2. How do I set the autonumber field as having to have 3 digits - and is there a way to have the auto-numbers reset every year? (Someone had suggested to manually adjust the formula at the beginning of each year by subtracting the highest number of last year - that’s certainly one way of doing it.)

Thank you so much in advance.

[Jeremy_Oglesby]

Markus_Wernig:

Which formula command “extracts” the 2 digit year (YY) from a “date created” field for example? I can’t seem to figure that one out. I am sure it’s easy…

This one is indeed easy -

DATETIME_FORMAT({<<your date created field name>>}, 'YY')

Markus_Wernig:

Is there a way to have the auto-numbers reset every year? (Someone had suggested to manually adjust the formula at the beginning of each year by subtracting the highest number of last year - that’s certainly one way of doing it.)

This one, not so much…
I could conceive of an extremely convoluted system of linking all these records to a summary table, and performing conditional rollups, to lookups, back to new rollups, and processed with a formula to try and extract that last Record in a year… but even then, you have the step of connecting each of these records to the summary table, which you still have to figure out how to automate.

I think ideally, your best bet to handle this might be the Scripting block (which will still require a button click, but that’s it), or a Custom Block (which could potentially handle this in a fully automated fashion).

[Markus_Wernig]

Thank you, Jeremy.

And thank you for explaining the first part of my question. I am starting to get how Airtable works now.
But I also had a feeling that the second part would be more than complex - and therefore not worth doing.

Even if it was possible to somehow get the reset of the autonumber part done - what if one had to go back into the 2019 records and add an entry there. The entire autonumber-based-on-record-creation-year would fall apart at that point…

Maybe some things are still best done by hand… :winking_face: