Clearing the Database Table Before Submitting Data to the Database

Version

Application Scenario

When importing Excel data for submission using the Excel Mark Import plugin, you may want to clear the original data in the database table first and then submit the imported data into the database table, as shown in the following figure.

Implementation Method

1. When you set data entry attributes, multiple built-in SQL submissions are executed in series (from the first one to the last one).

2. You can use the execution sequence to set Submission Type to Deletion Submission for the first built-in SQL to clear the table. Then you can set Submission Type to Smart Submission for the second built-in SQL to import the data.

3. This method ensures transaction consistency. If data import fails, the operation to clear the table will also be rolled back, ensuring that the database reverts to its original state before the transaction.

Template Creation

Choose File > New General Report on the menu bar to create a general report, as shown in the following figure.

Data Preparation

Create a dataset query named ds1 with the SQL statement SELECT * FROM Product, as shown in the following figure.

Report Design

Enter text in cells A1 to K1, set Expansion Direction to Vertical for cells A2 to K2, and design the report style, as shown in the following figure.

Note:

Widgets are not added during table design. You can only import data, but cannot modify it. If data modification is required, you can add widgets to the table.

Excel Mark Import Setting

Choose Template > Excel Mark Import on the menu bar and set the import area. Three import methods are available, namely Clear, Increment, and Cover. In this case, set the import method to Clear, as shown in the following figure.

Note:

When you set the import method to Clear, only the data on the page will be cleared. The Deletion Submission setting in section "Table Clearing Setting" clears the data in the database table.

Mark Import Button Adding

1. Choose Template > Web Attribute on the menu bar.

2. Click Data Entry Setting, select Set for This Template Separately from the drop-down list of Following Settings, and double-click the Import Mark button to add the button to the toolbar, as shown in the following figure.

Table Clearing Setting

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

2. Select the Product table in the FRDemo database, add the ProductID field, and tick the field as the key. Enter the formula SQL("FRDemo","SELECT ProductID FROM Product",1) in Value, as shown in the following figure. With the setting, you can query all values of the ProductID field and clear the table with the field as the key.

Note:

Only one field is needed for table clearing. If you set multiple fields, Cartesian product problems may occur.

Data Import Setting

1. Click + again, select Built-in SQL, and set Submission Type to Smart Submission.

2. Select the Product table in the FRDemo database, click Smart Add Field, add all the fields, add the corresponding cells, and tick ProductID as the key, as shown in the following figure.

Note:

You must add built-in SQLs in the correct sequence. The first built-in SQL is set for clearing the table, and the second built-in SQL is set for importing the data. Otherwise, the expected effect cannot be achieved.

Effect Display

Note:

The effect cannot be previewed on mobile terminals.

Save the report, click Data Entry Preview, click Mark Import on the toolbar, import the Excel file, and click Submit. In this case, the data table in the database will be cleared first, and then the imported data will be submitted to the database, as shown in section "Application Scenario."

For details, you can download the template Clearing the Database Table Before Submitting Data to the Database.xlsx.

For details, you can download the Excel file Clearing the Database Table Before Submitting Data to the Database.xlsx.