Automatic Serial Number Generation and Sorting by JS During Data Entry

Problem

When entering data during data entry, you may want to automatically generate serial numbers and dynamically renumber the existing rows upon row insertion or deletion. The following figure shows the effect.

Implementation Method

You can use the SEQ() function to obtain the serial numbers. Since inserting or deleting rows will trigger the loading end event of the page, you can use the frontend JavaScript API in the loading end event to renumber the rows after inserting or deleting rows every time.

Note:

The effect cannot be previewed on mobile terminals.

Report Design

1. Create a database query dataset ds1 with the SQL statement SELECT * FROM Sales.

2. Design the data entry table, and drag the dataset fields into the corresponding cells. The following figure shows the table design.

3. Insert the formula SEQ() into cell A3 and set B3 as the left parent cell, as shown in the following figure.

Note:

You must set B3 as the left parent cell of cell A3, because only in this way can the serial numbers expand when new data is added.

4. Add a button widget for cell G3, select Insert Row from the drop-down list of Button Type, and set Specified Cell to B3, as shown in the following figure.

5. Add a button widget for cell H3, select Delete Row from the drop-down list of Button Type, and set Specified Cell to B3, as shown in the following figure.

6. Select cell A3, choose Cell Element > Others, and select Original in Row Insert Policy, as shown in the following figure.

Loading End Event Setting

1. Choose Template > Web Attribute > Data Entry Setting on the menu bar, select Set for This Template Separately in Follow Settings, and add a loading end event in Event Setting, as shown in the following figure.

The JavaScript code is as follows:

var $tds = $('.x-table td[col=0][fm="SEQ()"]:visible');
//Get the cells with visible serial numbers in the first column (namely column A)
var n = 0;
$tds.each(function(i, e) {
n = n + 1;
$(e).text(n);
//Reconfigure the current serial numbers.
})

The core statement in the JavaScript is var $tds = $('.x-table td[col=0][fm="SEQ()"]:visible'). The following explains the statement.

col=0: obtains the first column (namely, column A).

fm="SEQ()": obtains the cell that contains the SEQ() formula. (The case of SEQ() in the JavaScript must match that in the formula of the template.)

visible: obtains visible cells (excluding hidden rows).

Effect Display

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

Template Download

For details, you can download the template Renumbering After Inserting or Deleting Rows.cpt.

Notes

In the solution mentioned above, the serial numbers displayed after inserting or deleting rows are display values. These numbers are only for display on the page and cannot be submitted to the database. If you want to submit these serial numbers to the database, you need to assign actual values to the corresponding cells. Specifically, you need to replace the JavaScript code in the loading end event with the following code.

var $tds = $('.x-table td[col=0][idx=0]:visible:not([fmt="T"])');
//Get the cells with visible serial numbers in the first column (namely column A).
var n = 0;
$tds.each(function(i, e) {
n = n + 1;
var row = $(e).attr("row");
//Get the row numbers.
_g().setCellValue(0, 0, i,"FanRuan");
//Reconfigure the current serial numbers.
})

The core statement in JavaScript is $('.x-table td[col=0][idx=0]:visible:not([fmt="T"])'). The following explains the statement.

col=0: obtains the first column (namely, column A).

idx=0: excludes interferences below the cells with serial numbers. This is a fixed statement that does not need modification.

not([fmt="T"]): excludes header elements above the cells with serial numbers. This is a fixed statement that does not need modification.

visible: obtains visible cells (excluding hidden rows).

Note:

If you assign actual values to cells with serial numbers using this solution, page lag may appear when the data amount is relatively large. Therefore, you need to consider whether to use this solution according to your actual needs.

Not Ranking the Additional Content Below the Template

Problem

Additional unrelated content exists below the data entry template. If you click Insert Row, extra fields (generated due to the extension of serial numbers) appear below the serial numbers in the ID column.

Solution

1. The Extra field will change accordingly due to the parent-child cell relationship in the template.

2. Therefore, you need to modify the JavaScript code of the loading end event. The following shows the modified JavaScript code.

Note:

The following code only takes effect for the rows inserted. If you want to renumber the existing rows after deleting the rows, you need to modify the code according to your actual needs.

var $tds = $('.x-table td[col=0][fm="SEQ()"]:visible');
//Get the cells with visible serial numbers in the first column (namely column A).
var n = 0;
$tds.each(function(i, e) {
if(4>i>=2){
n = n + 1;
$(e).text(n);//Reconfigure the current serial numbers.
}
})
window.deleteflag=0;
window.appendflag=0;

3. The following figure shows the data entry preview effect after you save the template.

Not Ranking the Additional Content Above and Below the Template

Problem

Additional unrelated content exists above and below the data entry template. If you click Insert Row, other content will be adjusted according to the serial numbers.

Solution

1. You need to modify the JavaScript code of the loading end event. The following shows the modified JavaScript code.

Note:

The following code only takes effect for the rows inserted. If you want to renumber the existing rows after deleting the rows, you need to modify the code according to your actual needs.

if(window.deleteflag==1||window.appendflag==1){//Renumber the existing rows if you insert rows.
    var $tds=$('.x-table td[col=1]:visible:not(:empty)');//Determine whether the cells are empty.
   var n=0;
   $tds.each(function(i,e){
   if(i>=2){//Determine from which visible row in the template the numbering should start. Since row numbers start from zero, row numbers need to be subtracted by one. For example, the template starts from the third row, which corresponds to two.           
          n=n+1;
          $(e).text(n);//Reconfigure the current serial numbers.
         }
   });
   window.deleteflag=0;//Clear the row deletion marker.
   window.appendflag=0;//Clear the row insertion marker.
 }

2. After you save the template, click Data Entry Preview. The following figure shows the preview effect.