Number formats
The value supplied for the Display Mask field of the UOM_Values form or the Number field form is used to determine the way that numbers with the unit of measure are formatted when displayed. The formatting in a Number field takes precedence over UOM formatting when displaying Number fields in query reports or forms. If a value has more digits to the right of the decimal place than shown in the Display Mask property, the platform uses Round Half Up to round the value in the display.
The value supplied for the Display Mask field is treated as a sequence of characters with each character having a particular meaning. Different characters have different meanings in a format. The following explains how formats work one character at a time.
The first character is 0 (zero). A zero is used to indicate a position that contains a digit. If a number has a format that consists of a sequence of zeros, it will be formatted to have at least that many digits. This is shown in the following table.
| Value | Format | Formatted Value |
|---|---|---|
| 0 | 000 | 000 |
| 3 | 000 | 003 |
| 45 | 000 | 045 |
| 678 | 000 | 678 |
| 12345 | 000 | 12345 |
Another character that has a special meaning in a number format is # (known as hash mark, number sign or octothorp). A # is used to indicate a position in a format that can contain a digit or a space. If a position that corresponds to a # would contain a leading zero, then it contains a space; otherwise it contains a digit. The following table shows some examples of numbers formatted with combinations of # and 0.
| Value | Format | Formatted Value |
|---|---|---|
| 0 | ##0 | 0 |
| 0 | #00 | 00 |
| 0 | ### | |
| 3 | ##0 | 3 |
| 45 | ##0 | 45 |
| 678 | ##0 | 678 |
| 12345 | ##0 | 12345 |
A number format may contain one period to indicate a decimal point in formatted numbers. The following table shows some example formats with a period.
| Value | Format | Formatted Value |
|---|---|---|
| 0 | ##0.000 | 0.000 |
| 123.453 | ##0 | 123 |
| 123.453 | ##0. | 123. |
| 123.453 | ##0.0 | 123.5 |
| 123.453 | ##0.00 | 123.45 |
| 123.453 | ##0.000 | 123.453 |
| 123.453 | ##0.0000 | 123.4530 |
If a format does not allow enough digits after the decimal point to express a number exactly, the number is rounded to fit within the format. If there are more zeros after the decimal point in the format than are needed to represent a number exactly, the number is formatted with trailing zeros.
Use # after the decimal point to avoid trailing zeros. There are examples of this in the following table.
| Value | Format | Formatted Value |
|---|---|---|
| 0 | ##0.0## | 0.0 |
| 123 | ##0.0## | 123.0 |
| 123.4 | ##0.0## | 123.4 |
| 123.45 | ##0.0## | 123.45 |
| 123.453 | ##0.0## | 123.453 |
| 123.4538 | ##0.0## | 123.454 |
A comma (,) can be used to specify that the digits in a formatted number should be divided into groups. The following table has examples of this.
Notice that if a number is longer than the format, the separation of digits into groups continues with groups the same size. The size of the groups can be three, four, or any other size that is desired. The last example in the table shows digits formatted into groups of four.
| Value | Format | Formatted Value |
|---|---|---|
| 0 | #,###,### | 0 |
| 123 | #,###,### | 123 |
| 1234 | #,###,### | 1,234 |
| 123456 | #,###,### | 123,456 |
| 1234567 | #,###,### | 1,234,567 |
| 1234567890 | #,###,### | 1,234,567,890 |
| 1234567890 | ####,#### | 12,3456,7890 |
Use an E to indicate numbers should be formatted using scientific notation. The following table shows some examples of this.
| Value | Format | Formatted Value |
|---|---|---|
| 1234 | 0.###E0 | 1.234E3 |
| 0.00123 | 00.###E0 | 12.3E-4 |
Do not use a comma (,) and an E in the same number format.
Put a % (percent sign) or ? (per-mill sign) at the beginning or end of number pattern. These characters are displayed as themselves. A % has the affect of multiplying the formatted version of the number by 100. A ? has the affect of multiplying the formatted version of the number by 1,000. Examples of these are shown in the following table.
| Value | Format | Formatted Value |
|---|---|---|
| 0.8432 | 0.#### | 0.8432 |
| 0.8432 | ##0.0#% | 84.32% |
| 0.8432 | %##0.0# | %84.32 |
| 0.8432 | ##0.0#? | 843.2 ? |
| 0.8432 | %##0.0# | ?843.2 |
Do not use both a % (percent sign) and a ? (per-mill sign) in the same number format.
Use a ¤ (currency sign) at the beginning or end of a number format to indicate that a currency symbol should be in the formatted number. This is always $ (dollar sign). The following table shows some examples of this.
| Value | Format | Formatted Value |
|---|---|---|
| 1234.5 | ¤#,##0.00 | $1,234.50 |
| 1234.5 | #,##0.00¤ | 1,234.50$ |
Characters that have no special meaning in a number format can be at the beginning or end of a number format. Such characters are just copied into formatted numbers. This is shown in the following table.
| Value | Format | Formatted Value |
|---|---|---|
| 1234.5 | **###0.00** | **1234.50** |
Number formats can specify how negative numbers are to be formatted. If a number format does not specify how negative numbers will be formatted, then a negative number is formatted the same as a positive number with a minus sign before it.
You can explicitly specify a how negative numbers are to be formatted by following the number format with a ; (semicolon) followed by another number format that specifies formatting for negative numbers. This is shown in the following table.
| Value | Format | Formatted Value |
|---|---|---|
| 1234.5 | #,##0.00 | 1,234.50 |
| -1234.5 | #,##0.00 | -1,234.50 |
| 1234.5 | #,##0.00;(#,##0.00) | 1,234.50 |
| -1234.5 | #,##0.00;(#,##0.00) | (1,234.50) |
Use a ' (single quotation mark) to indicate that the character following it should not be treated with any special meaning, for example, for literal symbols. The following table shows some examples of this.
| Value | Format | Formatted Value |
|---|---|---|
| 12 | '#0 | #12 |
| 12 | #0 o''clock | 12 o'clock |