‹Blog

Airtable: Auto‑number That Resets Every Year (and Month/Day)

FE
Filla Editorial•intermediate•Oct 29, 2025

Yearly (or monthly/daily) counters that reset

Goal: generate IDs in the format YYYY-#### that reset when the year changes—e.g., 2025-0001, 2025-0002, then 2026-0001 next year.

This guide summarizes a community walkthrough that uses a single table, a date field, and an automation with a short script. It also notes no‑script alternatives and caveats like backdating.

Table fields

  • {Date}: Created time (recommended) or a date field
  • {Number}: Single line text (the formatted counter like 2025-0001)

Using Created time avoids surprises from manual edits. A normal date field works if you’re careful not to backdate.

Automation (scripted)

Trigger: When a record is created.

Action: Run script.

Inputs to pass into the script:

  • recordId: the created record’s ID (trimmed)
  • dateValue: the record’s {Date} field value

Script overview (what it does):

  1. Derives a period key from dateValue (e.g., the year 2025; configurable to month or day)
  2. Finds prior records that share that period key
  3. Computes the next sequence (count + 1) within that period
  4. Formats as YYYY-#### (e.g., 2025-0001) and writes it to {Number}

Daily/monthly options: The reference implementation exposes a configuration toggle to reset by day or month instead of year.

Notes:

  • Be precise: configuration variables in the script must exactly match your table and field names
  • If you backdate records, behavior can be inconsistent; prefer Created time

No‑script alternative (linking by period)

If scripts are disabled, you can approximate resets by linking records to a Time Periods table:

  1. In Main, add formulas for the period key:
    • Year: YEAR({Created On})
    • Month: YEAR({Created On}) & "-" & MONTH({Created On})
  2. Create Time Periods with one record per period (e.g., 2024, 2025, …)
  3. Automation:
    • Trigger: when a record is created
    • Actions:
      • Find Records in Time Periods where name = the period formula
      • Update Record to link the new record to the found period
  4. In Main, roll up a per‑period count and format your {Number} from that count

This keeps counters scoped by the linked period without scripting.

Formatting helpers

Year prefix from a date:

YEAR({Date})

Zero‑pad a number to 4 digits:

RIGHT("0000" & {Seq}, 4)

Combine:

YEAR({Date}) & "-" & RIGHT("0000" & {Seq}, 4)

Where {Seq} is the per‑period sequence from the script or from a rollup/count.

Caveats

  • Backdating: Can create gaps/duplicates depending on your logic; prefer Created time
  • Concurrency: High‑throughput inserts may require an “Upsert” pattern in the script
  • Field types: {Number} must accept text (formatted IDs), not numeric

Source

Community walkthrough with script and configuration for yearly/monthly/daily resets: Auto Number that Resets Every Year

Airtable: Auto‑number That Resets Every Year (and Month/Day)