Ask Your Question

Base n:m many to many relationships between two tables using intermediate table, how to enter, display & modify the data using forms?

asked 2019-01-17 17:23:10 +0200

updated 2020-07-24 14:22:57 +0200

Alex Kemp gravatar image

File upload: Many_to_Many_Concept.odb

The uploaded file is a simple example of 2 tables in a many to many relationship using an intermediate table. The Items table represents pieces of metal art. The Colors table represents paint colors that can be applied to the various Items. All colors are NOT applied to all Items. There is relevant data in all 3 tables. I've completed the Tools/Relationships chart accurately.

The goals:
1) When entering a new Item, be able to see and select the paint color(s) - by color name (not just the ID).
2) When adding a new Color, be able to see and select the Item(s) by Item Description (not just the ID).
3) Be able to modify any Item record adding or removing colors.
4) When viewing Item records, see the color names displayed on the form.

I suppose I'm no longer a newbie to LO base but I'm no expert either. Any advice would be greatly appreciated.

Thank you in advance!

edit retag flag offensive close merge delete



Have previously looked at your web site & so have a tiny bit of understanding at to your project here. Having a difficult time placing what you want into the overall picture. For example, when Item 0 (Bird - large) is ordered, a) is that available in only red, blue and orange?; b) how is it known what color is wanted - seems no tie to order process? What am I missing? These three tables seem isolated from all else with the exception of an item (of what color is unknown) to be ordered.

Ratslinger gravatar imageRatslinger ( 2019-01-17 19:37:36 +0200 )edit

Hi - I appreciate you checking out our web-site though it is not the eCommerce site we want it to be. I hope you followed the link to our etsy store where you can better understand the breath of our metal art inventory. The example file I uploaded is just a microcosm of the larger system, in my attempt to not "muddy the water" with a lot of other superfluous minutiae. Your questions are good. The system is not part of a customer ordering system. It is part of a Sales Order Tracking, Inventory and Costing system. I'm trying to build the Sales Order Tracking piece first, since that is the urgent need. The Item file is the "heart" file (in my mind) of the system. Each piece of metal art has attributes associated with it that are used to analyze sales and forecast production. The many to many relationship ...(more)

Inkblotr gravatar imageInkblotr ( 2019-01-17 21:24:32 +0200 )edit

Part 2 - My Wife and I are the only users of the system I'm creating. We are constantly thinking of new metal art to make and offer. Those new pieces (new Items, in the example uploaded file) would be assigned various "colors" (think "tags") that would then be used for sorting, grouping, filtering, etc. for future reports (that I have yet to begin creating). We occasionally add new tags (colors) but often add new Items (and their related "tags"). Today, we track everything in an incredibly complex (my opinion) Calc spreadsheet that is cumbersome to maintain and VERY inflexible. Let me know if you have more questions, PLEASE!

Inkblotr gravatar imageInkblotr ( 2019-01-17 21:30:58 +0200 )edit

Part 3 - I haven't specifically answered your questions - I'm sorry. Here goes. a) Item 0 (Bird - large) would only be available in Red, Blue and Orange - Correct. b) The system would not be interacting with potential customers to be concerned about a different color request. I hope this helps.

Inkblotr gravatar imageInkblotr ( 2019-01-17 21:36:08 +0200 )edit

One quick question. I know you've tagged questions with HSQLDB. Are you using the default embedded HSQLDB? IF so, before you go too far, consider changing. It is not for production use as there can be potential data loss. Backup!

Ratslinger gravatar imageRatslinger ( 2019-01-17 21:36:37 +0200 )edit

Thank you for bringing up HSQLDB. I'm using the internal HSQLDB as a development platform and for learning. I have a MySQL server sitting across the room, that I'll move to for multi-user access (Wife & I) once I get the system working well enough to use. I see you've posted an answer, I'm about to look at it now.

Inkblotr gravatar imageInkblotr ( 2019-01-17 21:46:40 +0200 )edit

Consider moving to MySQL now (I personally find PostgreSQL and Firebird to be better). You can place MySQL server software on any machine and migrate to another machine later. There are multiple reasons for changing NOW. Already mentioned embedded problem with data loss. Also, that embedded version is VERY old. Moving to MySQL or another DB allows you to take advantage of newer versions of SQL and capabilities. Later conversion may require you to re-write SQL you may already have as syntax may differ. Field types in tables can differ also. Well this is some. Hopefully you get the point.

Ratslinger gravatar imageRatslinger ( 2019-01-17 21:56:12 +0200 )edit

