Parameter Values

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

Here is a sample of the configuration with the values 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

Here is a sample of the configuration with the 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

See the following topics to learn how to create value lists using database objects:

See the following topics to learn how to create value lists using REST API:

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

_NotNull and _KeepNull

Products add empty values to parameter value lists automatically.

Usually, the NULL value means "all values" if you use WHERE filters like

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

This feature was added in SaveToDB 7.31, 8.26, 9.12, and 10.0, DBEdit 2.0, DBGate 2.0, and ODataDB 4.0.

In the previous versions, stored procedures must select a NULL value to show empty values like

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

So, the new behavior is a breaking change as it adds an empty value to lists that haven't it before.

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

To show value lists as is, use the _KeepNull value in the TARGET_WORKSHEET column.

We recommend updating to the latest versions as it allows you to keep your code simpler.

Default Parameter Values

Parameters get initial values as the first item of the value list or NULL if the list does not exist.

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

For example:

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 turn off the empty value in the list. In this case, you may not specify a default value if the first list item fits.

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

Value List Parameters

Value list objects can have parameters.

The first case is selecting values in different languages. See Translating Data.

The second case is selecting values depending on the query parameters before the declared parameter.

For example, if your query contains parameters like @country_id and @state_id, then the query for state values can use the @country_id parameter.

In this case, when a user changes the @country_id parameter, the state query is reloaded with a new value (before the main query).

Starting SaveToDB 8.26, 9.12, and 10.0, DBEdit 2.0, DBGate 2.0, ODataDB 4.0, products support dynamic lists.

I.e., the query for state values can return id, state, and country_id (as the third column), and the list will be filtered dynamically depending on the @country_id value.

See Filtered vs. Dynamic Lists for details.

Reloading Value Lists

SaveToDB and DBEdit load parameter value lists in the first connection and when a user clicks Reload Data and Configuration or Reload Validation Lists menu items.

SaveToDB and DBEdit do not reload value lists in a regular Reload action.

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