Automatic Default Value Through Data Entry Linkage by JS

Problem

You want the first available value within the selectable ranges of linked other widgets to be selected automatically when selecting a value in the first widget, which is based on data entry linkage.

Implementation Method

When both value linkage and data dictionary linkage between cell widgets are configured, cell values will be cleared after the data dictionary linkage is triggered, invalidating the value linkage. Therefore, to implement both value linkage and data dictionary linkage simultaneously, you need to assign values to cells via JavaScript. For details about APIs, see JS API Summary.

Note:

The effect cannot be previewed on mobile terminals.

Designing the Table

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

Setting Data Dictionary Linkage for Widgets

Configure Data Dictionary for the three drop-down box widgets respectively: Set Type to Formula, and set Actual Value and Display Value as required, as shown in the following figure.

For the three widgets, their display values are all $$$, but their actual values are different as follows:

B1: sql("FRDemo", "SELECT [Shipper's region] FROM Orders", 1)

D1: sql("FRDemo", "SELECT [Shipper's country] FROM Orders WHERE [Shipper's region]='" + B1 + "'", 1)

F1: sql("FRDemo", "SELECT [Shipper's city] FROM Orders WHERE [Shipper's region]='" + B1 + "' and [Shipper's country]='" + D1 + "'", 1)

Setting Value Linkage for Widgets

1. Add an After Editing event to the drop-down box widget in cell B1.

The JavaScript codes are as follows:

Note:

The FR.remoteEvaluate API poses certain security risks. Use it as needed based on your situation. For details, see Global API - FR.

var row = FR.cellStr2ColumnRow(this.options.location).row + 1;
//Get the current row number.
var region = this.getValue();
//Get the current widget value.
var country = FR.remoteEvaluate('sql("FRDemo","select [Shipper\'s country] from Orders where [Shipper\'s region]=\'' + region + '\'",1,1)');
//Query the first country based on the selected region.
var city = FR.remoteEvaluate('sql("FRDemo","select [Shipper\'s city] from Orders where [Shipper\'s region]=\'' + region + '\' and [Shipper\'s country]=\'' + country + '\'",1,1)');
//Query the first city based on the selected region and the corresponding country.
setTimeout(function() 
	{_g().setCellValue('D' + row, null, country);
	//Assign the value to the cell in column D of the current row.
	}, 200)
setTimeout(function() {
	_g().setCellValue('F' + row, null, city);
	}, 800)
Show Code

2. Add an After Editing event to the drop-down box widget in cell D1.
The JavaScript codes are as follows:

var row = FR.cellStr2ColumnRow(this.options.location).row + 1;
//Get the current row number.
var region = _g().getCellValue('B' + row, null);
//Get the region value from column B of the current row.
var country = this.getValue();
//Get the current widget value.
var city = FR.remoteEvaluate('sql("FRDemo","select [Shipper\'s city] from Orders where [Shipper\'s region]=\'' + region + '\' and [Shipper\'s country]=\'' + country + '\'",1,1)');
//Query the first city based on the selected region and the corresponding country.
setTimeout(function()
	{_g().setCellValue('F' + row, null, city);
	//Assign the value to the cell in column F of the current row.
	}, 500)

Disabling the Restriction on Formulas in Scripts

Since this example requires JavaScript codes to call certain formulas, you need to log in to the decision-making system and disable  Forbid Script to Call Formula under System Management > Security Management, as shown in the following figure.

Effect Display

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

For details, you can download the template Automatic Default Value Through Data Entry Linkage by JS.cpt.