Logically Deleting Data by JS in the Data Entry Report

Problem

In some cases, you may want to back up the data for record-keeping while ensuring that the backed-up data is not displayed on the business page. How to achieve this effect?

Solution

You can use logic deletion to address this problem. That is, you can add the Status column to the database table. By modifying the value of the Status column, you can control whether the data is visible or not.

You can determine whether to display the data by filter. That is, you can control whether to display the data based on the value of the Status field. For example, the original query statement is SELECT * FROM CUSTOMER. After adding a constraint based on Status, the query statement is SELECT * FROM CUSTOMER WHERE Status='1'.

The following takes the CUSTOMER table in FRDemo as an example. Since the table does not contain the Status field, you need to use the DISTRICT field as Status.

When the value of the DISTRICT field is one, the status of the data is Normal. When the value of the field is zero, the status of the data is Deleted.

Data Preparation

Create a general report and create a database query ds1 with the SQL statement SELECT * FROM CUSTOMER.

Table Design

Drag the data columns from ds1 into the template, set borders for the table, align the text to the center, and set the text color in column F to red. The following figure shows the table style.

Button Widget Adding

Add button widgets for cell G2 and H2, and name the two button widgets Delete and Cancel, respectively. The following figure shows the steps.

Click Event Adding

1. Select cell G2, choose Widget Setting > Event on the right, and add a Click event. Add parameters row and col in the Click event, and enter the formulas row()-1 and col()-2 in Value for the two parameters, respectively. The formulas represent the current row and the column immediately preceding the current column.

The JavaScript code is as follows:

FR.Msg.confirm("Tip","Sure to delete data?",function(value){if(value){
_g().setCellValue(col,row,"0");
_g('${sessionID}').writeReport(); }
else{_g().setCellValue(col,row,"1");
_g().parameterCommit(); } })

The following figure shows the steps.

2. Select cell H2, choose Widget Setting > Event on the right, and add a Click event. Add parameters row and col in the Click event, and enter the formulas row()-1 and col()-3 in Value for the two parameters, respectively. The formulas represent the current row and the two columns immediately preceding the current column.

The JavaScript code is as follows:

var v1 = _g().getCellValue(0, col, row);
FR.Msg.confirm("Tip", "Sure to cancel data deletion?", function(value) {
if (value) {
_g().setCellValue(0, 0, i,"FanRuan");
_g().verifyAndWriteReport(true);
} else {
_g().setCellValue(0, 0, i,"FanRuan");
_g().verifyAndWriteReport(true);
_g().parameterCommit();
}
});

The following figure shows the effect.

Report Data Entry Attribute Setting

Choose Template > Data Entry Attribute on the menu bar, click +, and select Built-in SQL. Select the CUSTOMER table in the FRDemo database, add the CUSTOMERID and District fields and the corresponding cells, and click OK, as shown in the following figure.

Note:

For details about data entry attribute setting, see Data Entry Attribute Overview.

Display Setting

Select cell F2, choose Cell Attribute > Display, select Formula Form, and enter the formula if($$$="1","Normal","Deleted"). This formula indicates that if the value of the current cell is one, the status of Normal is displayed. Otherwise, the status of Deleted is displayed.

Effect Display

PC

Save the template and click Data Entry Preview. The following figure shows the effect.

Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

For details, you can download the template Logically Deleting Data by JS.cpt.