Base Form: How can an unusual (layered) 1:m relationship be handled?

[gpreg]

I have the following situation where I need to maintain the following tables (with the objective of doing this in a single form):

• Table 1 (T1) with fields: t1.s (pk), t1.b, t1.x
• Table 2 (T2) with fields: t2.h (pk), t2.s, t2.y
• Table 3 (T3) with fields: t3.m (pk), t3.h, t3.s, t3.z

… and these have the corresponding relationships:

• There is a 1:m relationship from T1 to T2 based on t1.s = t2.s
• There is a 1:m relationship from T1 to T3 based on t1.s = t3.s
• There is a 1:m relationship from T2 to T3 based on t2.h = t3.h
• There is also a 1:m relationship between t2.s and t3.s

As this implies, in order to add records in T3, a record must exist in both T1 (for t3.s) and T2 (for t2.h).

Currently, I have a form (Main=T1 and sub-form = T3 using a table grid) that allows maintenance (add, update and delete) of both T1 and corresponding T3 records (assuming the relevant records in T2 exist).

However, on the same form I am looking for a way to be able to add a new record into T2 and then be able to create the corresponding T3 record – preferably without using macros (I’m a LibreOffice newbie and not familiar with any of the macro language options).

I have attempted to do this by adding a second sub-form for T2 (enabling the user to (only) add a new T2 record), and this sub-form has its own subsub-form for adding the corresponding T3 record. The current master:slave link for the form:subform is t1.s=t2.s and the subform to subsub-form is t2.h = t3.h AND t2.s = t3.s (although I have also tried just t2.s = t3.s as well as t2.h = t3.h individually ).

However, in saving the new T2 record (via the navigation bar), the database keeps crashing with a generic error: “Due to an unexpected error, LibreOffice crashed. All the files you were working on will now be saved. The next time LibreOffice is launched, your files will be recovered automatically.”

The T2 record is being created in the process and the crash does not occur when I remove the subsub-form. (I’m using LO V4.3.1.2 with embedded HSQLDB tables.)

What am I doing wrong/how can I achieve my objective? Thanks muchly in advance!

[PChitnis]

Your problem is in the database set up. Draw a chart showing relationships using primary and foreign key concepts. You should be able to have a straight line relation from T1 to T2 to T3. The m, h and s fields appear to be Primary and foreign keys linking the tables, while x, y and z fields appear to be the actual data. The s key field in T3 appears to be superfluous, but if you need the info for some obscure reason avoid using the T1-T3 direct connection.