# Formula for specific digit length

Topic Labels: Formulas
Solved
2741 9
cancel
Showing results 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”

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

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!