3 dimension junction table, is it possible?

[Renel]

Here is a scenario that leaves me a little confused.

I have a join table TBL_C which joins a TBL_A and TBL_B.

So the junction table TBL_C can look like this:

Record A1 is linked to record B1
A1 , B16
A1 , B4
A9 , B1
A10, B16
A9, B4

I have a TBL_D whose records can be set as attributes referring to records in both TBL_A and TBL_B, or rather links created in joined table C. It could look like this:

[Record A1 is linked to record B1] is linked to record D1
[A1 , B16] D1
[A1, B4] D3
[A9, B1] D2
[A10, B16] D3

The problem I’m having is that the TBL_C can only contain two fields, a three-dimensional joined table doesn’t seem possible. What is the logic to follow in the relations in order to be able to link the records of TBL C [TBL_A and B] to the records of TBL_D? Said another way, how can I join a junction table to another table?

[Villeroy]

t79265.odb (3.7 KB)

Lets say TBL_A is one group of persons, TBL_B is a different group of persons and TBL_C keeps categories such as friends, foes, mates, married, professional.
TBL_ABC descibes the type of relation between 2 persons.

[Renel]

Ok Villeroy, if I understand correctly, I can integrate as many fields as necessary into the junction table without requiring them to be associated with a primary key and the relationships of groups of people and their attributes will be perfectly functional.

If so, it gives me a lot more flexibility than I thought and brings a little more refinement to my DB.

Thanks a lot!

[Villeroy]

The primary key is a combined one made of AID and BID which are also foreign keys to their respective detail tables.

[Renel]

t79265modbyrenel.odb (13.8 KB)

Villeroy,

I return your DB with a form that implements your solution. It seems to work very well. The only thing is that the form has to be refreshed for each person in group B that is added before you can select an attribute and you have to refresh again, but it works.

Thanks so much for taking your time to help!

[Villeroy]

OK, there are solutions to the refresh problem. I added 3 logical forms “NewA”, “NewB” and “NewC” to your form. They are set to “add new record only” and add a new name to A, B or C. When a listbox is selected, a second refresh button becomes enabled. This one refreshes only the listbox without reloading the form.
Then I made a copy of that form, added a little auto-refresh macro to the database and added hidden controls named “AutoRefresh” to the “NewB” and “NewC” form so they refresh their respective listbox automatically. For this feature you have to store the document in a “trusted directory” according to Tools>Options>Security … button [Macro Security], tab “Trusted Sources”.
More about that macro: Apache OpenOffice Community Forum - [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes - (View topic)

P.S. I also added a unique index on the text fields of TBL_A,B and C. This way, you can’t enter duplicate names.

[Renel]

Villeroy, you mention that you made changes to the form that I sent to you, but I believe that you did not return these changes to me.

I’m reading the ApacheWiki link on form refreshes and it’s really interesting, maybe a little complex for the little knowledge I have in this area, but I’ll persist in order to integrate all of this.

[Villeroy]

I forgot to upload the document.
Here it is:
t79265modbyrenel_2.odb (26.3 KB)

[Renel]

Thank you Villeroy,

I’m going to take the time to thoroughly analyze these new options while referring to the Apache OpenOffice link that you suggested to me.

I discovered on this site a large number of Topics that I would like to take the time to read, because I believe that they will be of great use to refine the operation of my DB.

All the same, my DB is already very functional and what you have suggested so far has been a great help and very useful. Once again, thank you very much!

[Villeroy]

That little macro is optional. It offers a convenience feature that can be added to a working form. You get the same functionality from the first form with no macro code but with a few more clicks.

I omitted the macro assignment to the form “NewA” because I was too busy and focussed on the list boxes. The form “mForm” is not refreshed automatically when you add a new name to group A. So I leave it up to you:

1. Use the form navigator, right-click “NewA” and add a hidden control to “NewA”.
2. Rename the hidden control to “AutoRefresh” and set its value to …/mForm (from this form one level up to “Forms” and then down to “mForm”).
3. Get the properties of form “NewA”. On the “Events” tab scroll down to “After Record Action” and assign the following macro: t79265modbyrenel_2.odb > AutoRefresh > formAction

How it works:
A form’s event “After Record Action” is triggered when the form has inserted, deleted or edited a record.
The triggered macro finds the hidden control “AutoRefresh”, reads its text value specifying a control or form and refreshes it. In more complex forms, you can specify more than one element separated by semicolons. This works very well if you specify the elements correctly by their case-sensitive names and if your element names have no slash nor semicolon. The macro applies to (sub)forms, listboxes and combo boxes. These 3 types of elements are the only ones which include a record set that needs to be reset when the record set has modified somewhere else.

[Renel]

Villeroy,

This is a question that may seem very basic to you, but it leaves me slightly confused.

What is the point of using an AutoRefresh macro. If I understand correctly, this allows an automatic refresh of a form or in other words, allows reloading (rereading) of tables.

In fact, throughout the data entry process, there is the navigation bar save button that appears and disappears after saving data in the table linked main form and so on for the sub -forms.

The data is permanently stored, so there is no risk of data loss. So what is the point of reloading or rereading the tables via a form refresh?

[Villeroy]

The first form works without any macro code. You type in new persons or categories, hit Enter (which triggers the OK button) and when you go back to the form or listbox, you have to refresh the element manually. Untrained form users hate this. They expect to find the newly entered item immediately in the other form elements.
In the second form you type in new persons or categories, hit enter and the newly entered items are selectable from the listboxes or accessible in the form (the main form in this particular case).

[Renel]

OK! It is only a question of functionality or handling depending on the type of DB users.

If I decide to use this script for a DB that comes with the following levels: L1: MainForm; L2 SubForm1; L3 SubForm1a; L3 SubForm1b

The hidden form should be located at level L3, is that correct, same level as subform 1a and 1b. Is it necessary to identify each form in the Value field of this hidden form? This is something that is not entirely clear to me…

[Villeroy]

The hierarchy of forms in both forms is correct as you made it. I did not modify this hierarchy. I just added 3 more main forms NewA, NewB and NewC with property “Add new records only”. Everything you enter here goes to a new record.
Level 1: mForm, NewA, NewB, NewC are independent main forms directoly below the root named “Forms”
Level 2: mForm has one subform showing all related B persons of the currently selected A person in the parent form.
Level 3: The level 2 subform has its own subform showing a listbox with the distinct category of the currently selected relation in its parent form.

All this is correct and functional so far.

The macro form has no “hidden form”. What I added to forms NewB and NewC (and forgot in NewA) is a hidden control. A hidden control is just a named value that can be used to configure macros. The name of the value is “AutoRefresh” and the value is the path to the form or form control to be refreshed by the macro.