OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Insert a subform for a many to many relationship

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Purplenaartjie
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Thu Jan 05, 2012 9:19 am    Post subject: Insert a subform for a many to many relationship Reply with quote

Hi,

I have my three tables: Contacts, Boroughs, ContactBoroughs - the last having two foreign PKs.

I have created my form Contacts and used Form Navigator to create a subform called Boroughs.

Now I just want to insert my subform allowing each Contact to select from many boroughs. How do I do this? In Access I'd just use the subform tool.

Help!!!!
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Fri Jan 06, 2012 3:05 am    Post subject: Reply with quote

Welcome to the the forum and I am sorry that you have not already had a reply to your post.

I suspect an example is better than any description that I can offer. There is a simple demo of handling the many-to-many relationship in Base that can be downloaded from here. You will need to read 'Contacts' in place to the demo's 'Clients' and 'Borough' in place of 'Trips'.

The demo file shows how Base differs from Access in the use it makes of List boxes. In Base the list box is best used to display more user friendly information when handling integer foreign key fields. Furthermore a list box in Base cannot be used for filtering record-sets without resort to a macro. As shown in the demo file that operation in Base is better handled by the data form/sub-form relationship which displays in the sub-form (or sub-sub-form) only those records that relate to the record selected in the relevant parent form. Each change of record pointer in the parent form automatically updates the record-set available to the sub-form.

Please come back if you have any problems or questions.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Purplenaartjie
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Fri Jan 06, 2012 4:45 am    Post subject: Reply with quote

The example is perfect for what I want. But I still can't see how I insert a table control or a sub form into my main form (using the wizard I can only insert data from the joining table as a sub form). The control button in the toolbar is greyed out, not sure if that's relevent. Help!
Back to top
View user's profile Send private message
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Fri Jan 06, 2012 8:00 am    Post subject: Reply with quote

Quote:
how I insert a table control or a sub form into my main form (using the wizard

The wizards provided for Base are rudimentary and should only be used for the simplest of tasks. Any serious work requires the use of the form document in 'edit mode' (right click on the required form document name from the Base opening screen and select "Edit"). You will also need to ensure the 'Form design' and 'Form controls' toolbars are available. The fifth icon, 'Form navigator' from the top/left of the 'Form design' is crucial for adding data forms or sub-forms to the form document.

I would suggest you also check out the tutorials to be found at:
http://neowiki.neooffice.org/index.php/To_Base_and_Back_Again
and in particular:
http://neowiki.neooffice.org/index.php/Adding_a_Subform_to_a_Form_in_Design_View
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
Purplenaartjie
Newbie
Newbie


Joined: 05 Jan 2012
Posts: 3

PostPosted: Fri Jan 06, 2012 8:52 am    Post subject: [solved] Insert a subform for a many to many relationship Reply with quote

That's it! Thankyou so very much... I can stop pulling my hair out now! Wink
Back to top
View user's profile Send private message
gkm
Newbie
Newbie


Joined: 09 Jan 2012
Posts: 3
Location: Canada (Montreal), France (Poitiers)

PostPosted: Mon Jan 09, 2012 10:15 am    Post subject: Subforms: Block Labels vs. Tables Reply with quote

Greetings to all.
My name is George (Georgios).

This is my first post in this forum.

I'm no programmer, but I have contributed a bit in the "concept creation" of a few databases.

I've looked through a number of messages concerning SUBFORMS, but II cannot seem to find an anwser to the following question.

Qn : If the MASTER form is in BLOCK LABEL layout, is there a way to make the SUBFORM (I suppose "SLAVE") in BLOCK LABEL layout as well? Furthermore, will the "SLAVE" BLOCK LABEL be "nested", with functional scroll buttons?

I've downloaded the sample "ClientsTrips.odb" file, and spent a while trying to make the "TABLE " to "BLOCK LABEL" conversion, but in vain.

If a solution DOES exist, I'd be most obliged if someone could give me a "step by step" explanation.

Kind regards to all.

PS: Concrete application: I'd like to create a simple form divided in two parts: The upper part will include CLIENT identification while the lower, NESTED part, will include DAY TO DAY information on the SAME client (example, changes of some variable, such as temperature)that Id like to fill in.
_________________
Good suffices not, unless well done. Οὐ γὰρ πράξην ἀγαθὴν, ἀλλὰ καὶ εὖ ποιεῖν αὐτὴν. Georgios K. MICHALAKIS (GKM)
Back to top
View user's profile Send private message Send e-mail
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Tue Jan 10, 2012 12:53 am    Post subject: Reply with quote

Quote:
Qn : If the MASTER form is in BLOCK LABEL layout, is there a way to make the SUBFORM (I suppose "SLAVE") in BLOCK LABEL layout as well? Furthermore, will the "SLAVE" BLOCK LABEL be "nested", with functional scroll buttons?

First, I would like to welcome you to the forum.

Secondly, I must apologise for not knowing what is implied by a "BLOCK LABEL layout". If you were able to enlighten me, I may be able to offer some help with your question. For example, does the content of the "BLOCK LABEL" in the main data form contain data from more than one field from its underlying data source?
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
gkm
Newbie
Newbie


Joined: 09 Jan 2012
Posts: 3
Location: Canada (Montreal), France (Poitiers)

PostPosted: Tue Jan 10, 2012 1:31 pm    Post subject: "nested" suform in "block label" layout Reply with quote

Thanks for your prompt response, Arineckaig.

Quote:
what is implied by a "BLOCK LABEL layout"

A number of images are attached below, so as to demonstrate what I translated from French as "block label". "Label" corresponds tothe "title" of a column, and "block" is the area within which each table cell can be viewed. Please see images below.

Quote:
does the content of the "BLOCK LABEL" in the main data form contain data from more than one field from its underlying data source?

I'm not sure, and it rather incumbs on me to apologize for using such terms given my ignorance of more appropriate ones!

Here is a description of what I'm trying to create as well as an image of what I'd LIKE to obtain as a final result ("aspired" or "expected" result).

The Form is divided in TWO parts:
A) the TOP part will contain PATIENT information that is usually CONSTANT, and can be used for IDENTIFICATION purposes (ex Name, date of birth). I call this MAIN or "Master" form)
B) the bottom part will contain a NESTED subform, which will depend upon the date and time the patient visits the doctor. For each PATIENT, MANY such "dates" are possible.
C) Since both MAIN Form and nested SUBFORM are likely to contain MANY cells, a SCROLL on the right side will be necessary for EACH one.



