Validation Lists

Suppose a database has the following tables:

It's a good idea, in most cases, to suggest value lists of accounts, items, and companies for editing fields like account_id, item_id, and company_id instead of integer foreign key values.

We use the "validation list" term that comes from Microsoft Excel.

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

For example:

SaveToDB 8+, DBEdit, DBGate, and ODataDB create such validation lists automatically for tables, views, and stored procedures using the foreign key relations and object definitions.

You can disable automatic validation lists for an object adding any manual configuration or setting the DoNotAddValidation event type.

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

Note that SaveToDB products load the source data, load data for configured value lists, and change foreign key values from the list first column to values from the list second column.

When a user saves data changes or executes change or other handlers, the products convert table values back to the foreign key values.

Converting values is a very fast operation. You can don't care about it.

However, loading validation list values can require additional time.

So, SaveToDB and DBEdit load validation 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 validation lists in a regular data reload.

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

The case above uses lists with two columns like id and name.

You can use lists with a single column also. In this case, the products create validation lists without any converting.

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

Also, you can use validation lists that depend on query parameters or values in the row cells. See Filtered vs. Dynamic Lists.

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

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

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

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