Autonumber without "gaps"

Hello everyone,

I recently saw a topic in this section about the “Autonumber” field.
Some of the problems mentioned are not relevant in my opinion.

Here are the my requirements :

  • Auto-increment a field
  • Always take the last generated number + 1
  • Never change a generated number

It does NOT :

  • Recalculate numbers when a record is deleted
  • Recalculate any number when record is moved

I use this to generate invoice/quotation numbers.

I made a very basic script to handle this, feel free to copy and use it on your Airtable base.

/*********************************/
/*      A better Autonumber      */
/*********************************/
/*  Author  : Florian VERDONCK   */
/*  Website : airtablefacile.fr  */
/*  Version : 1.0                */
/*********************************/

let config = input.config();

// Script configuration
const TABLE_NAME = config.TABLE_NAME;
const INCREMENTED_FIELD_NAME = config.INCREMENTED_FIELD_NAME;

// Query all records, sorted by number descending (biggest numbers first)
let table = base.getTable(TABLE_NAME);
let tableQuery = await table.selectRecordsAsync({
    "sorts" : [
        {
            field: INCREMENTED_FIELD_NAME,
            direction: "desc"
        }
    ]
});

// Define the default value to zero (in case the next condition is false)
let latestNumber = 0;

// Get the first one (so the biggest number)
if (tableQuery.records) {
    latestNumber = tableQuery.records[0].getCellValue(INCREMENTED_FIELD_NAME);
}

// Updates the created records and define it's number to (latestNumber + 1)
let numberToAssign = latestNumber+1;
await table.updateRecordAsync(
    config.RECORD_ID,
    {
        [INCREMENTED_FIELD_NAME]: numberToAssign
    }
)

output.set("assignedNumber", numberToAssign)

Hope this might be useful to some of you :slight_smile:

Florian

3 Likes

Nice! :slight_smile: You may want to submit this to the example script showcase here: