How do you use Airtable? Inventory system? CRM? Invoicing system? In any case, with these system, you may want to create your own record id in the way you want, example with combination of years and prefix.
Let’s assume that for this number: CUST-2023-00001, if we break it up, it will be:
- CUST- the prefix
- 2023 – Current year
- 00001 – 5 digit running number with leading zero.
Here is the actions in Airtable:
1. On the data sheet it self, create a autonumber column, let’s call it CustAutoNumber.
2. Next, create a formula type column, we called it CustomerID, which is the real generated customer id. Enter the below formula:
"CUST-" & DATETIME_FORMAT(NOW(), "YYYY") & "-" & REPT("0", 5 - LEN(CustAutoNumber & "")) & CustAutoNumber
3. To break it up, it mean
- DATETIME_FORMAT: We getting the year of the current date
- REPT: Repeating add characters based on the length
- 5 – LEN({CustAutoNumber} & “”): The maximum length of the running number is 5, I am using this this to deduct of the existing length of the running number, to determine how many leading zero do we need
4. Yup, you are done.
