Many-to-many Relations

Many-to-many Relations

The SaveToDB add-in has a special mode to edit data of many-to-many relations.

For example, you have three tables: employees, territories, and employee_territories.

The employee_territories table contains two columns of foreign keys: employee_id and territory_id.

When you get data from the employee_territories table without filtered values on the ribbon, you get actual data like

employee_idterritory_id_State_
Employee 1Territory 1✓
Employee 1Territory 2✓
Employee 2Territory 3✓

The add-in adds the checkbox _State_ column used to add or delete relations.

You can create a pivot table using the Excel data table as a data source.

Place the foreign key columns to axes and the SUM of the _State_ column to cells.

You will see a table like

Territory 1Territory 2Territory 3
Employee 1✓✓
Employee 2✓
Employee 3

The add-in adds validation lists of foreign key columns as axis items.

Moreover, you can double-click in the pivot table to change the data of the underlying table and save changes from the pivot worksheet!

Returning to the table, you can select a value on the ribbon.

For example, if you select Territory 1, you will have the following table:

employee_idterritory_id_State_
Employee 1Territory 1✓
Employee 2Territory 1x
Employee 3Territory 1x

So, you can double-click on the _State_ column value to add or delete the relation.

If you select Employee 1, you will have a table like this

employee_idterritory_id_State_
Employee 1Territory 1✓
Employee 1Territory 2✓
Employee 1Territory 3x

To implement these features, the add-in creates the following automatic configuration, reading the database metadata:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s07employee_territoriesemployee_idValidationLists07employeesTABLE[employee_id],[last_name]
 s07employee_territoriesterritory_idValidationLists07territoriesTABLE[territory_id],[territory_description]
 s07employee_territoriesemployee_idManyToManyATTRIBUTE
 s07employee_territoriesterritory_idManyToManyATTRIBUTE
 s07employee_territoriesAddStateColumnATTRIBUTE

It uses the ManyToMany event types for foreign key columns.

It uses the AddStateColumn event type to add the _State_ column.

Also, it creates validation lists using the primary key tables.

You can create such configuration for other objects too. For example, you can load data from stored procedures or views.

You can also disable adding automatic configurations using the DoNotAddManyToMany event type in the xls.handlers table.