Add Sequential Numbers to Grouped Rows in Airtable

Suppose you’re creating job reports in Airtable that are submitted via forms. These job reports belong to different projects.

You’ve grouped these reports by project name and are currently using Airtable’s auto-numbering for report numbers.

Because auto-numbering applies globally, the numbers don’t reset within each project. For example, if there are 14 reports across all projects, the third report in Project A will be numbered “15” instead of “3.”

What you need is for each project’s reports to have their own sequential numbering (e.g., Project A: Report 1, 2, 3; Project B: Report 1, 2, 3…).

Since Airtable’s auto-numbering doesn’t support this, how can you achieve it? Here’s how:

1. Set Up Your Tables

• Projects Table: This table holds all your project details (like project name, status, etc.).

• Reports Table: This table collects job reports submitted via forms, and each report is linked to a project.

2. Create a Unique Identifier for Each Report

In your Reports Table:

• Add a new formula field called ReportID.

• Use the formula RECORD_ID(), which generates a unique, fixed-length ID for each report.

3. Roll Up Report IDs into the Projects Table

Now, switch to your Projects Table:

• Add a Rollup field named ReportID Rollup.

• Configure it to pull the ReportID from the linked Reports:

  • Set the rollup source to the Reports Table.

  • Choose the ReportID field from that table.

  • Use the aggregation formula ARRAYJOIN(values, ";"). This creates a semicolon-separated list of ReportIDs for each project.

• Optional: Sort the linked records by Created Time (Oldest First) so that the numbering follows the order in which reports were submitted.

4. Bring the Report ID List Back into the Reports Table

Back in the Reports Table:

• Add another Rollup field, this time called Project ReportIDs.

• Configure it to pull data from the linked Projects:

  • Set the rollup source to the Projects Table.

  • Choose the ReportID Rollup field.

  • Again, use ARRAYJOIN(values, ";"). Now, each report will have a string that lists all ReportIDs for its project.

5. Calculate the Sequential Report Number

Finally, in the Reports Table:

• Add a formula field named Report Number.

• Use this formula (FIND(ReportID, {Project ReportIDs}) + 17) / 18

What’s happening here?

The FIND() function locates the starting position of a report's unique ID within the concatenated string of all IDs for its project.

Since each Report ID is 17 characters long, new IDs start at positions 1, 19, 37, and so on.

By adding 17 to the found position and then dividing by 18, we convert these positions into sequential numbers: 1, 2, 3, etc. Thus, each report is assigned a consecutive number within its project.

That’s it!

Now, whenever a new report is added, it will automatically get the next sequential number within its project. Even if you rearrange records, the numbering will stay consistent.