How can I add linked database records to multiple tables using a form?

[Frungi]

I have a database where one table has a foreign key matching an auto-incremented index in another table. I want to use a Base form to add a record to both at once, automatically including the foreign key for the new record in the second table. Is this possible?

Simple illustration: Table A has six rows, and table B has four rows. In a single form entry and without knowing either number, I want to add rows that look like this:

           A                          B
+----+------+-----------+    +----+-------------+
| id | b_id | name      |    | id | content     |
+----+------+-----------+    +----+-------------+
|  7 |    5 | blah blah |    |  5 | yadda yadda |
+----+------+-----------+    +----+-------------+

[frofa]

I want to use a Base form to add a record to both at once, automatically including the foreign key for the new record in the second table. Is this possible?

Logically the table B record must exist before a linked table A record is created (assuming you are enforcing primary and foreign key relationships, you probably would get an error if you tried doing it the other way round).

That said, you should be able to setup a main form sub-form pair with appropriately configured master and slave fields defined in the sub-form data properties to ‘automatically’ add a new record to table A with the appropriate b_id value as you describe once you have selected a row in your table B. In Base this is all ‘built-in’ to form/sub-form functionality. (You might also use a LIST BOX to select a row in table B (by content) and the ID value would be written to table A in a newly added row/record.)

When you say ‘automatically’, you still would have to add a row to table B if it didn’t exist already, and also enter the value of the name field in the linked table A row. Do you mean something else by ‘automatically’?

[Frungi]

I meant that I wanted to be able to type into the form (in my simplified example) both name and content and have the subform add the table B row, and then the form add the table A row. If I understand your answer, this is not possible, but please respond to confirm this. Thank you.

[frofa]

I cannot see how you can just ‘type in’ values for your name and contents fields without first creating new rows (records) in your two tables to ‘hold’ these values (where would you type them in?). But maybe I don’t really undertand what you are trying to achieve.

[Frungi]

Into the form. A Base form for adding new rows.

[frofa]

Are you trying to create a new row in the second table (i.e. the related table) ‘automatically’ (i.e. ‘triggered’ by adding a new row to the first table)? Maybe that would need a macro (coded) solution?

[Frungi]

I want the subform to create the new row in the second table, and then the main form to create a new row in the first with the foreign index, except all in one go. I tried to set this up with subform master/slave fields, and both rows are added when I enter new data for both, but the foreign index is blank.

I’m willing to accept that it’s impossible, but that would make me sad.

[frofa]

A form with a properly configured sub-form should allow automatic insertion of the foreign index/key in table B. I would suggest you look carefully at the way you have configured your sub-form data properties accessed via the form navigator. The link master-lave link fields must be correctly set. A screenshot of your FORM NAVIGATOR window (showing all the elements of the hierarchy and the data tab for the sub-form) might help diagnose your problem further.

[mariosv]

Maybe samples in documentation can help:

https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook

[Frungi]

If there was anything in there about adding from a form and subform at the same time, I couldn’t find it. Did I miss it?

[bencomp]

Could you be more specific, @mariosv?