‹Blog

Airtable: Create a Unique ID with a Formula in the Primary Field

FE
Filla Editorial•beginner•Nov 10, 2025

Why you need a formula-driven ID

When you inherit a template like the Sales CRM, the primary field often contains a user-facing name (e.g., “Company”) that is not unique. But for integrations or downstream systems you might need a deterministic key—something you can pipe to accounts, services, or invoices. Airtable hides stable IDs behind the scenes, yet you can expose them with formulas without reworking every link.

Primary field rules you must respect

  • A primary field value can never be blank.
  • It must be unique—no duplicates allowed.
  • You can set the primary field to a formula or autonumber, but not to a lookup/rollup.
  • If you convert the primary field to a formula, it becomes read-only; edits must happen in referenced fields.

Understanding these constraints helps you choose the right pattern for your base.

Option 1 — Reveal Airtable’s hidden RECORD_ID()

RECORD_ID() returns Airtable’s immutable, system-generated identifier. It is guaranteed unique, doesn’t change when you rename a record, and works in any plan.

Steps

  1. Insert a new field (temporary) next to the primary field.
  2. Set the field type to Formula.
  3. Use the formula:
RECORD_ID()
  1. Once you see the values, you can either:
    • Keep this formula field and leave the primary field as a human-friendly name, or
    • Convert the primary field itself to a Formula and paste RECORD_ID() there.

Because RECORD_ID() is immutable, it is perfect for linking tasks, services, or transactions in other systems. It is also the cleanest way to export Airtable data and maintain relationships.

Option 2 — Autonumber when order matters

If you want a sequential identifier (e.g., CUST-001, CUST-002), convert or insert an Autonumber field. This generates 1, 2, 3… in creation order.

  • Pros: Easy to read, simple to turn into a formatted code with a companion formula ("CUST-" & LPAD({Auto}, 3, "0")).
  • Cons: Autonumbers are not reusable (deleted rows leave gaps) and will shift if you copy the base.
  • Use this only when you specifically need chronological numbering; otherwise stick with RECORD_ID().

Option 3 — Meaningful composite IDs

Sometimes you want an ID that encodes business info, such as combining email and service tier. Build a formula that concatenates normalized pieces:

LOWER(Email) & "-" &
SUBSTITUTE({Service Plan}, " ", "") & "-" &
DATETIME_FORMAT({Signup Date}, "YYYYMMDD")

Tips:

  • Lowercase text and strip spaces to avoid mismatches.
  • Add delimiters (like -) between segments so AB + C ≠ A + BC.
  • Keep composite IDs under 500 characters to stay within Airtable’s formula limits.

This approach is ideal for customer-facing codes, but remember that if any component changes (e.g., the user updates their email) the ID also changes. Pair it with a RECORD_ID() field if you need both stability and readability.

Use IDs across linked tables and exports

William Porter’s advice still applies: Airtable already stores foreign keys; you only need to expose them when leaving the platform.[^community] Suppose you have Projects (one) and Tasks (many):

  1. In Projects, create {Project Record ID} = RECORD_ID().
  2. In Tasks, create {Task Record ID} = RECORD_ID().
  3. Add a lookup field {Project Record ID (Lookup)} pointing from Tasks to Projects → Project Record ID.

Now every task exposes both its own ID and its parent’s ID—exactly what you need for CSV exports or use in other databases. The visible linked-record selector remains friendly (“Acme Industries”) while the exported file carries the machine-friendly keys.

Map single selects to numbers (for scoring or billing)

The original question also asked how to convert options like “Basic”, “Pro”, “Enterprise” into numeric values. Use SWITCH() for a tidy mapping:

SWITCH(
  {Service Plan},
  "Basic", 1,
  "Pro", 2,
  "Enterprise", 3,
  0
)
  • SWITCH stops at the first match and returns the numeric value you supply.
  • The final 0 acts as a fallback when a record has no plan selected.
  • Combine the result with math (Price * {Plan Multiplier}) or additional formulas.

If you need percentages, return decimals (1.0, 1.5, etc.) instead of integers.

Automation and integration tips

  • Zapier/Make: Send both the human-readable name and the hidden RECORD_ID() so you can match records reliably in other apps.
  • Primary-field formulas in forms: If you use the primary field as a formula, it will be auto-generated after form submission—no extra work for submitters.
  • Backups: Before refactoring a live base, duplicate it. Converting the primary field to a formula is reversible, but having a copy protects existing views and automations.

Quick reference

Goal Recommended approach
Need an immutable ID Formula field with RECORD_ID()
Need a sequential customer code Autonumber + formula wrapper (e.g., "CUST-" & LPAD(...))
Need human-readable context Composite key formula (email + plan + date)
Exporting to another database Expose both record IDs and lookup parent IDs
Score single-select options SWITCH({Select}, "Basic", 1, "Pro", 2, "Enterprise", 3)

References

Airtable: Create a Unique ID with a Formula in the Primary Field