how to add leading zeros to the beginning of a field

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I want to add leading zero to the record ID to create a unique number

Photo of Shontay

Shontay

  • 490 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
No problem.

Make a formula text field such as

Right("0000" & ToText([Record ID#]),5)

That would give a result of 00123 for Record ID# 123.
Photo of Shontay

Shontay

  • 490 Points 250 badge 2x thumb
Thanks, how do I make sure that the number never exceeds 6
Photo of Shontay

Shontay

  • 490 Points 250 badge 2x thumb
([RELATED_PLANNING_CODE] & Right("00000" ToText([Record ID#]),6)), this the formula I am trying to get to work
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Sorry, I'm not understanding the question.  If the Record ID# hits 1,000,000, like if you enter your 1 millionth order, what result do you want?

This formula will show the code, then the rightmost  6 characters of a zero padded field, so at order # 1,000,000 you will lose the 1.
Photo of Shontay

Shontay

  • 490 Points 250 badge 2x thumb
ok I understand so how do I combine the code with another field

([RELATED_PLANNING_CODE] & Right("00000" ToText([Record ID#]),6))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Just do this
[RELATED_PLANNING_CODE] & Right("00000" ToText([Record ID#]),6)

You don't need those extra brackets at the beginning and end. What is not working with the formula you posted above?
Photo of Shontay

Shontay

  • 490 Points 250 badge 2x thumb
No I am getting a syntax error Possible missing Parentheses, quotes or Brackets, but I did use this formula and it seem to work, will this give me the same result  ([RELATED_PLANNING_CODE]) & Right("00000" & ToText([Record ID#]),6)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
You should not need the extra brackets but they will work, just extra clutter.  This should work   [RELATED_PLANNING_CODE] & Right("00000" & ToText([Record ID#]),6)