Returning Display Values in a Drop-down Tree in a Cell

Problem

If a drop-down tree widget in a cell is configured with both actual and display values in the data dictionary, the display values are displayed during selection, and actual values are displayed in the cell when the drop-down tree widget is in the blur state.

For example, the drop-down tree in the following figure is used to display the department structure. The values in the drop-down tree are changed from the display values, namely department names, to the actual values of options, namely department IDs, when the drop-down tree widget is in the blur state.

How to display the display values in a drop-down tree when the drop-down tree widget is in the blur state, and submit the display values to the database?

Cause

The drop-down tree returns an array. However, if the full hierarchical path is returned, the full hierarchical path of each node will be regarded as a single string to match data when actual values are converted to display values. Consequently, the corresponding display values cannot be matched.

Implementation Method

You can select Data Dictionary in Display, and set Actual Value and Display Value. In this case, the display values are finally displayed when the drop-down tree widget is in the blur state. You can use the MAPARRAY() and VALUE() functions to convert actual values to display values and submit the display values to the database.

Data Preparation

1. Create a general report and add a database query dataset ds1 with the SQL statement SELECT * FROM CorporationDepartment.

2. Add a tree dataset Tree1 and select ds1 mentioned above, as shown in the following figure.

Drop-down Tree Adding

Add a Drop-down Tree widget to cell A1, set Tree Creation to Automatic Creation, set Actual Value to DepartmentID, and set Display Value to Department_name. Select Multiselect and deselect Return Full Hierarchical Path. The following figure shows the effect.

Cell Display Setting

Select cell A1, choose Cell Attribute > Display, select Data Dictionary, set Dataset to ds1, set Actual Value to DepartmentID, and set Display Value to Department_name.

Data Entry Attribute Setting

Through the above settings, the display values are displayed when the drop-down tree widget is in the blur state. But the data to be submitted to the database now is still the values obtained by the cell, namely the actual values. In this case, you can use the formula maparray(A1,value("ds1",3,1,item)) where the maparray function is used to create an array and the value function is used to populate this array with department names corresponding to each department ID in cell A1.

If you need to concatenate the values in the result and store the result as a string in the database, you can add a string concatenation function JOINARRAY. The formula is JOINARRAY(maparray(A1,value('ds1',3,1,item)),',').

Effect Display

The following figure shows the preview effect on PC.

Note:

The solution also supports mobile preview.

For details, you can download the template Returning Display Values in a Drop-down Tree.cpt.