# Re: Formula for specific digit length

Solved
Jump to Solution
2323 0
cancel
Showing results for
Search instead for
Did you mean:
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
16 - Uranus

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.

9 Replies 9
18 - Pluto

Welcome to the community, @Attila_Nagy! :grinning_face_with_big_eyes:

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)
5 - Automation Enthusiast

Dear Justin!

Thank you a lot!
Worked perfectly fine.

Best regards
Attila

18 - Pluto

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.

18 - Pluto

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.

5 - Automation Enthusiast

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

18 - Pluto

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}`
})
5 - Automation Enthusiast

Thanks a lot, I will try it later!

16 - Uranus

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.

5 - Automation Enthusiast

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