We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2014-10-24 08:47:33 +0200

gpreg gravatar image

updated 2020-07-28 22:17:28 +0200

Alex Kemp gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 20:03:40.719605

1 Answer

Sort by » oldest newest most voted

answered 2014-11-03 08:30:41 +0200

PChitnis gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-10-24 08:47:33 +0200

Seen: 185 times

Last updated: Nov 03 '14