Submit multi-value by drop-down checkbox

1. Problem description

There is a drop-down checkbox in the data entry template, which sets the actual value and the display value. It is hoped that the selected data will be separated as different data and submitted to the database, and the display value needs to be submitted to the database. As shown below:

2. Solution

If you want to split multiple pieces of data into the database, the return value of the drop-down checkbox needs to select the array format.

At this time, if you use functions such as value or maparray in the submitted attributes to get the display value, two arrays will appear, resulting in a cartesian product. Therefore, this requirement needs to be realized in a more ingenious way.

Use the range function to make a fixed table, and use a formula to match the actual value of the selected data with the display value one by one, so that there is a corresponding relationship when submitting.

1. Design template

Create a new dataset ds1, the sql statement is SELECT * FROM Product order by ProductID limit 5

Set "Drop-down Checkbox" widget in cell B2, the "Return Value Type" is set to Array, the data dictionary is ds1, the actual value is ProductID, and the display value is Product_name.

2. Set formulas

Set formulas in cells A4, B4 and C4 to build a submission form.

A4: range(1,max(value("ds1","ProductID")))

B4: INDEXOFARRAY(B2,A4)

C4: value("ds1","Product_name","ProductID",B4)

The formula is explained as follows:

A4: Get the maximum number of drop-down checkbox options to construct the sequence number of the table

B4: Obtain the ProductID selected by the drop-down checkbox according to the serial number, and expand the display

C4: Obtain the corresponding Product_name according to the ProductID of B4

3. Set data entry attributes

First create a table to store the submitted data. This article uses mysql as an example and then establish a mysql data connection in the designer. The table structure is shown in the following figure:

Set the reporting properties as shown in the figure:

Note:

1) The Value field needs to be set as the primary key. Otherwise, if only the ID field is set, the data with the same ID according to the submission logic will be regarded as an update submission, and there is only one piece of data that is finally stored.

2) The submission condition needs to be set to len(B4)!=0, the purpose is to remove the empty data in the constructed form.

4. Preview effect

Click Data Entry Preview, the effect is as follows:

Click to download: 

Submit_by_drop-down_checkbox.cpt