[Solved] 2 Master Grids + 1 Junction Grid in 1 Form

Creating and using forms

[Solved] 2 Master Grids + 1 Junction Grid in 1 Form

Postby Ferineum » 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
  • 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
but the only two results I've achieved so far are either a blank "global" employee grid with no changes after adding/deleting or an identical grid as the junction table grid.

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   Expand viewCollapse view
Seminar Table
+------------+---------------------------+------------+
| seminarID  |          name             |     ...    |
+------------+---------------------------+------------+
|     1      |    OpenOffice Seminar     |     ...    |
|     2      |    BASIC Seminar          |     ...    |
|     3      |    Apache Seminar         |     ...    |
+------------+---------------------------+------------+


Code: Select all   Expand viewCollapse view
Employee Table
+------------+--------------+------------+----------+
| employeeID |    surname   |  forename  |    ...   |
+------------+--------------+------------+----------+
|     1      |    Doe       |   John     |    ...   |
|     2      |    Holmes    |   Sherlock |    ...   |
|     3      |    Stark     |   Tony     |    ...   |
+------------+--------------+------------+----------+


Code: Select all   Expand viewCollapse view
Junction Table
+------------+------------+
| seminarID  | employeeID |
+------------+------------+
|     1      |     1      |
|     1      |     3      |
|     2      |     2      |
|     2      |     3      |
+------------+------------+


Let's assume the following cases:
  • 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
If an employee gets added/deleted of a seminar, the 2nd and 3rd grid will automatically refresh to display the current assignments.
Last edited by Ferineum on Sun Feb 05, 2017 8:31 pm, edited 3 times in total.
OpenOffice 4.1.3 @ Windows 7
Ferineum
 
Posts: 3
Joined: Wed Feb 01, 2017 10:51 pm

Re: 2 Master Table Grids + 1 Junction Table Grid in 1 Form

Postby chrisb » Thu Feb 02, 2017 11:36 am

hello Ferineum,

the only way to answer a topic like this is with a working example (too many possible questions & answers).
download the attachment.
i will give a brief guide to using the form.

Select Seminar (mainform):
it has a list box from which a seminar is selected.
the ID value of seminar is saved to a filter table why? because we need a stored value to enable the elimination of all employees assigned to the current seminar from the list of employees in our second form.
we need a save button because this form has 2 subforms. after selecting a seminar hit 'SAVE' & then hit 'REFRESH' to update the subforms.
if 'SAVE' is not greyed out & you hit 'REFRESH' then base will throw an error message.

Select Employee (subform):
a list box without drop down lists all employees who are not assigned to the currently selected seminar (smart query 'qLB_EmployeeFilteredBySeminar').
select an employee & hit refresh to save.
if you decide against saving hit the 'CLEAR' button.

Employees On Selected Seminar Read Only (subform):
a list box in a table grid is used to display the data. all employees assigned to the currently selected seminar are shown.
this form is read only but employees may be removed from the seminar by hitting the 'DELETE' button. when your done deleting make sure to hit the 'REFRESH' button in order to update the subform 'Select Employee'.

check out the form structure & try to understand how our smart query 'qLB_EmployeeFilteredBySeminar' works.
i made this demo before seeing your edit! i hope it works OK but have not had the time to be certain. only data pertaining to seminars 1 & 2 was input.
the use of macros in forms of this type can greatly enhance the user experience.
Seminar.odb
(14.76 KiB) Downloaded 60 times
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 149
Joined: Mon Jun 07, 2010 4:16 pm

Re: 2 Master Table Grids + 1 Junction Table Grid in 1 Form

Postby Ferineum » Thu Feb 02, 2017 4:24 pm

Hi chris,

thank you for providing this working example.

I had a look at it and understood the main functionality of your qLB_EmployeeFilteredBySeminar query.
The idea with continuously updating the temporary filter table is very good. I would have never thought about such an approach. :shock:

2 questions so far:
  1. Do you think that it is somehow possible that the selection of a seminar in the "Add-Edit-Delete-Seminar" grid would update the "Select Employee" and "Employees On Selected Seminar Read Only" grid automatically? Maybe with the use of macro(s)? Just to get rid of the "Select Seminar" dropdown listbox. Maybe by using the After record change grid event and updating directly the temporary filter table via the macro?
  2. You also mentioned that the use of macros can greatly enhance the user experience. Can you please share some ideas/advice or other input (some other examples maybe?) I would highly appreciate that!

I'm going to adapt the temporary table approach to my real database and doing some macro-testing :D

Thank you very much!
OpenOffice 4.1.3 @ Windows 7
Ferineum
 
Posts: 3
Joined: Wed Feb 01, 2017 10:51 pm

Re: 2 Master Table Grids + 1 Junction Table Grid in 1 Form

Postby chrisb » Thu Feb 02, 2017 9:59 pm

