[Solved] 2 Master Grids + 1 Junction Grid in 1 Form
Posted: Wed Feb 01, 2017 11:16 pm
Hey guys.
I think I have some kind of special question.
My current configuration is like this:
There's one master data table which is called "employee" consisting of employeeID, surname, forename, gender, etc. Another master data table is called "seminar" with seminarID, name, place, date, etc.
And, of course, I have created a junction table "seminar_employee" which consists of employeeID and seminarID but no further columns.
My goal is to have one form to maintain the following:
Create and administer multiple seminars (as a grid) and assigning multiple employees to these seminars.
Since the employee table contains a lot of employees I don't think that listbox with dropdown is a user-friendly option.
That's why I've added a 2nd grid which currently contains all employees (of the employee table, connected with the seminar grid via master/slave ID properties) and also a 3rd grid which shows all already assigned employees to the selected seminar (currently not connected to any grid at all).
And now here comes the tricky part. I added two buttons for adding and deleting an employee to/from the junction table grid via BASIC functions.
As in terms of usability, I want to achieve that as soon as an employee is added to the selected seminar, the employee entry should disappear from the "global" employee list (3rd grid of the form) because a single employee can only be assigned once to a seminar.
I tested already several approches esp. regarding
So my question is:
Is it possible to use the master/slave form-subform linking to get all records of the employee master data table except the ones already assigned to the ID of the selected seminar?
Or any other approach?
Thanks in advance!
Edit: Please find the below tables as an example:
Let's assume the following cases:
I think I have some kind of special question.
My current configuration is like this:
There's one master data table which is called "employee" consisting of employeeID, surname, forename, gender, etc. Another master data table is called "seminar" with seminarID, name, place, date, etc.
And, of course, I have created a junction table "seminar_employee" which consists of employeeID and seminarID but no further columns.
My goal is to have one form to maintain the following:
Create and administer multiple seminars (as a grid) and assigning multiple employees to these seminars.
Since the employee table contains a lot of employees I don't think that listbox with dropdown is a user-friendly option.
That's why I've added a 2nd grid which currently contains all employees (of the employee table, connected with the seminar grid via master/slave ID properties) and also a 3rd grid which shows all already assigned employees to the selected seminar (currently not connected to any grid at all).
And now here comes the tricky part. I added two buttons for adding and deleting an employee to/from the junction table grid via BASIC functions.
As in terms of usability, I want to achieve that as soon as an employee is added to the selected seminar, the employee entry should disappear from the "global" employee list (3rd grid of the form) because a single employee can only be assigned once to a seminar.
I tested already several approches esp. regarding
- changing the position of the 3rd "global" employee list in Form Navigator (top-level, same level as junction table grid, level under junction table grid)
- creating views for each grid and experimenting with INNER JOIN and LEFT JOIN as well as NOT EXISTS in WHERE clause
So my question is:
Is it possible to use the master/slave form-subform linking to get all records of the employee master data table except the ones already assigned to the ID of the selected seminar?
Or any other approach?
Thanks in advance!
Edit: Please find the below tables as an example:
Code: Select all
Seminar Table
+------------+---------------------------+------------+
| seminarID | name | ... |
+------------+---------------------------+------------+
| 1 | OpenOffice Seminar | ... |
| 2 | BASIC Seminar | ... |
| 3 | Apache Seminar | ... |
+------------+---------------------------+------------+
Code: Select all
Employee Table
+------------+--------------+------------+----------+
| employeeID | surname | forename | ... |
+------------+--------------+------------+----------+
| 1 | Doe | John | ... |
| 2 | Holmes | Sherlock | ... |
| 3 | Stark | Tony | ... |
+------------+--------------+------------+----------+
Code: Select all
Junction Table
+------------+------------+
| seminarID | employeeID |
+------------+------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
+------------+------------+
- seminarID = 1 selected in seminar grid: "assigned" employee grid should display John Doe and Tony Stark; "global" employee grid should only display Sherlock Holmes
- seminarID = 2 selected in seminar grid: "assigned" employee grid should display Sherlock Holmes and Tony Stark; "global" employee grid should only display John Doe
- seminarID = 3 selected in seminar grid: "assigned" employee grid should be blank; "global" employee grid should display John Doe, Sherlock Holmes and Tony Stark