Overview
Data in text-based fields, such as Single Line and Single Select, is stored as text strings. Even numbers entered in these fields are treated as text strings and therefore cannot be used directly in calculations or aggregations.
This article introduces how to use the VALUE function in the following two cases, enabling numbers stored as text to be used in calculations:
Calculating Scores from Radio Fields
Numeric-only Validation
Calculating Scores from Radio Fields
In an after-sales service rating scenario, customers need to rate both Service and Quality, and the two scores need to be added together to calculate a total score.
Create a form according to the scoring rules above by completing the following steps.
Step 1 Designing the Form
Create a form named After-sales Service Rating and add the following fields:
Field Name | Field Type | Field Settings |
Please rate the product service and quality | Divider | — |
Service | Radio | Set the custom options to 1, 3, and 5 |
Quality | Radio | Set the custom options to 1, 3, and 5 |
Total Score | Number | — |
Step 2 Setting the Formula
Select the Total Score field. Go to Field Properties > Initial Value, select Formula, and click Edit.
On the formula editing page, set the formula for Total Score as follows:
VALUE(Service)+VALUE(Quality)
This formula first converts the selected scores from text strings into numbers and then adds them together.
Note:
If you set the formula directly as Service+Quality, the two scores will be concatenated instead of added. For example, 1+3 will return 13.
Step 3 Demostration
Numeric-only Validation
When using VALUE(text), if the text contains only digits, the function returns the corresponding number. If the text contains letters, symbols, or other non-numeric characters, the function returns 0.
Based on this behavior, you can use the VALUE function to validate whether a text string contains digits only.
For example, when collecting bank card numbers, you can require users to enter digits only before submitting the form.
Step 1 Designing the Form
Create a form named Bank Card Information and add the following fields:
Field Name | Field Type |
Name | Single Line |
Bank Card Number | Single Line |
Step 2 Adding Data Validation
Go to Form Properties > On Form Submission > Data Validation, and click Add Condition.
Set Prompt for failed check to:
Please enter digits only for the bank card number.
Set the formula as follows:
VALUE(Bank Card Number)!=0
This formula means that the form can be submitted only when the bank card number contains digits only and the value returned by the function is not 0. Otherwise, the submission will fail.
After completing the settings, click OK to save the validation condition.
Note: If a workflow has been enabled for the form, configure this formula in Node Validation instead.
Step 3 Demonstration
