Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Formula for specific digit length

Topic Labels: Formulas
Solved
Jump to Solution
531 9
cancel
Showing results for 
Search instead for 
Did you mean: 

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 2

1 Solution

Accepted Solutions

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.

See Solution in Thread

9 Replies 9

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)

Dear Justin!

Thank you a lot!
Worked perfectly fine.

Best regards
Attila

Formula 1

Formula 2

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.

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.

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

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

Thanks a lot, I will try it later!

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.

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