Duplication Verification of Multi-field Data

1. Problem description

When users enter data in the report, they want to verify the uniqueness of data through two or more fields.

2. Solution

When designing a report, multiple fields that need to be verified are spliced. The built-in submission verification formula is set in the data entry attributes to verify whether the spliced fields are duplicate with the existing fields in the database.

The following example uses the product name and unit price as the joint primary key to determine whether the input value is duplicated with the existing data in the database.

1. Data preparation

Create a new dataset ds1, input SQL query: SELECT *, (Product_name||Unit_price)as aa FROM Product

Note: in SQL statement (product name | unit price) means to splice product name and unit price into one field.

2. Template design

1) The report is designed as follows:

H2 cell insertion formula: concatenate (B2, C2), which means that the contents of B2 and C2 cells are spliced together when filling in. Hide H2 cells after formula insertion.

2) In order to add multiple records when filling in the front-end report, it is necessary to set the vertical expansion of the A2 cell, as shown in the following figure:

3) When adding multiple records to the front end, the new row needs to retain the formula in the H2 cell, so the insert row strategy of the H2 cell should be set to the original value, as shown in the following figure:

4) The H2 cell is only used to judge the duplicate data and does not need to be displayed, so the H column can be hidden after the setting is completed.

3. Data entry attributes

Click  Template > Date Entry Attributes > Submit , add a built-in SQL, and set the reporting properties as shown below

Add built-in verify. The verification formula is: inarray (H2, DS1. Group (aa)) = 0, indicating that the input data needs to meet the verification formula, that is, it is not in group (aa). Otherwise, an error message will be prompted: "The unit price and name of the current product is duplicate with the existing product."

4. Effect preview

1) PC terminal

Save the report, click data entry preview, and enter the product name and unit price that are repeated in the database. Error information will be prompted during data verification.

2) Mobile terminal:

Completed template, please refer to %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryAttribute\DE_BuiltinDup.cpt

Click download template: 

DE_BuiltinDup.cpt