Formula for specific digit length

Ask the community Formulas
#1

Hello!

I want to have a Formula as the Secondary Field. Now the Formula is:
CONCATENATE(“PR-”,Autonumber,"/",YEAR(TODAY()))

The “Autonumber” is a Field as Autonumber.

How can I achieve, that the “Autonumber” in the CONCATENATE is always 4 digits long, independent of it’s length. Besides that I want only the two second digits of the year.

So the values of the Autonumber is “4”, I want: “PR-0004/21”

Thank you for your help in advance!

Best regards
Attila

Formula 1
Formula 11280×700 55.8 KB

Formula 2
Formula 21280×700 61.8 KB

#2

Welcome to the community, @Attila_Nagy! :smiley:

I used to use one method for adding leading zeros until I saw a nifty trick from @Kamille_Parks recently. Replace your {Autonumber} reference with this: RIGHT("000" & Autonumber, 4)

This can be achieved using a similar trick. First concatenate the year with an empty string to turn it into a string, then get the right two characters: RIGHT(YEAR(TODAY()) & "", 2). This also uses the more compact & concatenation operator instead of the CONCATENATE() function.

Applying all of this to your original formula gives you this:

"PR-" & RIGHT("000" & Autonumber, 4) & "/" & RIGHT(YEAR(TODAY()) & "", 2)
#3

Dear Justin!

Thank you a lot!
Worked perfectly fine.

Best regards
Attila

Formula 1

Formula 2

#4

You can drop the surrounding CONCATENATE() function. It’s redundant because the & operator (as I said above) does the exact same thing: it combines the pieces into a string. Just use the formula that I wrote above as-is.

#5

Something just occurred to me: when the year rolls over, all of those IDs are going to change to end in “22” instead of “21” because you’re using a formula. You should consider moving that formula into a different field, and use an automation to copy that generated ID into the primary field if the primary field is empty. That way this year’s IDs won’t change next year.

#6

OK, I understand your point.
Is there any way to avoid the second field?
A script or something…

#7

A script would work. Create an automation that triggers when a new record is made. The next step would be a “Run script” action (which requires that your base be in a Pro plan workspace). This script would need two input variables, both from the trigger step:

  • recordId - The record ID of the triggering record
  • autonumber - The autonumber field value of the triggering record

Here’s the script:

const {recordId, autonumber} = input.config()
const productNumber = ("000" + autonumber).slice(-4)
const year = ("" + (new Date()).getFullYear()).slice(-2)
const table = await base.getTable("TABLE_NAME")
await table.updateRecordAsync(recordId, {
    "Name": `PR-${productNumber}/${year}`
})
1 Like
#8

Thanks a lot, I will try it later!

#9

You could also just use the CREATED_TIME() function to avoid using Automations:

"PR-" & RIGHT("000" & Autonumber, 4) & "/" & RIGHT(YEAR(CREATED_TIME()) & "", 2)

^ that way the formula outputs the year the record was created and won’t change from year to year like TODAY() would.

2 Likes
#10

I just tested it and this one also works fine!
Thank you!

#11

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.