Advanced number formatting

"Baserow currently only supports a very simplistic version of number formatting, as far as I can tell. So far all you can specify is the number of decimal places and whether negatives are allowed. Meanwhile, your average spreadsheet has quite sophisticated number formatting. You can specify decimal places, leading zeros, thousands separators, special purpose formatting (currency, percent, fractions, scientific notation, etc), and even custom formatting specs.

In my current use case, I wish Baserow would let me at least specify leading zeros so I could have integers of fixed width. However, an argument could probably be made for any of what your average spreadsheet offers.

While this would ideally be something added as additional options for a number column, it could also be provided via some sort of formula command to do number-formatting into an output column.

Here are the separate issues to introduce currency and percent field types."

Community request: https://community.baserow.io/t/better-number-formatting/1579

Acceptance criteria:

Field settings

  • The new settings must be added to the "number field subform", where we already have the Decimal places setting.
  • Selecting a prefix and/or a suffix should be possible. By default, both will be empty, meaning nothing will be added before or after the number value.
  • The length of the prefix and suffix should be limited to 10 characters, and both the backend and the frontend should validate that the max length is respected.
  • It should be possible to choose between these combinations of thousands and decimal separators:
    • Comma, period
    • Period, comma
    • Space, comma
    • Space, period
  • It should be possible to leave the number_separators setting empty, meaning no formatting will be applied to the number (as it is now)

Parse / format

  • If not editing the value, the formatted number should be shown
  • When the user edits the value, the prefix, the suffix, and the thousand separators are removed to make it easier to edit the number. The decimal separator should be maintained and should respect the settings. When the user stops editing the value, the number should be formatted again according to the field settings.
  • It should be possible to paste formatted values that match the field format and the field should parse the number correctly and send it to the backend, both in the grid view or directly in the cell input element
  • The field formatting must be applied everywhere in the frontend:
    • all the view types: grid, gallery, form, calendar, kanban, timeline
    • the aggregations at the bottom of the grid view
    • the row edit modal
    • the group column if a number is used to group_by
    • the number input component used for view filters
    • numbers shown in formulas, lookups or arrays of numbers
    • numbers shown in history
  • The prefix, formatted number and the suffix will be separated by spaces, like: {prefix} {formatted_number} {suffix}
  • It should be possible to format negative numbers. In that case the - sign should be after the prefix and before the number, separated by a space, i.e. prefix=$ -> $ -10.00

Backend specific

  • A formula that references number fields but results in a different type won't respect the formatting settings. For example: totext(field('number')) won't respect those settings as the formula language can't consider those settings, but it must be possible to sele
  • The value sent for number view filters must remain a number, not a formatted version of it (even if it will be converted to a string in the backend).
  • Must be compatible with the formula system. If the formula type is a number, selecting prefix/suffix and separators for the formula field should be possible, and the values must be formatted accordingly.

Import/Export

  • When importing numbers from a file, it should be possible to parse them as we do in the frontend, allowing users to provide formatted numbers.
  • Exported values should be formatted according to the field settings for all the available export formats: CSV, JSON, XML and XLSX. The value in the exported file must look the same as in the UI. If a user wants numbers in the exported file instead, they must change the field settings or create a formula that references the number field without any formatting.

Nice to have

  • When a value is pasted, either in the grid view or directly in edit mode in the cell, we should try to parse the value. To properly parse it, (I think) we should:
    • remove the prefix from the beginning if present and set for the field or remove the first block before the first space if it contains something different from numbers or separators
    • remove the suffix from the end if present and set for the field or remove the last block after the last space if it contains something different from numbers or separators
    • try to parse the remaining content, try to distinguish the decimal and the thousand separator if possible or preferring the field settings otherwise.
  • It should be possible to paste numbers in different formats, and they should be parsed correctly both in the backend (when importing a file) and in the frontend
Edited by Przemyslaw Kukulski