Duplicate Data Checking on the Data Entry Page

Application Scenario

In some cases, you may want to check whether the entered data of a field is duplicated. If duplicate values exist, the validation error message is displayed, and the entered data cannot be submitted to the database, as shown in the following figure.

Implementation Method

Note:

You can import the Excel data using this solution. For import performance improvement, you are advised to adopt the setting in section "Extended Example."

You can set the cells in Validation Formula of the built-in validation. If expandable cells are included, all values will be iterated and filtered. With this feature, you can convert all values expanded from the cell into arrays. Then, you can use the GREPARRAY() function in the validation formula to iterate, filter, and count all the values in the array. If the count exceeds one, multiple duplicate values exist. In this case, the validation fails, and an error prompt is displayed.

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

Create a database query ds1 with the SQL query statement SELECT * FROM Sales_Volume, as shown in the following figure.

Template Design

1. Enter text in cells A1 and B1, drag data columns into cells A2 and B2, and design the table style, as shown in the following figure.

2. Insert the formula A2 into cell A3 to convert the string in cell A2 into an array, as shown in the following figure.

Note:

In this case, you need to insert the formula A2 instead of entering the text A2 in the cell.

Submission Setting

1. Choose Template > Data Entry Attribute > Submit on the menu bar, click +, select Built-in SQL, and set Submission Type to Smart Submission.

2. Select FRDemo from the drop-down list of Database and select Sales_Volume from the drop-down list of Table. Click Smart Add Field, add the required fields, click Smart Add Cell, add the corresponding cells, tick Salesperson as the key, and click OK, as shown in the following figure.

Data Validation Setting

1. Choose Template > Data Entry Attribute > Validate Data on the menu bar, click +, and select Built-in Validation.

2. Click +, and enter len(GREPARRAY(A3,item=A2))<=1 in Validation Formula and "Data is repeatedly entered into the Salesperson field." in Prompt If Validation Formula Is Unsatisfied, as shown in the following figure.

Note:

1. The formula means that the array in cell A3 will be iterated and filtered, and the count of each value in cell A2 will be returned. If the count exceeds one, duplicate data exists.

2. The formula mentioned above cannot check for empty values. If you want to check for empty values, you can modify the formula to len(GREPARRAY(A3,item=A2))=1.

Effect Display

Save the report and click Data Entry Preview. When the data of the Salesperson field is duplicated, the validation fails, as shown in the following figure.

1. PC

The following figure shows the effect on PC.

2. Mobile Terminal

The following figure shows the effect on mobile terminals.

If you want to import the Excel data, you can adjust the solution to improve import performance.

The red box in the following figure shows the specific modifications, and other parts remain the same as the original solution.

1. Insert the formula row() into cells A3 and B4.

2. Insert the formula if(A3+1=B4,A2,"") into cell A4.

3. Add the data validation formula len(GREPARRAY(A4,item=A2))<=1.

For details, you can download the template Checking If Page Data Is Duplicated.cpt.

For details, you can download the template Checking If Page Data Is Duplicated (Export).cpt.