Jun 12, 2019 06:13 AM
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
Jun 12, 2019 10:21 AM
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}
)