Creating a Form. Can a Slave have multiple Masters?

[mayhem2408]

Hi have a very simple 4 table SQLite3 database. It has a Master table for my Products. There are 2 sub tables that relate to the Product table, and the last table refers to both of the sub tables. I have been using this database for a few months and have no problem with my SQL queries using this data. I wanted to create a simple form to make data entry easy, but in LibreOffice Base, I can not see a way to have my “t_masks” table links to both the “t_designs” and “t_sides” tables Attached is a visual respresentation of how the tables relate. Can anyone tell me if it is possible to have my “t_masks” slave linkeds to both “t_designs” and “t_sides”

This screenshot is just a respresentation of how the tables relate

This screenshot is a basic and not very pretty test of the form

It’s probably a simple tasks, but I’m new to LibreOffce Base and haven’t figured it out yet.
In the screenshot of the form layout, there are two text boxes over on the right side that needs to show data that is related to both the tables to the left of it. Can someone help shed some light on my problem.
Thanks in advance.

PS: I am running LibreOffce 6.4 on Ubuntu 20.04 LTS using a SQLite3 Data source via ODBC

[Ratslinger]

Hello,

May be missing something here. Why isn’t t_masks related to t_product & then t_design & t_sides follow? It seems the product is a mask with mods of design and sides.

With existing situation, you can have multiple sub forms to a form but not the reverse. You would need two forms, each with a sub form.

[mayhem2408]

Thanks for your feedback. So a Product can have multiple sides and multiple Designs. Each of the sides and designs conbinations can have different Masks. The Mask is in a way related to the product, but not directly.

If I select Product 1, I can see the multiple side (Front and Back in this example) and designs available for the product (1 through 5 in this example). I select a Side and a design, and I can see the mask used for that design and side. There could be 100’s of masks for a product, but only 1 mask per side and design combination. The mask does not need to reference the product directly because the side and design reference the product, and the mask references the side and Design.

[Ratslinger]

@mayhem2408,

Sorry but I just don’t get it. Get the product but the sides and designs just don’t make sense. A number of different times have looked at this but still unanswered question. Such as where did Front, Back come from in t_sides? And similar question about design_codes in t_designs? Trying to understand this ‘process’.

Regardless, as stated in first comment, you can have multiple sub forms to a form but not the reverse. You would need two forms, each with a sub form.

[mayhem2408]

So If I where to start with all tables empty, I would first create a product in the t_products. This product Had ID 1.
I would then enter the sides of the product into the t_sides table referencing product ID 1. Each side to a product also gets it’s own ID.
I would then enter the Designs for the product into the t_designs table referencing product ID 1.
Now for each side and design, I need to reference a mask in the t_masks table. The mask is unique to a side and design. This is represented in the first screenshot showing how the tables relate and link with each other.

It looks like I am going to have to create macros to update text fields to achieve what I want.

[Ratslinger]

@mayhem2408,

Not certain macros are the answer. Using the information thus far, I am not clear as to why you have ‘design’ and ‘sides’ in separate tables. Product 1 has two sides. The 'sides table simply states which side you are working with. The ‘design’ table is the design for that side. Why different tables? From what I have understood, each side has a design. This then points to a mask.

What am I missing.

Even if my understanding is incorrect, don’t see why you could not do as I mentioned - two masters & two subs. I am reasonably certain this works but will do a bit more of a test a bit later.

Edit: Did mock file and many reasons two masters & two subs does not work. With that test the design seems flawed. Still cannot wrap my thought into more than two tables - product & design (combination of side, design and mask constitute a record).

[mayhem2408]

The t_sides table also holds information that is specific to that side of the product that does not relate to a mask or a design. If I was the have t_designs and t_sides as on table, without thinking about the layout to much, I would have duplicate information related to the side for each design, which means if I change the parameters for the front, it would have to be changed in each design separately. It would also make the database a lot larger and some of the parameters are rather long.
I have updated to screenshot above to try and better show the relationship between the tables.

[Ratslinger]

Hello,

I do not necessarily agree with your statements but I don’t have a full understanding of all of what you are doing either.

Have been through this before (see → Fill fields with two mother tables ?)

Have no other answer at this time.

[mayhem2408]

Excellent Thanks for the link. I have written a Macro based on this article and it is working fine.

[mayhem2408]

So my solution to this problem is a fairly simple macro attached to the After Record event on the Sub Forms.
The article describing the macro can be found here Fill fields with *two* mother tables ?

To simplyfy the macro, I made my t_masks a subform of t_sides Form and linked the sides_id fields. Then all the macro had to do is filter based on the t_design ID value. It’s working perfectly now.

A big thanks to ratslinger for the link.