Duplication Verification

1. Application scenarios

Sometimes it is necessary to check whether the data reported in a certain field is duplicate. If there are duplicate values, an error message will be prompted and no submission is allowed.

2. Implementation ideas

Take the filled cell as an array, and set formulas in the other two cells. One formula gets the original value of the array, and the other formula gets the value of the array after duplication.

If there are duplicate data in the array, the values of the two cells are not the same. Finally, the two cells are verified in the submission verification. If the cell values are equal, the submission is allowed. If the cell values are not equal, an error is reported.

1. Data preparation

Create a new data query dataset ds1, and the SQL statement is: select * from SALES_COST

2. Design form

1) Design the data entry form, drag the salesperson and total sales field to A2 and B2 cells respectively, add title, and add two formulas to A5 and B5 cells, as shown in the figure below:

• A5 cell formula: joinarray ([A2], "), returns the string of all the values extended by A2 cell, keeps the duplicate data, and does not add the interval symbol
  

• B5 cell formula: joinarray (unique array ([A2]), "), returns the string of all values expanded by A2 cell, removes duplicate data, and does not add interval symbol
  

2) The A5 and B5 cells are hidden, and there is no need to display the data as a condition when filling in the front end. The final table is shown in the following figure:

3. Setup submission

In the menu bar, click Template > Data Entry Attribute to add a new built-in SQL submission, as shown in the following figure:

4. Setting data verification

In the designer menu bar, click Template > Data Entry Attribute to add a built-in verification under data validation settings, as shown in the following figure:

• Calibration formula: A5 = B5
  

• Error Information: "There are duplicate data in the salesperson field! "
  

Note: if A5 and B5 are not equal, there will be duplicate data.

5. Effect preview

1) PC terminal:

Save the template and click Data Entry Preview. If there are duplicate data filled in the salesperson field, the verification fails, as shown in the following figure:

2) Mobile terminal:

Completed template, see %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryAttribute\DE_CheckForDup.cpt

Click download template: 

DE_CheckForDup.cpt

The formula inserted in cells A5 and B5 can be replaced by the following two groups: