Formula field - add leading zeros to numeric value

[Mitch]

Hi all,

Trying to create a formula field  in a table to concatenate a numeric field ("ID" ) and text field ("Name".)  ID is Auto number field.  Need to append leading zeroes to convert ID field to a string that is ALWAYS 4 characters  in length then concatenate that to Name field.   For example, When ID = 13 and Name = MyName result should be "0013 - MyName".   Sorry if this is basic but new to this and not seeing an obvious solution.  Thanks. 

[SinJunYoung]

2 hours ago, Mitch said:

Hi all,

Trying to create a formula field  in a table to concatenate a numeric field ("ID" ) and text field ("Name".)  ID is Auto number field.  Need to append leading zeroes to convert ID field to a string that is ALWAYS 4 characters  in length then concatenate that to Name field.   For example, When ID = 13 and Name = MyName result should be "0013 - MyName".   Sorry if this is basic but new to this and not seeing an obvious solution.  Thanks. 

Hi @Mitch,

If you want to use the 00, then you will need to use prefix autonumber instead of the regular autonumber: 

 

Afterwards, create a formula field that will concatenate the records, but, since they are of different DataTypes, you will need conversion to text (preferred than conversion to number or integer).  Try this formula: CONVERT(VARCHAR,[@field:Concat_ID_2])+ '-'+ [@field:Name]

 

CONVERT(VARCHAR,[@field:Concat_ID_2])+ '-'+ [@field:Name] - The Bold text here represents the Prefix autonumber, 

CONVERT(VARCHAR,[@field:Concat_ID_2])+ '-'+ [@field:Name] - The Bold text here represents the field for the text. 

 

So they should look something like this: 

 

As soon as they're now okay, check if the records in the table work as expected:

 

That is the only workaround, for the Always 4 RULE, not sure if this is possible. I am assuming CASE WHEN statement?

[Barbie]

Hi @Mitch, 

Yes, @SinJunYoung is right, this can be achieved using CASE WHEN Statement. Considering that once the Autonumber is already more than four digits it will display as is. 

Here's a sample formula which you can use: 

CASE WHEN  [@field:Number_11] < 10 
THEN '000' + (CONVERT(VARCHAR, [@field:Number_11])) + '_' + [@field:Name]
WHEN [@field:Number_11] < 100 
AND 
[@field:Number_11] >= 10 
THEN '00'+ (CONVERT(VARCHAR, [@field:Number_11])) + '_' + [@field:Name]
WHEN [@field:Number_11] >= 100 AND [@field:Number_11] <= 999
THEN '0'+ (CONVERT(VARCHAR, [@field:Number_11])) + '_' + [@field:Name]
Else CONVERT(VARCHAR, [@field:Number_11]) + '_' + [@field:Name]
END

Here's a sample result assuming that the Number_11 field is the Autonumber field :
 

I hope this works on you end.

Regards,
- Barbie

[Mitch]

Thanks to both of you.  Learned a  lot here.  Great to be a new member with such helpful fellow members!