Forum Discussion

KatherinePhung's avatar
KatherinePhung
Qrew Trainee
7 years ago

Adding character and number to Record ID

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. 
App builders
Formulas and functions
  • KatherinePhung's avatar
    KatherinePhung
    Qrew Trainee
    7 years ago
    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#]
    )