Checking Duplicate Data in the Database by Concatenating Multiple Fields

Problem

When entering data in the data entry report, you may want to validate the uniqueness of data by using two or more fields, ensuring that the entered data does not exist in the database.

Solution

During report design, you need to concatenate multiple fields into one field for validation and concatenate the corresponding fields into one field in the database as well. Then you need to set the validation formula of the built-in submission in Data Entry Attribute to validate if the two fields obtained by concatenation are the same. If the two fields are the same, duplicate data exists in the database.

The following example takes Product_name and Unit_price as the composite key to determine whether the entered value is the same as the existing data in the database.

Data Preparation

Create a dataset ds1 and enter the SQL query statement SELECT *,(Product_name||Unit_price)AS AA FROM SProduct.

(Product_name||Unit_price) is used to concatenate Product_name and Unit_price into one field. This syntax applies to the SQLite database. You can search the internet for the concatenation syntax for other databases.

Template Design

1. Design the table as shown in the following figure. Add text widgets for cells A2 to G2.

Insert the formula CONCATENATE(B2, C2) into cell H2. In this case, the content entered in cells B2 and C2 will be concatenated during data entry.

2. To add multiple records during the frontend data entry, you need to set Expansion Direction of cell A2 to Vertical, as shown in the following figure.

3. When adding multiple records on the frontend, you need to retain the formula in cell H2 for the added row. Therefore, set Row Insert Policy to Original for cell H2, as shown in the following figure.

4. Since cell H2 is only used for identifying duplicate data and does not need to be displayed, you can hide column H after completing the setting.

Data Entry Attribute Setting

Choose Template > Data Entry Attribute > Submission, click +, and select Built-in SQL. Set the data entry attributes, as shown in the following figure.

Choose Template > Data Entry Attribute > Validate Data, click +, and select Built-in Validation. Set the validation formula, as shown in the following figure.

The validation formula is INARRAY(H2,if(COUNT((ds1.group(aa)))>1,ds1.group(aa),ARRAY(ds1.group(aa))))=0.

If the validation formula is unsatisfied, the prompt "The current product name and unit price are the same as those in the database." appears.

The following explains the formula.

The formula is used to retrieve the aa field obtained by concatenation from ds1 as an array, and then iterate through each row to determine whether H2 exists in the array. If H2 does, duplicate data exists.

Effect Display

1. PC

Save the report and click Data Entry Preview. Enter the product name and the unit price that are the same as those in the database, click Data Validation, and the error message is displayed.

2. Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

For details, you can download the template Checking If Data Is Duplicate by Built-in Submission Validation.cpt.