When Numerical Fields need to be able to support Text

There are instances where a field is expected to hold a numerical value, but if it’s empty, or it doesn’t apply, it may be desirable to enter a text value; something like “N/A”, for example.

In that case, instead of using a numerical field, you can use a short text field (or text, but short text may be better in these cases).
For display purposes within your table it makes no difference; the field will show a number of text.
However, if the value in the short text field needs to be used in a calculation, and you try simply add or apply some other mathematical operation involving the short text field, you will get a nonsensical value. The solution is use the “to_number” function in the calculation, to extract the numerical value, and ignore the text.

For example: Num01 is a numerical field. Stext01 is a short text field. Calc01 is a calculation field with the formula “SUM($Num01,to_number($Stext01))

If we enter the values:Num01: 150 , Stext01: 25 –––> Calc01:175 (150+25=175)

But we can also enter a text value in Stext01:
Num01: 150, Stext01:N/A –––> Calc01:150 (150+N/A=150)

1 Like