Working with Foreign Keys

Working with Foreign Keys

Starting SaveToDB 6.5, you can implement complex Excel forms based on tables, views, and stored procedures with fewer efforts as the add-in solves all tasks of converting between foreign key values and their names.

For example, a database contains the following tables and relations:

Example database schema: tables and relations

The dbo38.Payment table contains foreign keys from the dbo38.Account, dbo38.Company, and dbo38.Item tables.

To create a form for the dbo38.Payment table, we just add the ValidationList handlers to the dbo01.EventHandlers table as shown below:

Validation list configuration

As a result, we have the following table in Excel:

Excel table with validation lists

The add-in has replaced the integer key values to names in Excel and has added validation lists that allow choosing values from the tables of foreign keys.

You can apply this technique also to views and stored procedures. See examples in the Developer Guide 6.5 for SQL Server in the SaveToDB SDK.

When a user selects a cell with a validation list, the add-in shows the List Editor like this:

List editor

The List Editor allows selecting values more conveniently. Just double-click on a value or press Enter on a selected value.

You can also filter values. Just type several characters from the desired name.

If you have closed the editor, you can reopen it using Options, Show List Editor Task Pane.