Creating an Editable Many-to-Many Form

[Pino]

Hello everybody!

I aim to develop an editable form based on a many-to-many relationship between clients and products. I’ve structured my database with three tables: “clients,” “products,” and a linking table containing foreign keys: “clients_id” and “products_id.”

After constructing the main form using the ‘clients’ table, I created a query that combines the ‘clients’ table, the linking table, and the ‘products’ table. Subsequently, I inserted this query as a subform into the main form, linking the ‘clients_id’ column of the subform to the clients’ ‘ID’ column of the main form.

Unfortunately, it doesn’t work as intended. I encountered a challenge when attempting to add or edit data within the query-based subform. Additionally, the linking table fails to update accordingly.

My question is: How can I address this issue and create a functional, editable many-to-many form that ensures proper data management?

file in attached

test.odb (30.3 KB)

Thank you for all your help

[RobertG]

Think it is only an example - right? “Quantity” in the same table as “products” and “price” won’t work. If you never will change “price” you could combine “products” and “price” in one table. But you want to use this table for getting different “quantities” of a product, won’t you?

By the way: “quantity” should be a numeric field and price needs to be decimal with 2 decimal places. If you are writing this in text-fields you can’t count, multiplicate …

test.odb (40.5 KB)
Form doesn’t need a special query. You could insert clients and choose the item from a list box inside a table control. If you need more products you could add them in another table control. Then change to the listbox, mark the field for “products” and refresh the field (button in the navigation bar). Field will be available for choosing by list box.

[Pino]

Thank you, RobertG, for your help. However, in reality, the two tables “clients and products” are just an example of a many-to-many relationship to introduce the problem. What I am interested in is creating an editable form for a many-to-many relationship, regardless of the tables.

Unfortunately, it seems to me that this is not possible in LibreOffice Base since I can’t find anything around that explains how to create such a form. I really appreciate your contribution and the help you’ve provided so far. If you have any other ideas or suggestions on how to tackle this issue, I would be very grateful to hear them.

[RobertG]

You need to fill in values for 2 foreign keys. So the data for this 2 foreign keys must exist in the original tables (clients and products). This will work with a form and a subform where the subform contains a table control with a list box. The first foreign key will be provided by the mainform, the second foreign key will provided by the list box in the subform.

If there aren’t data available in clients and in products you couldn’t fill data into the table, which connects client and products. It won’t be supported to insert new data in a table, create a foreign key from the new data and save this key in another table. It will only be supported to change data with an existing key, which is already linked as foreign key to the other table.

[Pino]

That, I believe, underscores my point—it’s unfortunately not possible to have an editable many-to-many form that ensures proper data management directly from the form itself

[Villeroy]

A many-to-many relation consists of 3 tables and @RobertG demonstrates how to edit such a relation by means of one main form with one subform where the subform shows a column of many list boxes.
You can build arbitrary complex hierarchies of forms and subforms. For instance, you may want to add another main form to add more clients and more products that can be related to each other.

[Pino]

I see, btw I believe examples can often illustrate concepts more clearly than words alone. Would you guys be willing to provide an example using the file I uploaded? It would really help to understand better.

[Villeroy]

t83551.odb (32.1 KB)
Main form (clients) with subform and sub-subform showing the product details.
A second main form allows to add new products.
The navigation bar refers to the main form (clients).
After adding a new product, the second refresh button refreshes the focussed listbox when having the focus.

[RobertG]

@Pino : Have uploaded the example, depending on your database, with my first post here.

[Pino]

Hey guys, big thanks for the examples – they really hit the spot!
I’ve checked them out, and both solutions are spot on. They’ve given me fresh ideas on how to handle forms with many to many relation in order to manage all the data straight from the forms themselves. I’ve been scratching my head on this one for a while, but you guys nailed it! Big shoutout to @RobertG and @Villeroy for coming through. You both totally saved the day. Since I can’t tick off both solutions, I’ll just drop here : “problem solved” . Cheers!

[Villeroy]

You’re welcome. Two more points:

1. The quantity of a product is not a fixed attribute. It is a dynamic figure calculated from purchase and sales. SimpleInventory_embedded_FB.odb (55.3 KB)
2. The product price might change over time. Apache OpenOffice Community Forum - [Example] Invoice Forms (without macros) - (View topic) shows an example where the current price is copied to each sale record.
   Invoices_Articles_Prices.odb (160.4 KB) is a quick draft where all prices of all articles are stored in a separate table and looked up for each sale by the article-ID and the invoice date.

All samples without macros.