Parameter Values

You can configure value lists for parameters using the ParameterValues event type in the xls.handlers table.

Configuration Samples

Using Values from Tables

Here's a sample configuration with values sourced from tables:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
s02	usp_cashbook	account_id	ParameterValues	s02	accounts	TABLE	id, +name
s02	usp_cashbook	item_id	ParameterValues	s02	items	TABLE	id, +name
s02	usp_cashbook	company_id	ParameterValues	s02	companies	TABLE	id, +name

Using Values from Stored Procedures

Here's a sample configuration with values from stored procedures:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE
s02	usp_cashbook2	account_id	ParameterValues	s02	xl_list_account_id	PROCEDURE
s02	usp_cashbook2	item_id	ParameterValues	s02	xl_list_item_id	PROCEDURE
s02	usp_cashbook2	company_id	ParameterValues	s02	xl_list_company_id	PROCEDURE

Creating Value Lists

To learn how to create value lists using database objects, check out the following topics:

For creating value lists using the REST API, refer to these topics:

You can also define fixed values. See Value Lists with Fixed Values.

_NotNull and _KeepNull

Products automatically add empty values to parameter value lists.

Typically, a NULL value signifies "all values" when using WHERE filters like:

WHERE
    c.company_id = COALESCE(@company_id, c.company_id)

This feature was introduced in SaveToDB versions 7.31, 8.26, 9.12, and 10.0, as well as DBEdit 2.0, DBGate 2.0, and ODataDB 4.0.

In earlier versions, stored procedures needed to select a NULL value to display empty values, like so:

SELECT NULL AS id, NULL AS name UNION ALL ...

This new behavior is a breaking change, as it introduces empty values to lists that previously did not have them.

To prevent adding empty values, specify the _NotNull value in the TARGET_WORKSHEET column.

To display value lists as they are, use the _KeepNull value in the TARGET_WORKSHEET column.

We recommend updating to the latest versions to simplify your code.

Default Parameter Values

Parameters receive initial values as the first item in the value list or NULL if the list doesn't exist.

You can set the default parameter value using the DefaultValue event type in the xls.handlers table.

Example Configuration

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE	TARGET_WORKSHEET
	s17	usp_request	category_id	DefaultValue			ATTRIBUTE	1	_NotNull
	s17	usp_request	time_id	DefaultValue			ATTRIBUTE	2

You can set the _NotNull value in the TARGET_WORKSHEET field to disable empty values in the list. In this case, you may skip specifying a default value if the first list item is suitable.

Also, note that JSON forms can return parameter values from the query.

Value List Parameters

Value list objects can have parameters.

Use Cases

Selecting Values in Different Languages: See Translating Data.
Dynamic Value Selection: If your query includes parameters like @country_id and @state_id, the query for state values can utilize the @country_id parameter. When a user changes the @country_id parameter, the state query reloads with the new value (before the main query).

Starting with SaveToDB versions 8.26, 9.12, and 10.0, as well as DBEdit 2.0, DBGate 2.0, and ODataDB 4.0, products support dynamic lists. For instance, the query for state values can return id, state, and country_id (as the third column), allowing the list to filter dynamically based on the @country_id value.

For more details, see Filtered vs. Dynamic Lists.

Reloading Value Lists

SaveToDB and DBEdit load parameter value lists during the initial connection and when a user clicks Reload Data and Configuration or Reload Validation Lists.

They do not reload value lists during a standard Reload action.

Starting with SaveToDB 10, you can reload validation lists and parameter values using dependencies between objects. See Dependent Lists.