Here is a view of what I'd LIKE to obtain!



Here is the database I have created using the French version of Open Office Base. It contains MANY cells of information that i s usefull for a medical cabinet.

http://graeca.canto.ru/upload/MontrealPsaltiki/Med_2012/drMedConsultation.odb

My hunch is that some sort of table "linking" is required, and that I have been making labelling and "linking" errors in spite of my efforts to create linked tables in ALL possible directions (left to right, right to left, etc...)! I therefore abandoned manual linking, and used the wizard to create the form.
Screen shots provided below use "simplified" tables from a "simplified" version of the "complete" database provided above.

I'd appreciate comments on the "technical terms", so as not to burden further messages with unnecessary details.

Thank you all for your patience.

GKM

Step by step screenshots:























Here is what was obtained



This is a recall of what is EXPECTED

_________________
Good suffices not, unless well done. Οὐ γὰρ πράξην ἀγαθὴν, ἀλλὰ καὶ εὖ ποιεῖν αὐτὴν. Georgios K. MICHALAKIS (GKM)
Back to top
View user's profile Send private message Send e-mail
Arineckaig
OOo Advocate
OOo Advocate


Joined: 01 Mar 2004
Posts: 358

PostPosted: Wed Jan 11, 2012 2:54 am    Post subject: Reply with quote

Quote:
Here is a description of what I'm trying to create as well as an image of what I'd LIKE to obtain as a final result ("aspired" or "expected" result).
The Form is divided in TWO parts:
A) the TOP part will contain PATIENT information that is usually CONSTANT, and can be used for IDENTIFICATION purposes (ex Name, date of birth). I call this MAIN or "Master" form)
B) the bottom part will contain a NESTED subform, which will depend upon the date and time the patient visits the doctor. For each PATIENT, MANY such "dates" are possible.
C) Since both MAIN Form and nested SUBFORM are likely to contain MANY cells, a SCROLL on the right side will be necessary for EACH one.

