Alphanumerical sequential numbering

Ask for Help Formulas
1

Hi. I need to create a unique ID number for each entry in the table. I thought about something like:

@@##

where @ represent letter (A, B, C, …) and # digit: AB43, GF91 and so on.

Why? Because with that method I could create 2626100 unique records. With just digits I needed a 6-digit number for that and such type of numbering is easier to remember and simpler reference in communication. Everyone love when boss call to you and talk “check purchase number 012783” and you not wrote that number quick enough.

So I have 2 questions:

  1. how to generate such sequential number, in a way, that after AA99 I get AB00 and after AZ99 BZ00?
  2. how to ensure that ID become truly unique and stay persistent, even after removing some records and adding new ones? So after generating ID for the record it doesn’t change, even after removing some records with lower ID.

Thank you.

2

You could try out something like this: an “apply on create” trigger formula, on a “custom_id” column, with the appropriate python formula, something like:


n = $id - 1
return chr(65 + n // 2600) + chr(65 + (n // 100) % 26) + f"{n % 100:02d}"
3

While mine has 8 digits (all numeric), I like that it isn’t just random so that I can glean some information from it. I use a date combined with the last 2 digits of the record ID. It gives me a sequential unique number for each record.
rec.TravelTo_Start.strftime('%y%m%d') + str(rec.id) [-2:]

image
image110×134 988 Bytes