A question about a listbox on a subform

Discuss the database features
Post Reply
kenwong
Posts: 3
Joined: Tue Feb 26, 2019 5:51 pm

A question about a listbox on a subform

Post by kenwong »

In my database in Postgresql, I have these tables: company (companyid, companyname, ...), product (productid, productname, ...), buyerproductname (buyerpdtid, productid, buyerid, buyerpdtname, ...), salesorder (salesorderid, buyerid, ...), salesorderitem (salesorderitemid, salesorderid, buyerpdtid, ...).

In LO Base, I have a form with salesorder as the underlying table of the main form and salesorderitem as the subform, for data input. In the subform, I want to let the user to choose the buyer's product name (buyerpdtname) from a list (so a listbox) which will feed the buyerpdtid field. Obviously buyerid is not present in the salesorderitem table but the salesorder table. How can I do that?

We have our own product names but for certain buyers and products they will buy, they have their own product names. That's why there is a separate table for buyers' product names.

So, another issue is, what are the ways to deal with this and which is the best one? I think one is to use our own product name where the buyer has no their own. Another one is to leave it blank in the table for buyers' product names (buyerpdtname).

Any help would be much apprciated.
Libreoffice 6.1.5.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: A question about a listbox on a subform

Post by Villeroy »

There is no best way to implement a many-to-many relation in a Base form. There is only one way.
[Example] Relations reflected by list boxes in forms. The relation between animals and persons is one-to-many. The relations between persons and things is many-to-many with an intermediate table mapping things to persons.

The receipe is always the same:
First list in the main form.
Intermediate table in a subform's table grid.
Second list in a listbox within the subform's table grid.
Now you select one item in the main form, get all related other items in the grid, selectable from listboxes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply