How do you set up input for many-to-many intersections in Base?

[rvsutherland]

In a Base database, I have 2 tables related in a many-to-many relationship. For example, say there is TableA and TableB. Both have Name and ID columns. TableA can have many TableB “children”, and TableB can have many TableA “children.”

I want a Form where the user can select an existing TableA Name in a ListBox, and then select several existing TableB Names in a combo box. When saved, the corresponding list of TableA-ID’s and TableB-ID’s would be inserted into a table having those 2 columns.

Is there a way to do this using Forms in LibreOffice Base?

Thank you.

[bonbonauswurst]

Hi,

Is there a way to do this using Forms in LibreOffice Base?

Yes there is.

At first as you said, you have to create a third table with these two foreign-key-ID-coloumns. Then create a form that is based on this new table. In this form create a table-control with its foreign key-ID coloumns. In edit-mode you can do right-click on the table-header and say “replace with”? (I use the german version, don’t know the right translation) and choose “list box”. After That, again right click on the coloumn header and click on “Cloumn…”. In the new menu, go on the register “Data”.

• In the field value(?) you select the value that TableC gets into (the foreign key ID).
• Type of values(?) = SQL
• listcontent(?) = “SELECT [coloumn you want to display], [id-coloumn] FROM TableA”
• bounded field(?) = 1

You also can display more than one coloumn in the listbox by using

• “SELECT…concat([col1], ’ ', [col2]), [id]…” for example

I hope I could help you with my terrible enlish

[ratrace]

This is a video i found on youtube with the procedure that bonbonauswurst describes.