hello Ferineum,
Ferineum said
Do you think that it is somehow possible that the selection of a seminar in the "Add-Edit-Delete-Seminar" grid would update the "Select Employee" and "Employees On Selected Seminar Read Only" grid automatically?
the answer is no we require a list box.
the purpose of the form 'Add-Edit-Delete-Employee' is self explanatory, it enables the manipulation of data stored in the table 'tEmployee'.
likewise 'Add-Edit-Delete-Seminar' which permits the manipulation of data stored in the table 'tSeminar'.
these 2 forms are only included for the purpose of clarification i.e. they negate any possible doubts regarding accuracy/inclusion of data contained in this demo.
in the real world these forms would not be visible in the same window together with the other forms.
Ferineum said
You also mentioned that the use of macros can greatly enhance the user experience. Can you please share some ideas/advice or other input (some other examples maybe?) I would highly appreciate that!

if this was my database & it was used on a regular basis then i would incorporate the use of macros here:-
'Select Seminar':
when i hit the list box it should refresh (update the list). a generic macro may be used.
when i change the list box selection then the bound field should be saved & subforms updated. a bespoke macro will be required. the buttons 'REFRESH' & 'SAVE' can be dispensed with.

'Select Employee':
when i hit the 'REFRESH' button the currently selected values should be saved to the table 'tEmployee_ID_Seminar_ID', the list refreshed with cursor position retained & the forms 'Select Employee' & 'Employees On Selected Seminar Read Only' reloaded. a bespoke macro will be required.

'Employees On Selected Seminar Read Only':
when i hit 'DELETE' a message box will request confirmation & if approved remove the selected entry from the table 'tEmployee_ID_Seminar_ID'. this form & the form 'Select Employee' will be updated. the 'REFRESH' button can be dispensed with. a bespoke macro will be required.

so macros will allow the removal of 3 buttons & enhance the user experience by removing that clunky feeling, many examples are available through this forum.
if you think the benefits outweigh the effort then i suggest you do some research & if you get bogged down then ask specific questions in a new thread.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 149
Joined: Mon Jun 07, 2010 4:16 pm

Re: 2 Master Table Grids + 1 Junction Table Grid in 1 Form

Postby Ferineum » Sun Feb 05, 2017 8:29 pm

I was able to achieve the goal I wanted to. The creation of a filter table to store the filtered values did the trick.

Thank you very much for your help! :)
OpenOffice 4.1.3 @ Windows 7
Ferineum
 
Posts: 3
Joined: Wed Feb 01, 2017 10:51 pm

Re: [Solved] 2 Master Grids + 1 Junction Grid in 1 Form

Postby 1gatomontes » Wed Jul 05, 2017 5:32 am

Chrisb, I've been looking at the example file "seminar.odb" you provided years ago. It addresses the functionality needed in my database. Unfortunately, it does not quite work for me. As a result, I've been trying to understand OOBase better.

For example, using seminar.odb, when I select an employee and click on the Refresh button, this employee gets added to the seminar. That's nice, but in order to debug my application, I'd need to understand how the Refresh action works, and I cannot find any such information.

Is there documentation available somewhere?

Thanks!
OpenOffice 4.1.2
Windows 10 (v.1607)
1gatomontes
 
Posts: 13
Joined: Sun May 14, 2017 3:48 am

Re: [Solved] 2 Master Grids + 1 Junction Grid in 1 Form

Postby chrisb » Thu Jul 06, 2017 3:03 am

1gatomontes,

open the form for edit.
open both the form navigator & form properties.
using the navigator hit the '+' to the left of the mainform 'fFilter_Select_Seminar', this expands the form structure.

the mainform has two subforms 'sEmployee_Seminar' & 'sEmployees_On_This_Seminar'. expand both subforms to reveal their contents.

when a form is refreshed then it automatically refreshes all of its subforms. these subforms will then refresh their subforms & so on.

looking at the actual form we can see three refresh buttons, the FIRST under 'Select Seminar', the SECOND under 'Select Employee' & the THIRD under 'Employees On Selected Seminar'.
in the navigator click on each button & you will see that buttons FIRST & SECOND are both placed in the mainform. when we hit FIRST or SECOND then the mainform & subsequently both subforms & their list boxes are refreshed.
the THIRD button is placed in the subform 'sEmployee_Seminar'. it's needed to refresh the list of Employees after removing an Employee from the seminar.

by the way after selecting a Seminar the we must first hit 'SAVE' to save the record & then 'REFRESH' to update the subforms.
the 'SAVE' button is included because we have two subforms. if we had only one subform then the 'SAVE' button could be dispensed with & the 'REFRESH' button located in the first subform.

read through this topic especially the post by Arineckaig https://forum.openoffice.org/en/forum/v ... 85#p411485
Last edited by chrisb on Fri Jul 07, 2017 6:07 pm, edited 1 time in total.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 149
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] 2 Master Grids + 1 Junction Grid in 1 Form

Postby 1gatomontes » Thu Jul 06, 2017 5:05 am

Yes ChrisB, I've been working with the form navigator to access the controls properties. Sorry for my question not being as clear as I hoped it'd be. The post by Arineckaig does shed some light on my question though, so thanks for the link, and for the quick response! So far, this forum has turned out to be simply awesome!
OpenOffice 4.1.2
Windows 10 (v.1607)
1gatomontes
 
Posts: 13
Joined: Sun May 14, 2017 3:48 am


Return to Forms

Who is online

Users browsing this forum: No registered users and 8 guests