Okay, you've made an impression. Will I have to add a connector to my workstation Base install to connect to say a Firebird instance, also installed on my workstation?

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:16:06 +0200 )edit

Well, first thing is to determine which DB you are to end up with. I believe I mentioned planning before. Consider in this choice the future and how e-commerce may affect this as hosting companies may allow for different DB's (they host but you interface with) and this may be a factor in choosing. Migration and connection will depend upon the from and to and is best in a new question.

Ratslinger gravatar imageRatslinger ( 2019-01-17 22:26:51 +0200 )edit

Not going to migrate this system outside our house.

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:33:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-01-17 21:15:31 +0200

Ratslinger gravatar image

updated 2019-01-17 22:53:02 +0200


Notwithstanding the open questions in the comment, have modified your 'Item' form. It is simply adding a sub form here. A table control was used and the Color_ID field was changed to a List box to display the color (from color table) rather than the ID.

Sample ----- Many_To_Many_Question.odb

A few other points. Have seen you have macros (did not look at closely - yet). Since there are still some open bugs regarding form sizing, you can set the size and position of the form using a macro. See:

Base: How to define a form's exact size?

and on Dutch forum (has sample files):

are the properties width, heigth, xpos, ypos of a form in base accessible via basic?

The other point is the button use for changing forms. Don't see that as necessary here. You can have multiple internal main forms on a Base form. You can also hide/reveal controls, if wanted, when using different forms. See:

Hide combobox in starbasic with Macro

Libreoffice Base - Display Form based on Group Box Option selected (be sure to see link in comment under answer)

Tabbed forms within a Main Form

Edit 2019-01-17:

OK. Here is the modified form in edit view. Have used the Form Navigator and selected the SubFor and show the properties:

image description

By selecting the ellipsis to the far right of either Link property brings up this dialog:

image description

By using the drop down (actually a list of fields in each table) you can relate what is to be viewed in the sub form. Now although your intermediate table contains entries for many items, this linking process will only show those items in the intermediate table which match the selected field in the displayed Items (master) table.

Hope this helps.

edit flag offensive delete link more


I've looked at the file you uploaded and the ItemsModified form. I see the table shows the Item_ID and Color columns. I just realized I omitted something that is significant. I don't see a way to "select" colors associated with a new Item and would then be saved as new record(s) in the intermediate rel_Item_Color table. I'll now look at the other links you provided...

Inkblotr gravatar imageInkblotr ( 2019-01-17 21:57:01 +0200 )edit

Maybe I don't see something. On this modified form, if you enter a new item the sub form will be empty. You can add records to this sub form - this is the association and thus the intermediate table is updated.

Ratslinger gravatar imageRatslinger ( 2019-01-17 22:02:11 +0200 )edit

Then I must be missing something. When I add a new Item record I don't see how to scroll or how to select color choices....

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:07:02 +0200 )edit

I've added 2 new items and there are no associated intermediate table records for the new Items....

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:11:12 +0200 )edit

Many things to learn yet. When you enter a new item, the record is not in the database until it is saved. Until it is saved there is no connection in the sub form. Two ways to save on existing form. 1) go to a different record (new or otherwise) and back to this one which essentially saves this record, or 2) on the Navigation bar under the Item click on the Save Record icon which will display the new record & allow entry in the sub form.

Ratslinger gravatar imageRatslinger ( 2019-01-17 22:14:36 +0200 )edit

This empty sub form now has the Item ID already filled in. To add an item, select the Color field & use drop down to select color or simply start typing color wanted. It is list box as discussed in another question.

Ratslinger gravatar imageRatslinger ( 2019-01-17 22:18:03 +0200 )edit

Okay, I see it working now. But I don't see how you did it.... When I "edit" the ItemsModified form, I can bring up the Properties of the Navigation Bar, nothing to see there. When I bring up the Properties of the Table Control I don't see anything there either....???

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:22:22 +0200 )edit

Sorry, should have mentioned in answer. This is how you typically use a sub form (see LO docs for more info if needed). You limit what is seen in a sub form by linking it to the main form. This limits records in the sub form to what is associated with it in the main form. Look at the sub form properties on the Data tab under Link master/slave fields items.

In this manner you can go even further with sub sub forms, sub sub sub forms etc.

Ratslinger gravatar imageRatslinger ( 2019-01-17 22:32:41 +0200 )edit

My newbie-ism has me in a fog, I'm not understanding...

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:35:54 +0200 )edit

Okay, found the sub-form properties.

Inkblotr gravatar imageInkblotr ( 2019-01-17 22:50:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-17 17:23:10 +0200

Seen: 723 times

Last updated: Jan 17 '19