Validation Lists

Validation Lists

Suppose a database has the following tables:

Database Diagram of Sample 02 - Advanced Features

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:

 s02cashbookaccount_idValidationLists02accountsTABLEid, +name
 s02cashbookitem_idValidationLists02itemsTABLEid, +name
 s02cashbookcompany_idValidationLists02companiesTABLEid, +name

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:

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