Use UUID to achieve a unique number

1. Problem Description

If there is no specific field as the primary key when reporting, we hope to automatically generate a string of random characters as the primary key, and it cannot be repeated. The effect is as shown in the ID field of the figure:

2. Solution

The FineReport designer has a built-in UUID function. When setting the Data Entry Attribute, the value of the primary key can use this function to generate a unique identification code and realize the uniqueness of the number.

1. Preparation of data

Create a new dataset ds1, and the SQL statement is: select * from book

2. Designing data entry form

1) Designing report

Drag the fields in the dataset to cells A2 ~ E2, and add Custom insert and delete buttons in cells F2 and G2, as shown in the following figure:

2) Previewing report

After the form is designed, first data entry preview and take a look at it. It turns out that there is data in the form, and there are random strings under the ID field. Now we want to keep the existing IDs in the table unchanged, and new IDs can be generated when inserting new data, so how do we use the UUID() function to achieve this?

3. Setting data entry attributes

Add built-in SQL submission to the report, the specific settings are as follows, where the value of the primary key field of id is set to the formula: if(len(A2)=0,UUID(32),A2)

The meaning of the formula is that if a new row is inserted, the UUID() function is used to automatically generate a random string, and it will be stored in the library when submitted. If it is an existing data row, the id of these data rows can remain unchanged.

4. Effect preview

1)Data entry preview

Save the report, click data entry preview, add three rows of data, leave the ID field blank, fill in the other fields, and click Submit after filling in.

2) Automatic generated

Refresh the page, you can find that the empty ID field is automatically generated with a string by filling in the attribute UUID formula, and the original ID has not changed.

Note: App and H5 preview are supported.

For the completed template see:%FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Form\Use UUID to achieve a unique number.cpt

Click to download the template: Use UUID to achieve a unique number.cpt

1. The page is inconsistent with the receipt ID

When the cell uses UUID formula, the ID value obtained in the data entry preview interface is inconsistent with the receipt ID value. As shown in the following figure:

This is because the formula editing interface defaults to the option of "Preserve formula in data entry", and the formula will be calculated twice when the report is submitted, which will cause the id to be regenerated when entering the warehouse.

To solve this problem, you only need to uncheck "Preserve formula in data entry" in the cell formula setting interface where UUID () is located.

2. Modifying data after successful submission

After data entry successful, if a piece of information is directly modified without refreshing the page, it will be found that a new piece of data is added after submission, but the original data is not updated normally.

When the page is not refreshed after successful entry, a piece of information is modified. Although the ID value on the page does not change, the UUID () will change every time the UUID () is submitted successfully because "Preserve formula in data entry" is checked in the formula, resulting in new pieces of data.

To solve this problem, you need to uncheck the "Preserve formula in data entry" in the cell formula setting interface where UUID () is located.

3. The ID column is not displayed when the dataset is previewed

After data entry succesful, the existing data in column ID can be seen in the database table, but when you click dataset preview in the designer, column ID is not displayed, as shown in the following figure:

This is because the ID field in the book table in FRDemo is of type blob and does not support dataset preview.

You want the random characters generated by the UUID to display properly. You need to empty the data table first, and then change the ID field type to text or string type. After filling successfully, the preview can be displayed.

However, the modification will be effective only when the table is empty, because the designer will judge the field type of the first data when filling in it. After the first judgment , modifying the field type of the table will not affect the preview display of the dataset.

4. Using UUIDs in Multiple Built-in SQL

If the report is a multi-source report type, for example, the basic employee information is stored in Table 1, and the employee department information is stored in Table 2. Table 1 and Table 2 have the same field "Employee ID". Both tables must be saved when filling in "Employee ID".

At this time, you must use UUID() in the cell to generate the employee ID, and set two "Built-in SQL Submit" in the report filling property, which are bound to Table 1 and Table 2, respectively. The "Employee ID" field of Table 1 and Table 2 are bound to the same cell.

If you use the UUID() function for each report in the report data entry attributes, the generated serial numbers will be different, which will lead to inconsistent employee IDs.