JS realizes data entry linkage and generate default value-FineReport Help Document

Last update： 2021-06-07

I. Overview

1. Problem description

We hope to realize that based on the data entry linkage, after selecting the first widget value, other linkage widgets can follow to select the first value in the selectable range. The effect is shown in the following figure:

2. Realization idea

When both the cell linkage and the widget data dictionary linkage are set, when the widget data dictionary linkage is triggered, the cell value will be cleared, causing the cell linkage to fail. So to achieve this requirement, you need to assign a value to the widget through JS.

Note: Mobile terminal is not supported.

II. Example

1. Design report

Add drop-down box widgets to cells B1, D1, and F1, as shown in the following figure:

2. Add data dictionary

Set the drop-down box data dictionary for the three widgets, select formula, enter the actual value and the display value, as shown in the following figure:

The three widgets differ only in actual value, the display value is $$$, and the actual values are:

B1：sql('FRDemo','SELECT Region FROM Sales_Volume',1)

D1：sql('FRDemo','SELECT Salesperson FROM Sales_Volume where Region="'+B1+'"',1)

F1：sql('FRDemo','SELECT Product_types FROM Sales_Volume where Region="'+B1+'" and Salesperson="'+D1+'"',1)

3. Set default value for widgets

If the ‘widget needs to add a default value, you can add a default value to the widget by entering a formula, as shown in the following figure:

The cell formulas are:

B1：sql('FRDemo','SELECT Region FROM Sales_Volume',1,1)

D1：sql('FRDemo','SELECT Salesperson FROM Sales_Volume where Region="'+B1+'"',1,1)

F1：sql('FRDemo','SELECT Product_types FROM Sales_Volume where Region="'+B1+'" and Salesperson="'+D1+'"',1,1)

4. Add events

Add Edit End events to B1, D1 drop-down box widgets(After Editing event can also be used) .

1) First, click Template>Template Parameters to define a parameter row, as shown in the figure below:

2) Add an edit end event to the B1 widget, set the parameter row to the formula row(), as shown in the figure below:

The JavaScript code in B1 is as follows:

var region = this.getValue();
setTimeout(function() {
	var salesperson;
	var ptypes;
	var sql = 'SELECT Salesperson FROM Sales_Volume where Region="' + region + '"';
	salesperson = FR.remoteEvaluate("=sql('FRDemo','" + sql + "',1,1)");
	_g().setCellValue('D' + row, null, salesperson);
	var sql1 = 'SELECT Product_types FROM Sales_Volume where Region="' + region + '" and Salesperson="' + salesperson + '"';
	ptypes = FR.remoteEvaluate("=sql('FRDemo','" + sql1 + "',1,1)");
	_g().setCellValue('F' + row, null, ptypes);
}, 200);

2) Similarly, add an edit end event to the D1 widget, and set the parameter row to the formula row().

The JavaScript code in D1 is as follows:

var salesperson = this.getValue();
var ptypes;
setTimeout(function() {
	var region = _g().getCellValue('B' + row, null);
	var sql = 'SELECT Product_types FROM Sales_Volume where Region="' + region + '" and Salesperson="' + salesperson + '"';
	ptypes = FR.remoteEvaluate("=sql('FRDemo','" + sql + "',1,1)");
	_g().setCellValue('F' + row, null, ptypes);
}, 300);

5. Set data entry settings

In the designer menu bar, click Template>Web Attributes, select data entry settings, and check the "Display Widgets Directly", as shown in the following figure:

6. Turn off script call formula restriction

Because some formulas are called by the script in the example, you need to open the decision-making platform, click Manage> Security, and turn off "Script call formula restriction", as shown in the following figure: