JS realizes automatic generation of serial numbers and sorting

1. Problem description

When filling in data, it is hoped that the serial number can be automatically generated, and the serial number will not be disconnected when adding or deleting data rows. The effect is shown in the following figure:

2. Implementation ideas

Use the SEQ() function to sort. Since inserting and deleting rows will trigger the loading end event of the page, in the loading end event, the front-end JS interface can be used to realize the effect of recalculating the sequence number after each insertion and deletion of rows.

Note: mobile preview and new data entry preview are not supported.

1. Design report

1) Create a new database query dataset ds1

 The SQL statement is: SELECT * FROM Sales

2) Design the filling form

Drag the dataset field into the corresponding cell, the form design is as shown in the figure below:

3) Insert the formula SEQ() into cell A3

 Set the left parent cell to B3, as shown in the figure below:

4) Add a Button widget to the G3 cell

Select the button type to Insert Row, and Specified CellB3

5) Add a Button widget to the H3 cell

Select the button type toDelete Row, and Specified CellB3

6) Select cell A3

Set Cell Attributes>Other>Insert Row Policy to the Original

2. Set loading end event

In the menu bar, click Template> Web Attributes > Data Entry Settings, choose Individually set for the template, and add the loading End event,as shown in the following figure:

The JavaScript code is as follows:

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

The core statement in this JS is $('.x-table td[col=0][fm="SEQ()"]:visible'), the description is as follows:

col=0: Get the first column (column A)

fm="SEQ()": Get the cell where the seq() formula is written (the case of SEQ() in JS must be consistent with the formula in the template)

visible: Get visible cells (excluding hidden rows)

3. Effect preview

Save the report, click Data Entry Preview, the effect is as shown in the figure below:

Click to download the template:JS_realizes_automatic_generation_of_serial_numbers_and_sorting.cpt

The serial number displayed in the above scheme after inserting and deleting rows is the displayed value, and cannot be submitted to the dataset, and is only used as a page display effect. If you want to submit the serial number to the warehouse at the same time, you need to assign the actual value to the corresponding cell. The specific solution is 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 serial number cell visible in column 1 (column A)
var n = 0;
$tds.each(function(i, e) {
	n = n + 1;
	var row = $(e).attr("row");
	//Get line number
	_g().setCellValue(0, 0, row, n);
	//Reset the current number
})

The core statement in this JS is $('.x-table td[col=0][idx=0]:visible:not([fmt="T"])'), the description is as follows:

col=0: Get the first column (column A)

idx=0: Eliminate the interfering elements below the serial number cell, the fixed wording does not need to be modified

not([fmt="T"]): exclude the title element above the serial number cell, the fixed wording does not need to be modified

visible: Get visible cells (excluding hidden rows)

Note: Using this scheme to assign actual values to the serial number cells will cause page jams when the amount of data is large, so you need to consider whether to use it according to the actual situation.