An Airtable autonumber gives each deal record a unique number, but it is not designed to count records and leaves gaps after deletes. It is the short ID that lands in the forecast export, the CRM note, the deal review deck, and the message that asks a rep what changed this week. When it works, nobody thinks about it. The problems show up in the last two weeks before a quarter closes, when the forecast file has to match the table exactly and someone asks why a number is missing or why the count of IDs does not agree with the count of deals.
Key takeaways
- Use the autonumber as the key for each deal row, and check that each ID appears once in the forecast file.
- Count open deals from a filtered view, not from the highest ID.
- Log every deletion with its ID, date, and reason.
- Leave gaps alone during close, and plan any renumbering for after the quarter is reported.
This post covers three facts about Airtable autonumber fields that matter for deal IDs before close. Each one comes from Airtable's help documentation on number-based fields, and each one leads to a plain rule for the forecast file. We will follow one example through all three sections. The example is a renewal deal for one customer. It is one record in the deals table, it has an autonumber ID, and it is one of the rows going into this quarter's forecast file. A renewal is a useful example because it touches every problem: its ID has to stay stable, it sits in a table where other records get deleted, and it is close enough to the cleanup temptation that someone might try to renumber the whole table.
Let's start with the first fact.
First fact: an autonumber is a unique ID for each record
Airtable describes the field in plain terms. In its words, "Autonumber fields automatically generate unique and incremented numbers for each record." (Airtable's number-based fields article) The same article says autonumbers "can be helpful when you need a unique identifier for each record or when using a formula in the primary field." (Airtable's number-based fields article)
Both halves of that sentence matter for forecasting. The first half says each record gets its own number, and the number goes up as records are added. The second half names the use: a unique identifier for each record. A deal ID exists to answer one question without any doubt: which record is this row? It is not supposed to describe the deal. A good ID does not carry the deal name, the stage, the amount, or the close month, because all of those change during a quarter and the ID should not change with them.
The formula point deserves a note too. A common pattern is to build a readable label for the primary field from several pieces of data, such as the ID plus the deal name. The autonumber is what keeps that label unique when two deals share a name. The renewal deal might be one of several deals for the same customer, or the same name might be reused by a new rep the following year. The ID settles which record is which, so the label can stay friendly.
For the renewal deal, the ID is whatever number Airtable assigned when the record was created. Suppose the rep moves the deal from negotiation to commit early in the week. The ID stays the same. Later that week, the forecast row for the renewal deal can be matched to the same record, and it does not appear as a new deal because someone edited a stage field. That stability is the reason to use the ID as the key at all.
Use the ID as the key in three places:
- The forecast file. Keep one row for each deal, keyed by the autonumber, with the deal name in a separate column so people can read the file without memorizing numbers.
- The CRM link. Store the Airtable ID next to the CRM opportunity ID so a row can be traced from either system. If the two systems disagree, the ID tells you which Airtable record to open first.
- The weekly review. When a deal comes up on the call, say the ID first and the name second. Two deals with the same name are easy to confuse out loud and impossible to confuse by ID.
When a rep asks why a deal moved, answer from the record, not from memory or an email paraphrase. The record is the source, and the forecast row is a copy of it.
Be careful about what the ID order tells you. Because the number goes up as records are added, sorting by ID shows the order in which records were created. It does not show when a deal will close, how large it is, or how far along it is. A reviewer who sorts the file by ID and reads the top rows as the newest or most important deals is reading a creation sequence as if it were a priority list. Sort by close month or stage for the call, and use the ID only as the way to find the record. For more on forecast categories, see forecast categories.
That leads to a rule you can check before every forecast call: each ID should appear exactly once in the forecast file. Sort the ID column and look for repeats. A repeat usually means a row was pasted in by hand, or an export was appended to last week's file without clearing the old rows first. Hand-typed IDs are an easy way to create duplicates, so copy IDs from the export and do not type them. A duplicate row inflates the pipeline total, and it is hard to spot in a file with hundreds of lines.
Uniqueness also has a limit. An ID identifies one record, not one customer. If the renewal and a separate expansion deal both belong to the same account, they get two IDs and should appear as two rows. Roll them up by account in a summary or pivot, and keep the ID as the grain of the forecast file. Mixing the two levels in one file is a common way to double count, because a customer-level total and a deal-level total can both look reasonable on their own.
Second fact: autonumbers do not count records, and deletes leave gaps
The second fact is one of the more common sources of forecast errors, and it comes from two sentences on the same Airtable page. The first says: "Autonumber fields are not designed to count the number of records in a table." (Airtable's number-based fields article) The second says: "When a record is deleted, the remaining records are not re-numbered—leaving gaps in the numbering." (Airtable's number-based fields article)
Put those together and three tempting conclusions fall apart. The highest ID in the table is not the number of deals. The lowest ID is not the first deal of the quarter. A gap in the sequence is not proof of a missing deal, although deleting a live deal by mistake can create one. Each conclusion feels reasonable when you look at a list of numbers, and each one is wrong for forecasting.
Here is how that plays out with the renewal deal. Imagine the table holds a duplicate record created by mistake during an import, and someone deletes it the week before close. Its number is now a gap. The renewal deal keeps its number, and so do the other deals, because the remaining records are not renumbered. Nothing in the live table is broken. But an analyst who rebuilds the deal count by taking the largest ID and assuming every number below it is a live deal will count the gap as a deal, and the pipeline will look one deal bigger than it is.
Gaps also pile up over time. Every deletion leaves one behind, so a table that has been in use for a few years will have many of them. That is normal behavior, not a sign of damage. A forecast process that expects gaps will be calm about them. A process that expects a continuous sequence will treat every gap as a problem to explain.
It helps to decide in advance what the forecast summary should show about gaps. A reasonable default is a single line under the totals: the number of open deal records in the view, the number of rows in the file, and the number of logged deletions this quarter. When the open-deal count and the file row count match, nothing needs explaining. When they do not, the difference points straight at the deletion log or at a duplicate row, and the person reviewing the file knows where to look. Leaving the gaps out of the summary entirely is a mistake, because it hides the very thing a reviewer may need to see.
So where does the count come from? Count records. Build a view that filters to open deals in the current quarter, and count the rows in that view. Put that count on the forecast summary next to the total amount, and label it as a count of records rather than as the highest ID. If the count in the view and the number of rows in the forecast file disagree, treat the difference as something to investigate before the call. Do not smooth it over by adjusting one number to match the other.
Gaps need a paper trail too. A gap tells you that a number is no longer in use. It does not show what the deleted deal was, who owned it, or why it was removed. Keep a short deletion log in a notes tab of the forecast file. Each line should have the ID, the date, the name of the deal as it was, and a reason in a few words. The log takes a minute to update. Without it, a gap becomes a question nobody can answer when someone raises it at the quarter review. For a record removed by mistake, see recover deleted sales records.
When that question does come up, the answer should be short and specific: the number belonged to a deal that was deleted on a given date, the deal was a duplicate, and the sign-off is on file. A short answer like that closes the topic. A shrug invites a much longer audit.
Because deletions near close carry the most risk, set one rule for the last two weeks of the quarter: a deletion needs sign-off from whoever owns the forecast. Duplicates are fine to remove, but the sign-off makes sure a live deal is not removed by accident along with them.
Third fact: renumbering from 1 can be done by delete-and-recreate or a type round trip
The third fact is about the temptation to clean up. Gaps look untidy, and sooner or later someone on the team will ask to make the numbers start over again. Airtable's article names the two ways to do that: "To re-number from 1, either delete and recreate the Auto Number field, or convert it to another field type (like Single line text or Number) and then convert it back to Auto Number." (Airtable's number-based fields article)
Read that quote as a warning about scope. Both methods act on the field, not on a single row. Deleting and recreating the field, or converting it to another type and back, changes the numbering across the table. It is a structural change to the base, not an edit to one deal. That is why the timing matters more than the method.
For the renewal deal, a renumber could change its ID. Any forecast row, CRM note, slide, or email from earlier in the quarter that quotes the old ID would then point to a different record, or to nothing at all. The clean-looking result would hide a break in the audit trail, because last month's file and the new numbering no longer match. Someone reading the old forecast in a board pack would have no reliable way to find the deal it describes.
A renumber also does not help the forecast. As long as you count records instead of reading IDs, the gaps change nothing in the totals. The renumber only changes labels, and it carries real risk. Doing it in the two weeks before close puts the forecast file in the path of the change for no gain in accuracy.
There is a trust cost as well. Reps and leaders learn deal IDs over a quarter, and some of them end up memorized. Changing those numbers in the middle of a close forces everyone to relearn them while they are trying to defend a commit. That cost is paid in meeting time, and it buys nothing the forecast needs.
Here is what to do instead:
- Leave the gaps in place and document each one in the deletion log.
- Plan any renumbering for a quiet period after the quarter's forecast is locked and reported, not during close.
- Export a snapshot first. Before any structural change to the autonumber field, export the full table with the current IDs and store the file with the forecast it supports. Name it with the date you took it.
- Test on a copy. Try the method on a copy of the base before you touch the live one. Airtable documents both routes, but how they affect your views, automations, and any connected tools is for you to check.
If the team does reset the numbers after close, record the change in the log with the old and new ID for each affected deal. That lets anyone reading last quarter's forecast follow the renewal deal from the old number to the new one. Without that map, the reset cuts the link between the forecast and the table.
Pre-close check for the example deal
Here is the check the forecast owner can run on the day the file is locked, using the renewal deal as the walk-through:
- Find the renewal deal in the forecast file by its ID, not by name. Confirm the ID matches the Airtable record it should match, and that the name in the file matches the record too.
- Confirm that the ID appears exactly once in the forecast file.
- Confirm the open-deal count comes from the filtered view, not from the highest ID in the table.
- Scan the deletion log for entries near the renewal deal's neighbors in the sequence, and make sure each one has a reason and a sign-off.
- Confirm that no change to the autonumber field is scheduled before the forecast is locked.
Suppose the deletion scan in step 4 turns up a deletion near the renewal deal with no reason logged. That is the case to take seriously. The deleted record may have been a live deal that someone removed by mistake, and the renewal deal's neighbors in the sequence are where that mistake would show up. Ask whoever made the deletion what the record held and why it was removed. If the answer is a duplicate, check that the surviving record carries the activity, the amount, and the close date of the one that was removed. If the answer is unclear, raise it with the forecast owner before the file is locked, not after the call.
Assign the check to one person, usually the forecast owner, and have them note the result at the top of the notes tab. If a step fails, do not fix it silently. Write down what you found, correct the file or the record, and log the change, so the next reviewer sees the same thing you saw.
The three facts reduce to one working habit. Treat the autonumber as a label that does not move, and treat the count as something you compute from records. Leave gaps alone during close, log every deletion, and save renumbering for after the numbers are reported. A forecast file built that way can survive a messy quarter.