Skip to main content

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.



This one is indeed easy -


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


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).

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:

Reply


Terms & Conditions