• Learn
  • Blog
  • Pricing
  • About
  • Sign in

Feature: Pad numbers with leading zeroes

You can now pad numbers with leading zeroes by setting a minimum number of integer digits in the inspector. With a minimum number of four digits, 23 is formatted as 0023. As a bonus, we show how to accomplish the same thing using formulas and share some news regarding the venerable IF function.

Calcapp has long had comprehensive support for formatting numbers. Use thousands separators (200 000, not 200000), format numbers using scientific notation (1.00E+04, not 10000) and format the fractional part of a number as an actual fraction (3 3/4, not 3.75). You also get full control over the number of decimal places that are used (12.36 can be formatted as 12.4 or as 12.3600, depending on your preferences).

What hasn’t been very straight-forward in the past has been formatting numbers with leading zeroes. Imagine if you always want a number to appear formatted with four digits at a minimum. That means that 23 should appear as 0023, 123 as 0123 and 12345 as 12345. Now, you can specify the number of integer digits a number should be formatted with, right in the inspector:

The new support for leading zeroes in Calcapp Creator

Bonus tip: Adding leading zeroes the old-fashioned way

App creators have happily been creating fields with leading zeroes even without this feature, thanks to the flexibility afforded by Calcapp’s formulas. Here’s a now-redundant formula for formatting the value of Field1 with leading zeroes so that it always consists of at least four digits:

IF(ISDEFINED(Field1), REPT("0", MAX(0, 4 - LEN(Field1.FormattedValue))) & Field1.FormattedValue)

This formula first checks to see if the value of Field1 is defined and not blank. Then, it calculates the length of the formatted value of Field1 using the LEN function and subtracts the length from four, thereby calculating the number of leading zeroes.

In other words, if the value is 23, two leading zeroes are used. If, however, the value is 12345, no leading zeroes should be used. The MAX function is used to ensure that the number of leading zeroes is never negative.

Finally, a single zero is repeated the correct number of times using the REPT function, invoked with the text string “0” (producing “00” if Field1 is 23). That text string is then joined together with the actual value using the & operator, finally producing a text string such as “0023”.

Note: The eagle-eyed reader may have noticed that we only pass two parameters to the IF function in the formula above. Traditionally, the IF function takes three parameters: a condition which evaluates to TRUE or FALSE, a value to use if the condition evaluates to TRUE and a value to use if it evaluates to FALSE. It turns out that popular spreadsheets also accept only two parameters as well, allowing users to leave out the third parameter, which created work for some of our app creators. As a result, we have made the third parameter optional in Calcapp. IF now returns a blank value if the condition evaluates to FALSE and no third parameter is supplied. IF(2 > 3, 42) is thus now equivalent to IF(2 > 3, 42, BLANK()).

« Feature: Force a 24-hour clock for time fields Feature: Determine the device type from formulas »