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:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE | TARGET_WORKSHEET |
---|---|---|---|---|---|---|---|---|---|
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:
ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE | TARGET_WORKSHEET |
---|---|---|---|---|---|---|---|---|---|
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.