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

Inkblotr · January 17, 2019, 4:23pm

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:

When entering a new Item, be able to see and select the paint color(s) - by color name (not just the ID).
When adding a new Color, be able to see and select the Item(s) by Item Description (not just the ID).
Be able to modify any Item record adding or removing colors.
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!

Ratslinger · January 17, 2019, 6:37pm

Hello,

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.

Inkblotr · January 17, 2019, 8:24pm

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 is real between Items and what we call “Types” (but colors worked for the example). If you can show me how to do it in the example file, I can do the rest, I hope.

Inkblotr · January 17, 2019, 8:30pm

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 · January 17, 2019, 8:36pm

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.

Ratslinger · January 17, 2019, 8:36pm

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!

Inkblotr · January 17, 2019, 8:46pm

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.

Ratslinger · January 17, 2019, 8:56pm

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.

Inkblotr · January 17, 2019, 9:16pm

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?

Ratslinger · January 17, 2019, 9:26pm

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.

Inkblotr · January 17, 2019, 9:33pm

Not going to migrate this system outside our house.

Ratslinger · January 17, 2019, 9:41pm

Only going by you mention of:

...though it is not the eCommerce site we want it to be.

Not to say your entire system but the possibility of data between eCommerce & your system. Again, just planning & future consideration.

Ratslinger · January 18, 2019, 6:01am

In answer to your question about connectors, there are some answers here in the forum. You can also check Chapter 2 - Creating a Database, section on Accessing external databases in the Base documentation.

Ratslinger · January 17, 2019, 8:15pm

Hello,

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:

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.

Inkblotr · January 17, 2019, 8:57pm

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…

Ratslinger · January 17, 2019, 9:02pm

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.

Inkblotr · January 17, 2019, 9:07pm

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 · January 17, 2019, 9:11pm

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

Ratslinger · January 17, 2019, 9:14pm

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 · January 17, 2019, 9:18pm

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.