Adding character and number to Record ID

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
I'm trying to add character and number to Record ID. For example, [Record ID#] = 326. My goal is to create a new formula field to add 'MF' and '00' before it. The final result should look like this MF00326

The formula I came up with: 
"MF" & "00" & [Record ID#] 

This formula works fine. However, I want to make sure the 0 adding correctly. Is there a formula can solve the following statement? 
1) If the length of [Record ID#] is equal 3, add two '00' in front of the number
2) if the length of [Record ID#] is equal 4, add one '0' in front of the number
3) if the length of [Record ID#] is equal 5, DO NOT add any '0'  in front of the number. 

*I tried the 'Length' function but it doesn't work because [Record ID#] is a Numeric field. 
Photo of Katherine Phung

Katherine Phung

  • 248 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Katherine Phung

Katherine Phung

  • 248 Points 100 badge 2x thumb
Solve it by the following code: 

var number Len = Length(ToText([Record ID#]));

Case($Len, 
    2, "MF"&"000"&[Record ID#],
    3, "MF"&"00"&[Record ID#],
    4, "MF"&"0"&[Record ID#],
    5, "MF"&[Record ID#]
)