Many-to-many Relations
The SaveToDB add-in has a special mode for editing data in many-to-many relations.
Consider three tables: employees, territories, and employee_territories.
The employee_territories table contains two foreign key columns: employee_id and territory_id.
When you retrieve data from the employee_territories table without any filters applied on the ribbon, you get the following data:
| employee_id | territory_id | _State_ |
|---|---|---|
| Employee 1 | Territory 1 | ✓ |
| Employee 1 | Territory 2 | ✓ |
| Employee 2 | Territory 3 | ✓ |
The add-in adds the checkbox _State_ column, which you can use to add or remove relations.
You can create a pivot table using the Excel data table as a data source. Place the foreign key columns on the axes and the SUM of the _State_ column in the cells. You’ll see a table like this:
| Territory 1 | Territory 2 | Territory 3 | |
|---|---|---|---|
| Employee 1 | ✓ | ✓ | |
| Employee 2 | ✓ | ||
| Employee 3 |
The add-in also adds validation lists for foreign key columns as axis items.
You can double-click in the pivot table to modify the underlying table's data and save changes directly from the pivot worksheet!
Returning to the table, you can select a value on the ribbon. For example, if you select Territory 1, the table will look like this:
| employee_id | territory_id | _State_ |
|---|---|---|
| Employee 1 | Territory 1 | ✓ |
| Employee 2 | Territory 1 | x |
| Employee 3 | Territory 1 | x |
You can double-click on the _State_ column value to add or remove the relation.
If you select Employee 1, the table will appear as follows:
| employee_id | territory_id | _State_ |
|---|---|---|
| Employee 1 | Territory 1 | ✓ |
| Employee 1 | Territory 2 | ✓ |
| Employee 1 | Territory 3 | x |
To implement these features, the add-in automatically configures itself by reading the database metadata:
| ID | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | EVENT_NAME | HANDLER_SCHEMA | HANDLER_NAME | HANDLER_TYPE | HANDLER_CODE |
|---|---|---|---|---|---|---|---|---|
| s07 | employee_territories | employee_id | ValidationList | s07 | employees | TABLE | [employee_id],[last_name] | |
| s07 | employee_territories | territory_id | ValidationList | s07 | territories | TABLE | [territory_id],[territory_description] | |
| s07 | employee_territories | employee_id | ManyToMany | ATTRIBUTE | ||||
| s07 | employee_territories | territory_id | ManyToMany | ATTRIBUTE | ||||
| s07 | employee_territories | AddStateColumn | ATTRIBUTE |
The add-in uses the ManyToMany event type for foreign key columns and the AddStateColumn event type to add the _State_ column. It also creates validation lists based on the primary key tables.
You can create similar configurations for other objects, such as loading data from stored procedures or views.
If you want to disable automatic configurations, use the DoNotAddManyToMany event type in the xls.handlers table.