Parameter Values

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:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEET
 s02usp_cashbookaccount_idParameterValuess02accountsTABLEid, +name
 s02usp_cashbookitem_idParameterValuess02itemsTABLEid, +name
 s02usp_cashbookcompany_idParameterValuess02companiesTABLEid, +name

Here is a sample of the configuration with the values from stored procedures:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEET
 s02usp_cashbook2account_idParameterValuess02xl_list_account_idPROCEDURE
 s02usp_cashbook2item_idParameterValuess02xl_list_item_idPROCEDURE
 s02usp_cashbook2company_idParameterValuess02xl_list_company_idPROCEDURE

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:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEET
 s17usp_requestcategory_idDefaultValueATTRIBUTE1_NotNull
 s17usp_requesttime_idDefaultValueATTRIBUTE2

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.