Automatic Primary Key Generation by the UUID Function

Problem

When no field is specified as a primary key during data entry, you may want to generate a string of random and unique characters as primary keys. The following figure shows the effect of using the ID field as the primary key.

Solution

When setting data entry attributes, you can use the built-in UUID function of the FineReport designer to generate unique identifiers as primary keys, ensuring code uniqueness.

Data Preparation

Create a dataset named ds1 with the SQL statement SELECT * FROM Book.

Designing the Data Entry Table

1. Drag the corresponding fields from the dataset into cells A2 to E2, set cell B2 to be the leftmost parent cell, and add custom Insert Row and Delete Row buttons to cells F2 and G2, respectively, as shown in the following figure.

2. After designing the table, click Data Entry Preview to preview the effect. The table already contains existing data and the ID field specifies random strings.

In this case, to maintain existing IDs in the table and generate new IDs for data added, how to realize the effect by the UUID() function?

Setting the Data Entry Attribute

You can add a built-in submission for the report, as shown in the following figure. The value of the ID field (the primary key) is set to the formula if(len(A2)=0,UUID(32),A2).

Through this formula, for newly inserted data rows, random strings will be automatically generated as the value of the ID field by the UUID function and will be stored in the database during submission. For the existing data rows, the values of the ID field remain unchanged.

Effect Display

1. Save the report and click Data Entry Preview. Add three rows of data with only the values of the ID field being left blank, and click Submit.

2. Refresh the page. In this case, strings are automatically generated (by the UUID formula set in Data Entry Attribute) into the cells where values of the ID field are left blank before. Meanwhile, the original values of the ID field remain unchanged.

Note:

The template can be previewed on the DataAnalyst app and HTML5 terminals.

For details, you can download the template Automatic Primary Key Generation by the UUID Function.cpt.

ID Inconsistency During Page Preview and Submission

In some cases, the IDs obtained by the UUID formula in cells are inconsistent on the data entry preview page and during data submission to the database, as shown in the following figure.

This is because Retain Formula for Calculation During Data Entry/Analysis is selected by default on the Formula Definition page. In this case, the system will perform the secondary calculation during data entry submission, generating new IDs during data submission to the database.

To solve the problem, you just need to deselect Retain Formula for Calculation During Data Entry/Analysis on the Formula Definition page for the cell where the UUID() formula is located.

Modifying Data After Successful Submission

After successful data entry, if you modify a record of information without refreshing the page, the original data will not be normally updated. Instead, a new record of data will be added after submission.

Even though you modify a record of information without refreshing the page after successful data entry, the values of the ID field on the page are not changed. However, since Retain Formula for Calculation During Data Entry/Analysis is selected on the Formula Definition page, values are regenerated by the UUID() formula upon each successful submission, creating multiple new records of data.

To solve the problem, you just need to deselect Retain Formula for Calculation During Data Entry/Analysis on the Formula Definition page for the cell where the UUID() formula is located.

ID Column Not Displayed During Dataset Preview

After successful data entry, the data in the ID column is displayed in the database table, but is not displayed during the dataset preview after you click Data Entry Preview in the designer, as shown in the following figure.

This is because the ID field in the Book table of the FRDemo database is of the BLOB type that is not supported for the dataset preview.

To normally display the random characters generated by the UUID function, you need to clear the database table first, and then modify the data type of the ID field to the text or string type. In this case, after successful data entry, the data in the ID column can be displayed during the data entry preview.

However, the above setting can take effect only after you have cleared the database table. This is because table field type re-modification will not affect the dataset preview display after the designer has judged the field type of the data upon the data entry of the first record of data. And after the first judgment, the preview display of the dataset will not be affected after you modify the field type.

UUID Function in Multiple Built-in SQL Submissions

Sometimes, the data entry report may be a multi-source data entry report. For example, the employees' basic information is stored in table 1, and the employees' department information is stored in table 2. Table 1 and table 2 share the same field EmployeeID. During data entry, the field EmployeeID needs to be saved in both of the two tables.

In this case, you must use the UUID() formula in a cell to generate employee IDs and bind two built-in SQL submissions bound to table 1 and table 2, respectively, in Data Entry Attribute. You need to bind the EmployeeID fields in table 1 and table 2 to the same cell where the UUID() formula is located.

If you use the UUID() formula for all reports in Data Entry Attribute, different serial numbers will be generated for each report, resulting in inconsistent employee IDs.