Automatic file numbering - Year-File#

Topic Labels: Formulas
2055 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


I’m trying to create an automatic file numbering scheme for my project list and file list. I’ve read some of the other posts on this but can’t get my specific need. I’d like the filing scheme to be:
year (last 2 digits) + 3 digit sequential number: so 20-001, 20-002, etc.

I figured out how to pull out the string value for the year from current date (i.e. “20”) and I’ve figured out how to sequentially number entries. But I can’t get the two together. For example, when 2021 starts, I want the numbering to reset to 001. Is there anyway to do that?

Many thanks,

1 Reply 1
10 - Mercury
10 - Mercury

This is the type of problem that bumps up against the limitations of Formulas. A couple things formulas aren’t currently designed for (1) caching information (2) accessing data from other rows.

(1) In your case, caching is trouble for the Year value since functions like TODAY() don’t cache and will continue to update with time. Manually set Date or Single Select fields are the norm instead.

(2) A formula runs once per row and doesn’t have access to other rows’ data (unless Linked Records are being used). So awareness of some repeating, row based pattern is difficult.

Solutions to fill the void are the same in both cases:

  • a formula that concatenates a few fields together automatically, then have users employ clever copy/pasting and spreadsheet work on import
  • vanilla Airtable tools like Automations, the Button Field, and the Scripting Block (JavaScript programming) which can auto-fill values for you
  • integrations and the API for heavier weight, third party solutions, which can also employ auto-filling beahvior