Formula for ID generator.

[wallison]

Hello! How are you?

I would like some help with a solution I'm having difficulty finding answers for.

I have a table and I need to generate a unique code (ID), but not based on randomness, rather on a relationship of information within the table itself.

The ID in question should have a format similar to this: YYMMDD + BR + Numerator

Where the numerator should differentiate between identical records, for example:

Suppose on the date 23-08-08 there were 3 records. The code should then be: 230808BR01, 230808BR02, 230808BR03.

Does anyone know how to solve an issue like this?

[Ron_Williams]

I think the easiest way would be to run an automation when a new row is added (or a checkbox if you want to trigger it yourself), it runs "find record". I would suggest adding a "created time" field and use a condition based on "created "is" today". It will return the number of matching records in the "length" property that were created today. Then run this script with the input variable "length of records" from the search in the previous step:

// Get the input data from the triggering record

let inputRecord = input.config();

let numberField = inputRecord.YourNumberFieldName; // Replace "YourNumberFieldName" with the name of your field.

// Increment the number

let incrementedNumber = (numberField + 1).toString().padStart(2, '0'); // Ensure at least 2 digits with padding

// Get the current date and format it as YYMMDD

let currentDate = new Date();

let formattedDate = currentDate.getFullYear().toString().slice(-2) + // Get last two digits of the year

(currentDate.getMonth() + 1).toString().padStart(2, '0') + // Month

currentDate.getDate().toString().padStart(2, '0'); // Date

// Generate the final output string

let finalOutput = formattedDate + "BR" + incrementedNumber;

// Output the final string

output.set("finalOutput", finalOutput);

Then update the record field you want with the output. This should accomplish what you need. 

[Sho]

Hello @wallison ,

There are two ways that unique IDs are guaranteed in Airtable: the Autonumber field and the RecordID.
The Autonumber field is a unique value, but is frowned upon by the meticulous, since deletions, etc. will result in missing numbers.

You may also find this guide helpful.
Sequential Numbering of Records · Kuovonne's Guide to Airtable (coda.io)