Auto Data Matches Based on Widget Values

Application Scenario

You may hope that corresponding data in other related cells can be automatically retrieved from the database after you enter data in a cell, as shown in the following figure.

Implementation Method

You can achieve the data entry linkage effect through three methods.

• You can use the value() function, which boasts good performance.

• You can use the ds1.select() function, which has average performance.

• You can use the SQL() function, which has poor performance in large-data-volume scenarios. All these three methods can be used in expansion scenarios.

Note:

1. Hierarchical coordinate (such as A1[A1:-1], A1[!0], A1{A1=1}, and &A1) linkage is not supported.

2. The three-level linkage is not supported. That is, C cannot be linked based on B when B is linked based on A.

Table Design

1. Create a general report, design the data entry table, add a drop-down box widget to cell A3, and add text widgets to cells from cell B3 to cell F3, as shown in the following figure.

2. Set Expansion Direction of cell A3 to Vertical, otherwise multiple-row adding will be limited, as shown in the following figure. 

Data Dictionary Setting

Select cell A3 and bind the drop-down box widget to the data dictionary, as shown in the following figure.

Method 1: Through the value() Function

Add a dataset named ds1 and enter the SQL statement SELECT * FROM Product.

Add formulas to cells from B3 to F3 to query specific data based on the value selected from the drop-down box of cell A3, as shown in the following figure.

The formulas added to cells from B3 to F3 are as follows.

• Cell B3: value("ds1",2,1,A3)

• Cell C3: value("ds1",5,1,A3)

• Cell D3: value("ds1",6,1,A3)

• Cell E3: value("ds1",7,1,A3)

• Cell F3: value("ds1",8,1,A3)

Method 2: Through the ds1.select() Function

Add a dataset named ds1 and enter the SQL statement SELECT * FROM Product.

Add formulas to cells from B3 to F3 to query specific data based on the value selected from the drop-down box of cell A3, as shown in the following figure.

The formulas added to cells from B3 to F3 are as follows.

• Cell B3: ds1.select(Product_name,ProductID=A3)

• Cell C3: ds1.select(Unit_quantity,ProductID=A3)

• Cell D3: ds1.select(Cost_price,ProductID=A3)

• Cell E3: ds1.select(Unit_price,ProductID=A3)

• Cell F3: ds1.select(Inventory_quantity,ProductID=A3)

Method 3: Through the SQL() Function

Add formulas to cells from B3 to F3 to query specific data based on the value selected from the drop-down box of cell A3, as shown in the following figure.

The formulas added to cells from B3 to F3 are as follows.

• Cell B3: sql("FRDemo","select Product_name from Product where ProductID="+A3,1,1)

• Cell C3: sql("FRDemo","select Unit_quantity from Product where ProductID="+A3,1,1)

• Cell D3: sql("FRDemo","select Cost_price from Product where ProductID="+A3,1,1)

• Cell E3: sql("FRDemo","select Unit_price from Product where ProductID="+A3,1,1)

• Cell F3: sql("FRDemo","select Inventory_quantity from Product where ProductID="+A3,1,1)

Note:

ProductID in the formula corresponds to the number in cell A3. If the content in cell A3 is a string, you need to modify the formula. For example, modify the formula sql("FRDemo","select Product_name from Product where ProductID="+A3,1,1) to sql("FRDemo","select Product_name from Product where ProductID='"+A3+"'",1,1).

Row Insertion Policy Setting

If you need to insert rows, you can set Row Insert Policy to allow the inserted rows to inherit the linkage effect.

Select cells from B3 to F3 and set Row Insert Policy to Original, as shown in the following figure.

Note:

If you have modified the values of cells (from B3 to F3) where the linkage formulas are located during data entry, the newly entered values will overwrite the formulas, causing cells to no longer be linked when you re-select Product ID. If you insert rows later, the inserted rows will also no longer have the linkage effect after inheriting the original values.

Effect Display

For details about the effect on PC, see section "Application Scenario." The following figure shows the effect on mobile terminals.

You can download the example template.

Method 1: Auto Match - value() Function.cpt

Method 2: Auto Match -ds1.select() Function.cpt

Method 3: Auto Match - sql() Function.cpt

If you need to achieve the linkage effect for data imported from Excel, ensure that columns with linkage formulas (such as columns from B3 to F3 in the above examples) in Excel do not exist, otherwise, the linkage formulas will be overwritten, resulting in the linkage failure. The following figure shows an Excel example where the linkage can take effect after import.