Help

Formula for ID generator.

Topic Labels: Formulas
1453 2
cancel
Showing results for 
Search instead for 
Did you mean: 
wallison
4 - Data Explorer
4 - Data Explorer

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?

2 Replies 2
Ron_Williams
6 - Interface Innovator
6 - Interface Innovator

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
11 - Venus
11 - Venus

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)