[Solved] Main subform linking

Creating and using forms

[Solved] Main subform linking

Postby gkick » Sat Oct 19, 2019 12:11 am

Hi,

Contact and Activity table - one to many relationship,
Now I need to design a form either main sub or a couple of tablecontrols where I first select an activity from the activity table which then will display all those contacts who like skiing, etc.
My minds gone completely blank on how to use multiple master slave links.
The attached is just a dummy, in the real db the activity is an integer from a referencetable.
Thanks for your thoughts.
Attachments
mto.odb
(12.14 KiB) Downloaded 22 times
Last edited by gkick on Thu Oct 24, 2019 8:37 am, edited 2 times in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: main subform linking

Postby Villeroy » Sat Oct 19, 2019 11:35 am

In real life this would a many-to-many relation. Each person can be assigned to many activities and vice versa. Your one-to-many relation assigns each activity to one person. Therefore you can not select an activity and get all the related persons since there can be only one person per activity.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Main subform linking

Postby F3K Total » Sat Oct 19, 2019 12:51 pm

Find attached your file, modified, according to the idea of Villeroy, as a sample.
Attachments
mto.odb
(13.7 KiB) Downloaded 24 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Main subform linking

Postby gkick » Sat Oct 19, 2019 3:23 pm

Wow, fantastic and thank you F3K Total and Villeroy !!! Exactly what I have in mind.

Aah, and thanks for refreshing my memory Villeroy. Of course I need a junction table which means a bit of a redesign of my mainform (see attachment).
The goal is to display in a different form then three table controls to tell the story of

Activity Category Activity Contact
select one pick one display all contacts who like Golf

To do that I need to change the Description to a Category table (Junction) tblContactsActivities as per F3K Total
In the mainform put the Category field of the category table in front of the activity
Some how filter(sync) the Activity listbox by the selection of the Category listbox, would you have an example of that as I also could use that for country and region
Thanks a lot guys for your time and help
Attachments
currentTableControl.JPG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Main subform linking

Postby F3K Total » Sun Oct 20, 2019 12:44 pm

upload a sample containing all tables and their relations...
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Main subform linking

Postby gkick » Mon Oct 21, 2019 4:48 am

Hi, here s a quick and dirty mockup of my intentions put together at candle light (having some power outages here.)
The contact add edit form should allow multiple activities by different categories using listboxes, the selection of activity however limited to the category chosen.
The one and only query seems to to the right thing, the type acvtivity maintenance form is used to poulate the activities table
The original activity matching form needs a third control for the category ie
first select category, then select activity resulting in all contacts engaged in it
Hope this makes sense
GK
Attachments
mockup.odb
(32.8 KiB) Downloaded 26 times
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Main subform linking

Postby F3K Total » Tue Oct 22, 2019 6:52 pm

Try attached version using no listboxes. It's done without a macro. If you want to have a cascading listbox, there are tutorials in this forum, e.g. here: https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=94393&p=449618&hilit=cascading#p449618
R
Attachments
mockup.odb
(34.92 KiB) Downloaded 25 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: Main subform linking

Postby gkick » Wed Oct 23, 2019 3:42 am

Thank you very much, appreciate it.
Greetings from Chile
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 188
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest