Discussions

Expand all | Collapse all

Adding character and number to Record ID

  • 1.  Adding character and number to Record ID

    Posted 04-20-2018 16:24
    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. 


  • 2.  RE: Adding character and number to Record ID

    Posted 04-20-2018 16:58
    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#]
    )