Text formatting functions
Present a number or a template as text a reader can use.
Syntax
Write the function's name and the value to present, with the options it asks for.
DOLLAR(number, decimals)
FIXED(number, decimals, no_commas)
NUMBERVALUE(text, decimal_separator, group_separator)
HANDLEBARS(template, data, [mode])
TEXT(number, format)
SPELLNUMBER(number)
ARRAYTOTEXT(array, [format])Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
DOLLAR
Required: Yesnumber
Required: YesdecimalsFIXED
Required: Yesnumber
Required: Yesdecimals
Required: Yesno_commasNUMBERVALUE
Required: Yestext
Required: Yesdecimal_separator
Required: Yesgroup_separatorHANDLEBARS
Required: Yestemplate
Required: Yesdata
Required: NomodeTEXT
Required: Yesnumber
Required: YesformatSPELLNUMBER
Required: YesnumberARRAYTOTEXT
Required: Yesarray
Required: Noformat
Examples
A computed line total, a refund and a round figure, each shown as currency.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
DOLLAR | text | The amount as currency with a dollar sign and thousands separators | DOLLAR(1234.5, 2) |
FIXED | text | The value as text with a fixed number of decimals | FIXED(1234.567, 1, false) |
NUMBERVALUE | decimal | The number the text holds | NUMBERVALUE("1,234.5", ".", ",") |
HANDLEBARS | text | The template with each name filled in | HANDLEBARS("Hi {{name}}", {"name": "Ada"}) |
TEXT | text | The number written in the format | TEXT(1234.5, "#,##0.00") |
SPELLNUMBER | text | The number in words. A decimal is written as dollars and cents | SPELLNUMBER(1250) |
ARRAYTOTEXT | text | The list written out as text | ARRAYTOTEXT([1, 2, 3]) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 | A required argument is missing, such as DOLLAR(1234.5) | Give the decimals |
HTQL-5005 | DOLLAR or FIXED was given a whole number | Write the amount with a decimal point, such as 1234.0 |
HTQL-2001 | HANDLEBARS was given a mode other than html or text | Use html or text |
Limitations
DOLLARandFIXEDreturn text, not numbers. Do the arithmetic first and format last.DOLLARandFIXEDneed the amount as a decimal:DOLLAR(1234, 0)is refused withHTQL-5005,DOLLAR(1234.0, 0)is not.NUMBERVALUEneeds all three arguments: with only the text it stops with the message "Out of bounds access". Coming: reading a number that uses a comma for the decimal point,NUMBERVALUE("1.234,5", ",", "."), which answers 1.2345 where 1234.5 is meant. The form with a point for the decimal,NUMBERVALUE("1,234.5", ".", ","), answers 1234.5.- Coming: a variable that holds a record with a single field, passed to
HANDLEBARS, fills nothing: afterLET order = {"name": "Ada"},HANDLEBARS("Hi {{name}}", order)answers "Hi ". A record with two or more fields, or a record written in the call, works.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Format a value only when you present it.
- Use HANDLEBARS for messages with several values.