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

---

FAQ

Can I reset an auto-number by month or quarter instead of year?

Yes. The same scripted automation approach works for any period. Change the period key derivation in the script to use YEAR & "-" & MONTH for monthly resets, or build a quarter key with YEAR & "-Q" & CEILING(MONTH/3). The no-script linking method also adapts: just create period records for each month or quarter instead of each year.

Does the counter stay accurate if I delete records?

Deleting records can create gaps in the sequence because the script counts existing records to determine the next number. If you need a gapless sequence, avoid deleting records. Instead, mark unwanted records with a status field and filter them out of views.

Will this work with Airtable's free plan?

The scripted version requires automations with scripting, which is available on paid Airtable plans. The no-script alternative using a linked Time Periods table works on any plan, though you still need automations to link records automatically.

How do I handle multiple users creating records at the same time?

Concurrent inserts can cause duplicate numbers because two scripts may read the same count before either writes. For high-throughput scenarios, add a short delay or use an upsert pattern in the script to check for collisions and retry with the next available number.

---

Build Better Airtable Forms with Filla

If you are collecting data through Airtable forms that feeds into numbered records, Filla gives you more control over that intake process.

• Conditional logic to show or hide fields based on responses
• Multi-step forms that guide submitters through complex entries
• Custom validation rules so records arrive clean and complete
• Prefilled fields to reduce manual entry errors

Start free and connect your first form to any Airtable base.

Create Your First Form Free

---

Source

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