Formula for specific digit length

[Attila_Nagy]

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

[Justin_Barrett]

Welcome to the community, @Attila_Nagy! :grinning_face_with_big_eyes:

Attila_Nagy:

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

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)

Attila_Nagy:

Besides that I want only the two second digits of the year.

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)

[Attila_Nagy]

Justin_Barrett:

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

Dear Justin!

Thank you a lot!
Worked perfectly fine.

Best regards
Attila

[Justin_Barrett]

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.

[Justin_Barrett]

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.

[Attila_Nagy]

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

[Justin_Barrett]

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}`
})

[Attila_Nagy]

Thanks a lot, I will try it later!

[Kamille_Parks · Accepted solution]

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.

[Attila_Nagy]

Kamille_Parks:

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

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