[Solved] Showing records from many-to-many relations on form

[EarlMorton]

I'm trying to display data on a form. The data is coming through a many-to-many relationship.

I have set up a test database to figure out how to set this up in OpenOffice Base. (I've done it many times in other database manangers, but this is the first time I've tried to use Base.) The main and secondary tables have two fields each, an ID field that is automatically entered, and a text field. A third tables links the other two through their ID fields to establish the many-to-many relationships.

My form contains both fields from the main table, and displays each record one at a time. But I also want the form to list the related records from the secondary table. All I can get it to do is list all the records from the secondary table, not just the related records.

I've looked at several earlier threads on two different forums, and can't find what the problem is. I'm sure I'm overlooking something simple. Can anyone spot what it is?

Thanks!

Earl Morton

[RPG]

Hello

On this forum are two sub forum for Base one for Tutorials and for for examples examples
I think a good starting point can maybe this [Tutorial] Forms in OpenOffice.org Base. You can find there also links to other tutorials. Not all tutorials are even important for you.

In your case use the master and slave field in the subform.

Romke

[Arineckaig]

I've looked at several earlier threads on two different forums, and can't find what the problem is. I'm sure I'm overlooking something simple. Can anyone spot what it is?

Welcome to the forum. To assist your study of the recommended tutorials, if may help to have a practical example to hand. In the attached revised file you will see an extra but revised form document where there are the following changes from the original:

1. The sub-form uses the link table “DomainLinks” as its data source;
2. The master and slave link fields in the sub-form are set to their respective “NoteID” fields;
3. To be more user-friendly the “DomainName” column in the sub-form is replaced by a list box whose content is the SQL statement -

Code: Select all

SELECT "DomainName", "DomainID" FROM "StudyDomains" ORDER BY "DomainName" ASC

Thus, the “DomainName” field is displayed from the “StudyDomains” table but the listbox itself is bound to the “DomainID” in the “DomainLinks” table;
4. A superfluous text box column added to the sub-form merely to demonstrate how the link fields between the two forms update the sub-form every time the record pointer in the main form changes.

Please come back if this example merely confuses further or you have other questions.

[EarlMorton]

Thank you RPG and Arineckaig for the quick responses! I've been trying to get this working for a couple weeks, and am getting rather frustrated. I really appreciate you taking the time to help me out with this!

I nearly decided that Base simply wasn't mature enough to do what I want, but at least you've shown me that that is not true. However, I still do not understand how you made it work. I'm sorry, but I'm confused at almost every point.

1. The sub-form uses the link table “DomainLinks” as its data source;

Where can I see and set this? I am looking at the Properties page, but it does not have the Data tab. (I don't understand why Base sometimes shows the Data tab, and sometimes not, even for controls that are linked to data, like this one.)

2. The master and slave link fields in the sub-form are set to their respective “NoteID” fields;

Same problem. I have seen the the master and slave link fields sometimes while tinkering with this, but right now, I can't find them.

3. To be more user-friendly the “DomainName” column in the sub-form is replaced by a list box

This one has me really confused. The DomainName column is part of a table control. How do you replace part of a control with another control? I also don't see any list boxes on the form, and none are indicated in the Form Navigator.

whose content is the SQL statement

You have to know SQL to do this? Doesn't the Relationships tool tell Base all it needs to know about where the data comes from? What's it for if it doesn't do that?

Thus, the “DomainName” field is displayed from the “StudyDomains” table but the listbox itself is bound to the “DomainID” in the “DomainLinks” table

How do I bind the listbox (which I cannot see) to the right table and field?

4. A superfluous text box column added to the sub-form merely to demonstrate how the link fields between the two forms update the sub-form every time the record pointer in the main form changes.

I understand that this is for illustration and not required to make the form work, but what is a "text box column" and how is it different from the list box control you used for the DomainName column? I can't see this control either, neither on the form nor in the Form Navigator.

I have looked at some of the tutorials also, but I have to say that tutorials generally do almost nothing for me. My brain just doesn't work that way. (Those who analyze such things call me a "deductive learner"; tutorials teach inductively.)

Thank you again!

Earl Morton

[Arineckaig]

Sorry to have confused you, but it will take me a little time to draft a reasonable explanation.

[Arineckaig]

It may be simpler if I try to explain points 1 & 2 first. I have prepared a simple explnation of the process by which these first two changes were implemented and have attached it as a PDF file to ensure the illustrations stay put.

First it was necessary to change the data source for the sub-form to the "DomainLinks" table because there is no common field between the Notes table and the StudyDomains table. It is an essential element of a RDMS that there are linking fields that relate to one another in each table if one is to be used as the data source for a main form and the other for its sub-form.

Secondly, it is essential that these link fields are set as the respective Link Master and Slave properties if the two forms are to be synchronised and work together.

Let me know if the attached file is of any help - there is no point in going to the next stage until you understand how a sub-form links to its parent form in Base.

[EarlMorton]

Yes! I'm with you so far. I was going wrong at step 5 by looking at the properties for the table control, rather than for the subform. So I was missing a link in the chain. Please continue!

Earl

[Arineckaig]

I have attached a further PDF file in which you will find an attempt to explain:
1. How to replace a form control in the column of a table form control;
2. How to set the properties of a form control in the column of a table form control;
3. How to set the Data field property which is the bound field of a list box;
4. How to use the Query Design dialog to supply the SQL required for a list box.

I hope this will at least get you started but please let us know how it goes.

[Villeroy]

[Solved] How to insert/create a grid

[EarlMorton]

I think I've got it! Well, at least, it is working. I can't say I completely understand it, and I'll certainly have to do it several more times before the process is firmly in mind. Thank you all the help!

And thank you, Villeroy, for the link to additional information. That's good background to help understand how it works.

Earl