Multiselect Lookups/Dropdowns
When dbFront sees a Many to Many (M-M) relationship it will add a "(M-M)" field to the list of available form fields.
This "(M-M)" field can be added to the form just like any other field. Users can then select multiple items and when the form is saved, dbFront will automatically create or delete the appropriate M-M records.
How Multiselect Lookups Work
On the surface, the difference between a simple and multi-select lookup seems minor but the difference is quite significant. With a simple lookup, you only store a single data point but with a multi-select, you need to store a list of data points. Therefore a multi-select requires a separate table to store all of the connections between your current table and the lookup table. This connecting table is called an associate table.
A Many to Many (M-M) relationship is constructed from two One to Many (1-M) relationships arranged as (1-M = M-1). The two M's are implemented using a single connecting table called the associate table. This central associate table records the Many connections between the two outer tables. To add a connection you would add a record to the associate table, to delete a connection you would delete the record.
In summary, a multi-select field hides (or wraps up) what is implemented as two database relationships and an associate table. A multi-select field allows you to easily add or remove associations, and once you click save, dbFront will add or remove the appropriate database records.
The Associate Table
The primary key on the associate table is usually made up of the two foreign keys. A primary key which only includes the two foreign keys allows the DBA to prevent duplicate relationships between the two sides of the M-M relationship. For example, a box of Oranges could have multiple flags (Clearance, Sale, etc..) but it could not be flagged with the same flag twice.
When a Many to Many relationship is present, dbFront will also show the associate table as a regular child table in one of the tabs below the main table. If the associate table is simple then you may want to hide it since it provides no extra value. A simple associate table will consist of only the two foreign keys and have no additional attributes of its own. To hide an associate table you can edit the relationship visibility from the table properties.
Customizing the Multi-Select
You can click on the gear icon on the multi-select lookup column to adjust the preferences such as:
- customizing the columns that are displayed in the dropdown,
- adjusting the sort order,
- filtering the rows in the dropdown.
If you currently don't have a multi-select lookup table then dbFront has a Table Tool available that will help you to create a lookup table. The tool will generate the SQL you need to migrate one or more columns and all their data to a newly created lookup table. The Table Tool is found on the last tab of the table preferences.
NOTE: The SQL is only generated as an aid, please validate before running.
For more information see: