NVL Function

Function

In data entry applications, you may encounter a scenario where only the non-empty values from a group of cells need to be saved to a single database field.

Explanation

The usage guide of the NVL function:

NVL(Value1, Value2, Value3, ...) returns the first non-null value from all parameters.

Note:

If the parameters of the NVL function are derived from other formula calculations, ensure that the calculation results are not infinite; otherwise, the calculation result of the NVL function may contain garbled characters.

Data Creation

1. Create a table named C in the database, as shown in the following figure.

2. Create a dataset named ds1 with the SQL statement Select * from C.

Table Design

Merge cells as needed, and drag the corresponding fields into cells, as shown in the following figure.

Widget Setting

Add text widgets to cells B5, C5, E5, E6, E7, and E8, as shown in the following figure.

Data Entry Attribute Setting

1. Select cell E5, add a condition attribute with the formula $$$!=4 to set New Value to null when the current value is not equal to 4, as shown in the following figure.

2. Select cell E6, add a condition attribute with the formula $$$!=2 to set New Value to null when the current value is not equal to 3.

3. Select cell E7, add a condition attribute with the formula $$$!=2 to set New Value to null when the current value is not equal to 2.

4. Select cell E8, add a condition attribute with the formula $$$!=1 to set New Value to null when the current value is not equal to 1.

Other Attribute Setting

To ensure that cells without widgets (such as those containing Excellent, Good, Qualified, and Poor) are automatically added upon record adding, you can select the cells first, choose Cell Attribute > Others > Row Insert Policy, and select Original, as shown in the following figure.

Report Data Entry Attribute Setting

Enter the formula=NVL(E5,E6,E7,E8) in Value of NUMBERQ to retrieve the first non-empty value for data entry, as shown in the following figure.

Effect Display

PC

Save the template and click Data Entry Preview. The following figure shows the effect.

Click Add Record, and click Submit, as shown in the following figure.

The effect of a successful submission is as shown in the following figure.

You can refresh the page to see the successfully submitted value. The submitted value is the first non-empty value, which is 3, as shown in the following figure.

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

For details, you can download the template: NVL Function.cpt.