Your points A and B represent a one-to-many relationship that is often well handled by Base when a sub-form is linked to its parent main form with its 'Slave field' linked to a 'Master field' in the main form PROVIDED these linking fields are of the same data type. Typically the linking fields will be the 'Primary Key' field in the table that is the data source for the main form and a relevant 'Foreign Key' field in the data source for the sub-form. The effect will then be that whatever record is displayed or selected in the main form, the record-set displayed or supplied to the sub-form will be filtered to include only those records whose foreign key field matches the primary key of that main form record. Also, when there is any change of record selected or displayed in the main form, Base will automatically update the filtering of the record-set made available for display in the sub-form.

On your C point, I doubt there is any method by which Base can apply scroll bars to either a main form or a sub-form. To justify that opinion some more precise definitions would be helpful because the English word "form" can be applied to several different and distinct objects in Base.

"Form document" can be defined as any of the so-called forms that can be opened from the Base initial screen, such as the "F_Consultation001" object in the case of your sample "drMedConsultation.odb" file - a form document is essentially a particular type of Writer document;
"Data form" can be defined as a main form or sub-form contained within a form document: for example the two objects called "MainForm" and "SubForm" are data forms contained within your "F_Consultation001" form document;
"Form control" can be defined as any of the various objects (fields, labels, buttons etc.) that can be contained within a data form.

In summary, a form document is a 'container' for data forms, which in turn are 'containers' for form controls. Furthermore one particular form control is also a 'container' for other form controls: the "Table control" in Base is a form control that contains other form controls (its columns) - it is not of itself a data form but is specifically contained within a data form which may well contain other form controls in addition to a “Table control”. Bear with me, as hopefully the need for this tedious introduction will soon become clear.

An important feature of any “Data form” is that unlike a “Form document” or a “Form control”, it is merely a logical object: there is no physical or graphical representation (technically known as a “view”) of a data form in Base. Thus there is no graphical object to which scroll bars can be applied. It might be thought that when a form document is in edit mode it is possible to select a data form and have it displayed as an object enclosed within green selection points, but in reality that display is merely a logical frame around all the form controls contained within the data form: it is not a graphical representation of the data form itself.

Returning to the issue raised in your first post a data form could be said to be always in BLOCK LABEL layout even when the only form control it contains is a 'Table control'. Duplicates of each of the form controls represented as columns in that 'Table control' could be added as extra form controls directly into the data form that contains the 'Table control'. If the 'Table control' were then to be deleted it could be described as a 'conversion' from one layout to another of the contents of that same form control.

Please come back if this lengthy explanation is merely a cause for further confusion or if you have other questions.
_________________
When this issue has been resolved, it helps other users of the forum if you add the word [Solved] to the Subject line of your 1st post (edit button top right).
OOo 3.4.1 and MySQL on MS Windows XP and Ubuntu
Back to top
View user's profile Send private message
gkm
Newbie
Newbie


Joined: 09 Jan 2012
Posts: 3
Location: Canada (Montreal), France (Poitiers)

PostPosted: Wed Mar 07, 2012 11:01 pm    Post subject: Reply with quote

[quote="Arineckaig"]
Quote:

Please come back if this lengthy explanation is merely a cause for further confusion or if you have other questions.


I am very grateful for your in depth analysis of my problem.
I read your response about ten times over the past two months, and I must admit that I do NOT completely understand everything.

I know that what I wish to accomplish with odb is possible with the much heavier program, "Access". I also know that ti can be accomplished with "MySQL", because last year undergraduates have done so on some server database.

Given the above facts, I "assumed" that it was something "easy" and "straightforward" to do in open office.

Unfortunately, I seem to understand that it is either "quite an endeavour" or even "impossible"!
Whatever the case, I have given up the project for the time being, but I do feel that a solution should be found to create what I call "forms with nested subforms" in a "one to many" relation. Such a "script" can be quite useful in many similar projects.

Once again, I express my gratitude for the time and effort you invested in so as to help me.

GKM
_________________
Good suffices not, unless well done. Οὐ γὰρ πράξην ἀγαθὴν, ἀλλὰ καὶ εὖ ποιεῖν αὐτὴν. Georgios K. MICHALAKIS (GKM)
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group