There are several Quickbase settings used to support the needs of our customers around the world. Number formats are among these settings. You can set a number format and separator to control how any Numeric field in your app will display.
For those of you who are account or app administrators, it isn't necessary to set a number format for every Numeric field. If you have one number format that is used across all Numeric fields in an app, you can set a default value for the app. Likewise, if you only use one number format across all apps in your account, you can set a default value for the account. These defaults affect apps and fields when they are created, so changing them won't affect existing items. These defaults can be overridden on a field-by-field basis.
Supported number formats
Quickbase supports the following six number formats, which are combinations of digit grouping (2 or 3), thousands separator (, or .), and decimal separator (, or .):
-
12345678.99
-
12,345,678.99
-
1,23,45,678.99
-
12345678,99
-
12.345.678,99
-
1.23.45.678,99
Setting a number format
To set a number format for the account:
There is an account-level property for the number format, which is set from the Manage Billing Account page. To set this property:
-
On the left side of the Summary tab, click Edit Account Properties.
-
In the Formatting Defaults section, use the Numbers field to choose a number format.
-
Click Apply Changes.
New apps created from scratch use the account-level property as a default.
To set a number format for an app:
There is an app-level property for the number format that overrides the account-level property. This property is set on the App Properties page:
-
From the app Home page, click
.
-
Under Basics, click App properties.
-
Expand the Formatting Defaults section, if necessary.
-
Use the Numbers field to enter the number format.
-
Click Save on the Page bar.
New apps created by copying another app use the app-level property of the copied app.
New Numeric fields in an app use the app-level property.
Custom columns in reports use the app-level property.
To set a number format for a field:
Every Numeric field has a field-level property for the number format that overrides the app-level property. This property is set on the Field Properties page:
-
From the table Home page, click
-
Under Table Structure, click Fields.
-
Click a Numeric field to display the Field Properties page.
-
Expand the Display section, if necessary.
-
Use the Display Format field to enter the number format.
-
If you chose a number format with number separators, use the Separators field to choose whether the separator will be shown after 3 places or after 4 places.
Note: Spaces are not allowed as separators.
-
Click Save on the Page bar.
New Numeric fields created by copying a field use the field-level property of the copied field.
Working with Numeric fields and number formats
Searching
Searches on data in Numeric fields ignore the thousands separator.
Quick Search and Advanced Search allow the user to search for both formatted and unformatted numbers.
Importing and exporting data
Data exported from Numeric fields will include the correct number format.
Numeric data imported into existing fields in a table use the field-level property for the number format. If number format information is included in the imported data, it should match the field-level properties of the fields it's being imported into.
Working with formulas
The ToText(Number n) formula function returns the number with a decimal separator but no other formatting.
There is now a formula function called ToFormattedText(Number n, Text format) that can return a formatted text version of the number specified. The Text argument to the function specifies the format, as shown in the table below:
| This format used in the formula function... | Will return the number like this... |
|---|---|
| none_dot | 12345678.99 |
| comma_dot | 12,345,678.99 |
| comma_dot_2 | 1,23,45,678.99 |
| none_comma | 12345678,99 |
| dot_comma | 12.345.678,99 |
| dot_comma_2 | 1.23.45.678,99 |
Is it possible to return just numbers without decimal or commas?
You can set up a numeric field to display with no commas or decimal marks. To do so, you'll need to set the decimal places to 0. And then, for the display format, pick the first option under Period decimal mark, or Comma decimal mark: