Value Lists

Value Lists

DBEdit allows using user-friendly values instead of identifiers in tables and UI controls.

For example, a database contains the following tables:

For example, the database contains the following view:

CREATE VIEW s02.view_cashbook
AS
SELECT
    *
FROM
    s02.cashbook

This view looks in DBEdit like this:

In this sample, DBEdit loads values from the referenced tables and uses the id column as a value member and the name column as a display member.

DBEdit does this automatically if the following conditions are met:

  1. A field must be a foreign key.
  2. The parent table must have at least two fields.
  3. The parent table must have a unique key or an index at least for one additional field except for the primary key column.
  4. A user must have permissions to read foreign key metadata.
  5. A user must have permissions to read an SQL definition of a view or stored procedure.

Developers may configure this feature in other cases using the ValidationList and ParameterValues handlers or disable it using the DoNotAddValidation handler.

Note that if you configured a handler for any column, DBEdit disables automatic lists for other columns.

To configure handlers, use the xls.handlers table of the SaveToDB Framework, or create own table or view with the same column structure.

ValidationList Handlers

Use the ValidationList handlers to configure value lists for table cells.

DBEdit also uses such lists in UI controls for WHERE fields of tables and views.

This sample shows various ways to configure lists using tables, views, stored procedures, or SQL codes:

See details in the xls.handlers table description.

ParameterValues Handlers

Use the ParameterValues handlers to configure value lists for parameters of stored procedures, SQL-based objects, and even tables or views.

This sample shows various ways to configure lists using tables, views, stored procedures, or SQL codes:

See details in the xls.handlers table description.

Handler Output

Value list handlers may return one, two, or three columns.

Three-Column Output

Handlers must return columns in the following order: value, display, and filter members.

For example, we have a table with the columns: id, item_id, and company_id.

The ValidationList handler of the company_id column returns a list with the columns: id, name, item_id.

In this case, DBEdit shows the name column values in the company_id column and filters values using a value of the item_id table column.

The first and second columns of the returned list can have any name. The third column must have a name of the table column used as a filter.

Two-Column Output

There are two cases possible:

  1. The first column is a value member, and the second one is a display member.
  2. The first column is a value member, and the second one is a filter member.

DBEdit uses the second column as a filter if the table contains a column with the same name before the validated column.

For example, a table has columns: id, country, and state. A list of the state column returns two columns: code and country.

In this case, as the country column exists before the state column, DBEdit uses the country column as a filter.

In exclusion, DBEdit never uses columns named as "id" and "name" as filters.

Single-Column Output

DBEdit uses a single column as a value member.

Filtered Lists vs. Dynamic Lists

As discussed above, the value lists may have a filter column.

In this case, DBEdit loads from a database the entire list and filters its values in-place.

Alternatively, the handler objects may filter lists on a server using query parameters.

For example, a query has the item_id and company_id parameters with their lists.

A handler of the company_id list may have the parameter @item_id.

In this case, DBEdit reloads the company_id list on every change of the item_id.

This technique reduces the loaded list size and allows using multiple filters.

Empty Values

DBEdit adds a blank value (NULL) to value lists in most cases automatically.

However, in several cases, you need to add it in your codes.

For example, you may add the following line for a two-column list:

SELECT NULL AS id, NULL AS name UNION ALL

Specific Features by Handler Types

DBEdit allows using the following handler types in the HANDLER_TYPE column of the handler configuration table:

  • TABLE, VIEW, FUNCTION, PROCEDURE
  • CODE
  • VALUES

Tables and Views

As discussed above, the value list handlers may return one, two, or three columns.
Also, the name of the list filter column is important. It must be equal to the table filter column.

As a first solution, you may specify the CODE type and use the SELECT SQL command to select the required list.

As a second solution, you may use a DBEdit (and SaveToDB) specific syntax in the HANDLER_CODE field.

You may specify a comma-separated field name list including AS aliases with the following field name prefixes:

+   - ORDER BY ASC
-   - ORDER BY DESC
@   - WHERE

For example, a simple case:

id, state

Below is a case with the sorted state column and a filter column that uses the alias:

id, +state, country_id AS county

Below is a case with a parameter that implements a dynamic list:

id, +state, @country_id AS country

DBEdit generates the following SQL code for this sample:

SELECT [id], [state] FROM ... WHERE [country_id] = @country ORDER BY [state]

Functions and Stored Procedures

Database functions and stored procedures allow implementing any logic.

Note that you may use the @DataLanguage and @data_language context parameters to return the values in a user language.

SQL-Based Objects

You may use SQL queries to return value lists easily.

Just specify the SQL query code in the HANDLER_CODE field.

The SQL code can include parameters in the following forms:

  • @<ParameterName> for Microsoft SQL Server, Microsoft SQL Server Compact, and SQLite
  • :<ParameterName> for Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, Snowflake, and PostgreSQL

For example:

SELECT id, state FROM dbo.states WHERE country_id = @country_id ORDER BY state

In this example, DBEdit reloads the value list when a user changes the @country_id parameter.

Values

You may specify a fixed value list separated by commas or semicolons in the HANDLER_CODE field.

Add an empty value in the first position, if you need to have it in the list.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.