Validation Lists

Consider a database with the following tables:

In most cases, it's beneficial to use value lists for accounts, items, and companies when editing fields like account_id, item_id, and company_id, rather than using integer foreign key values.

We refer to these as "validation lists," a term borrowed from Microsoft Excel.

You can configure validation lists using the ValidationList event type in the xls.handlers table.

Example Configuration

Here's an example of how to set up validation lists:

SaveToDB 8+, DBEdit, DBGate, and ODataDB automatically create these validation lists for tables, views, and stored procedures based on foreign key relations and object definitions.

To disable automatic validation lists for an object, you can add manual configuration or set the DoNotAddValidation event type.

You can configure validation lists by copying the automatic configuration from the workbook information in the SaveToDB add-in, pasting it into the xls.handlers table, and then editing it.

Data Loading and Conversion

SaveToDB products load the source data, fetch data for configured value lists, and convert foreign key values from the first column of the list to the corresponding values in the second column.

When a user saves data changes or executes change handlers, the products convert table values back to foreign key values. This conversion is a fast operation, so you don't need to worry about it.

However, loading validation list values may take additional time. SaveToDB and DBEdit load validation lists during the initial connection and when a user clicks Reload Data and Configuration or Reload Validation Lists. They do not reload validation lists during regular data reloads.

In SaveToDB 10, you can reload validation lists using dependencies between objects. See Dependent Lists for more details.

List Structure

The example above uses lists with two columns, such as id and name. You can also use lists with a single column; in this case, the products create validation lists without any conversion.

You can define fixed values as well. For more information, see Value Lists with Fixed Values.

Additionally, you can create validation lists that depend on query parameters or values in row cells. Refer to Filtered vs. Dynamic Lists for more details.

Further Reading

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

• Value Lists Using Tables and Views
• Value Lists Using Stored Procedures
• Value Lists Using SQL

For creating validation lists using the REST API, see these topics:

• Value Lists with REST API
• Value Lists with OData
• Value Lists with ODataDB
• Value Lists with DBGate