Grouped by 1 field

[Princess_Curren]

Hi,
I have migrated my data from Google sheet and each record has a unique code (i.e. TO-1234). I want to autogenerate a new number in increments of 1 for each record that is added which shold not a duplicate of any previous code listed in the field.
;
in regex the formula is:

=MAX(
ARRAYFORMULA(
value(
if(REGEXREPLACE({N3:N9290},“TO-”,"")=“XXXX”,
“”, REGEXREPLACE({N3:N9290},“TO-”,""))
)
)
)+1

I can’t figure out how to replicate this in Airtable. Help!
Thanks

[Jeremy_Oglesby]

• Step 1 - Sort your records such that the lowest number you have in your list of records (your earliest record) is first and the rest follow in suit
• Step 1 - Create a new “Autonumber” field
• Step 3 - Create a new Formula field that adds a value to your Autonumber field that makes the Autonumber of your first record (1) equal to the original record code on the first record by adding however much is needed to the Autonumber to make the value equal, and then concatenating it with leading “0’s”

Let’s say your first record has the code TO-0005 - this would be your formula:

"TO-" &
RIGHT("000" & ({Autonumber Field} + 4), 4)

If the output of that formula matches the existing code on each already existing record all the way down the line, then you can simply replace the existing unique code field with this formula. If not (ie, you have gaps in your original sequential numbering, and you need to retain your original record numbers), you can create a third formula field that uses your original code if it exists, but uses the newly generated one if not. Then all old records will use their original code, but all newly created records will continue to created their own sequential codes.

IF(
   {Original Code Field},
   {Original Code Field},
   {Formula}
)