Add multiple tables to a form

[gabeB]

Hey Guys

I created a simple Database in MySQL with three tables. I now have created a Form with the Wizard that displays my main Table "User" with Userdata and a SubTable called "Orders". I'd like to now also add the third table "Products" which is in relation to "Orders" as well. Is there a way to do that? I tried to add it as a Query but that did not work...

I am a total beginner at Base, but I just wasn't able to find an answer.

Thank you for your help.

Cheers,


Gabe

[UnklDonald418]

Open your form document in the Edit/Design mode.
Look along the bottom left there should be a Form Design toolbar (it can be turned on/off from View>Toolbars)
Select the Form Navigator. If you used the Wizard to create the form document you will probably see a SubForm.
Right click on that and select New to add another Form on the sub-subform level.
Right click on that and select Properties to open the Form Properties dialog and select the Data tab
With Content Type set to Table you should be able to select your Products table from the list on Content
You will need to link the tables together, select the ellipsis to the far right of Link master fields to get a field selection dialog.
Now that the data is available, you will need to add a control to the new Form so you can actually see the data.
For more on Form design you can download a Base Guide from https://documentation.libreoffice.org/e ... mentation/

[gabeB]

Hello UnklDonald

Thank you for your help! I've tried following your steps, also with the links, but I am now not able to see the other form...:


The form in the middle is the subform that has been created before...I also can only see the first four entries of the other Table.

Do you know what I might do wrong?

Kind regards,


Gabe

[UnklDonald418]

Without having a copy of your database it would be difficult to know what is wrong.
Look at this example
https://forum.openoffice.org/en/forum/v ... hp?t=56006

If you still need help here is a link to instructions on how to upload an example of your database for us to analyze
https://forum.openoffice.org/en/forum/v ... =74&t=8289

[gabeB]

Hello UnklDonald

Sure, here is the DB (the Data is shown correctly when I use a Join in MySQL directly, so its probably just me doing something wrong^^).

The Tables are in german, but I think you should get the logic behind it, otherwise let me know.

Thank you for your help.

Cheers,


Gave

[UnklDonald418]

I didn't find the .odb file in the zip archive you uploaded, but I forgot that you are using MYSQL for your backend.. I don't use MYSQL, so I probably couldn't connect to your data even if I had the .odb file.
What I need to know is table structures, relationships and what you expect to see on the form document.
Looking at what you posted earlier it appears that at the top of the form you select a User
The table below should get its data from SubForm and display any records from the Orders table, associated with the selected User.
I suspect the Form connected to the Products table should be at the MainForm level, not the sub-subForm level I initially recommended. That would allow all the records in the Products table be displayed and any edits or additions would be independent of the other two Forms.
I suspect the relationship between Orders and Products would be accomplished by a ListBox control on the ProductID column of the Orders table control

[gabeB]

Hello UnklDonald

Oh, okay, yes here's also the DB File.

Cheers,

[UnklDonald418]

As I suspected, I am unable to connect to your MYSQL database.
I get as far as the Authentication dialog where it reports that there are missing drivers and won't let me see anything else. Is there an actual password? I tried the MYSQL extension for Libre Office but I was still unable to make the connection.
Since I have no other need for MYSQL we can try to figure out a solution without my seeing the actual forms and tables.
It isn't clear to me how you want the Form to work.

[gabeB]

Okay, here's the ERM of my Database, maybe that helps:

Lhr92ME.png (31.26 KiB) Viewed 3699 times

I just try to have one form with all my Tables on it, so that I can lookup my entire database over one form.

[UnklDonald418]

The relationship diagram is helpful and I did find that when I opened Barcodescanner_DB.sql with Writer it revealed the script it uses to create the tables, so I now have those details also.
Based on all that and the earlier screen shot of your Form documnt I see
MainForm is using the table “User” as the data source and several individual controls to display the data.
SubForm is using the table “Bestellung” as the data source and a table control to display the data.
SubForm is linked to MainForm with the field “UserID”
In operation, when a User is chosen, all the records from “Bestellung” with a matching “UserID” will be listed on the SubForm table control.
If you add a Sub-SubForm using the table “Produkte” as the data source, linked to the SubForm with the field “ProduktID”
In operation it will display the product details for the selected record in the SubForm table. In other words the Sub-SubForm will display the details of only one record at a time.
If you want to simultaneously display the details for all the rows on the SubForm, then the data source for the SubForm would be a Query joining “Bestellung” and “ProduktID”

Using the Form Navigator right click on Forms at the very top of the list and add a New form. On the Data tab of the Form Properties dialog use the table “Produkte” as the data source. On the Form Controls toolbar along the left margin and usually near the bottom, make sure Wizards are turned On. Add a table control to the new Form and a Wizard will pop up, Chose the table “Produkte” and you can add the columns to the table control.
This would be on the same level as MainForm and operate independently from the other tables.
Screen size is the limiting factor in Form design. You can add more Forms and Controls than you have room on the screen to display.