JS cpt Refresh a Single Cell or Column

1.1 Requirement

Refresh a cell or a column of cells in real time in the data entry mode. The effect is shown in the figure below:

1.2 Solution

Modify database data through a data entry report.

Create a new data entry report for real-time partial refresh of data. Insert an SQL function and an IF function into the cell. As changes in function values will drive the system to re-read the database, setInterval in a Loading End event can be used to constantly modify function values, so as to refresh data.

2.1 JS-based partial refresh of a template

1) Create a new normal report: Partial refresh.cpt; add a template dataset: select * from Client limit 5; insert a formula into B2 to read the corresponding column through an SQL function, as shown in the figure below:

The formula inserted into B2 is listed below. When the value of E2 changes, the SQL function will acquire a new value. Use an IF statement to change the value to 1 (the result is 1 regardless of the value of E2), which becomes the SQL condition WHERE 1=1

SQL("FRDemo", "SELECT Contact_name FROM Client WHERE 1 = " + IF(E2 = 1, 1, 1) + " AND Company_name = '" + A2 + "'", 1, 1)

Input 1 in the cell E2 to hide column E.

2) Click [Template]>[Web Attributes]>[Data Entry Settings], select Individually set for the template and add a Loading End event, as shown in the figure below:

3) Input the following JS codes:

1. Refresh a single cell:

//Loop function
setInterval(function(){
    //Get the cell object in Row 2, Column 5, i.e., E2
    var _changeCell = $("tr[tridx=1]","div.content-container").children().eq(4);
    //Get the value of E2
    var _changeVal = _changeCell.text();
        if(_changeVal=="1"){
          contentPane.setCellValue("E2",null,"2")
    }else if(_changeVal=="2"){
        contentPane.setCellValue("E2",null,"1")
    }
},1000)//1000 means the loop is refreshed 1 second later

2. Refresh a column, e.g., an expanded column: (this JavaScript is used in the sample template)

//_obj means the object of tr
var _obj = $(".x-table tr");     
//_length means the row number of tr
var _length = _obj.length;     
setInterval(function(){
//Loop over tr, i.e., each expanded row
for(var i = 0; i < _length; i++) {
    //If Row 1 is the title row, then execute continue in the if condition, or change the if condition to a note    //If Rows 1, 2 and 3 are the title rows, please change the if condition to if(i==0 || i==1|| i==2)
    if(i == 0){
         continue;
    }
    //Get the td object in tr
    var _td = _obj.eq(i).find("td");
    //The column to be refreshed, eq(4) means cells in Column E (the index value can be changed as needed)
    var _this = _td.eq(4);
    //Get the value of Column E
    var _changeVal = _this.text();
    //contentPane.setCellValue("E"+(i+1),null,"2") means assigning values to Column E cells. As row number begins from 0, when assigning values to cells in Column E, the index of the row number is i+1
    if(_changeVal == "1"){
        contentPane.setCellValue("E"+(i+1),null,"2")
    }else if(_changeVal == "2"){
        contentPane.setCellValue("E"+(i+1),null,"1")
    }
}
},1000)//1000 means the loop is refreshed 1 second later 
Show Code

2.2 Data entry template

Create a new normal report: Data Entry.cpt; add a template dataset: select * from Client limit 5; drag data columns into the cells as follows:

Add text widgets to cells A2, B2 and C2; and add a number widget to D2;

Click [Template]>[Data Entry Attributes] and set data entry attributes as shown below:

Submit Type: Update Submit; select corresponding tables and add corresponding fields and cells.

2.3 Preview

Data entry preview: Data Entry.cpt and Partial refresh.cpt.

Modify and submit the value of contact name on the data entry page;

View the JS-based partial refresh page, where we can see that contact names (Mr. Wang, Wang Xuanhao, Mr. Fang) have been updated to the modified values (1, 2, 3), as shown in the figure below:

Data Entry.cpt

Partial refresh.cpt