How to populate foreign key on child record creation

[db]

I am sure this is an easy answer that I am missing. I have lots of experience with Relational DBs but I am new to Libre Base. I have created a Form called contact that has a source of the table CONTACT. The contact table has a foreign key of ADDRESSID that references the ADDRESS table. I then have a sub form that has a source of the ADDRESS table. The linked fields are set to ADDRESSID.

The problem happens when I create new records. I type data in the main form for the contact and then in the subform for the address. When I save the record the primary key is created in the ADDRESS table but then it is not populated as a foreign key for the newly created contact in the CONTACT table.

Any help would be greatly appreciated.

Using libre office Version: 4.3.5.2

[frofa]

db:

Which way round does the 1:n relationship go? Is it one CONTACT to multiple ADDRESSES or one ADDRESS to multiple CONTACTS? (You seem to be going for the latter case in your form design). If it’s the former shouldn’t you have contact_id as the FK in your sub form? Then if you set the master-slave field as contact_id, that value should propagate correctly (as an FK) to the newly created ADDRESS record in the sub form.

---

Further to your comment #1 below:

Since, as you say, you have a 1:n relationship between ADDRESSES and CONTACTS, it would seem to make more sense to consider your ADDRESSES table the parent/master in the main form and CONTACTS the child/slave table in the sub-form. Thus when selecting an ADDRESS in the main form list, you would see a filtered list of all the CONTACTS for that address. Also, when a NEW CONTACT record (in the sub-form list) is created, this NEW record will automatically ‘inherit’ the parent address_id PK as its FK.

Notes: Exactly them same scheme would apply to your other form showing VENDOR information. These form schemes might need re-thinking if your CONTACTS can have more than one assigned ADDRESS (i.e. the relationship between CONTACTS and ADDRESSES is in fact n:n).

---

Further to your comment #2 below:

As far as I can see, this doesn’t change what I suggested above. Logically, you must add a new address to the list in the main form before its key can be added to a new CONTACT record in the slave-linked sub-form as a FK (and I think this is enforced by the underlying HSQLDB engine). Base should do this automatically for you when you create the new CONTACT record (provided the parent record of the address list has been created and is selected). You might need to set Add data only to YES in the form properties to make this work. (Although I think it should work without doing that).

SUMMARY: Essentially, you just need to swap the table assignments between your master main form and slave sub-form.

Note: You could also have a separate sub-form on the same level of your data-entry FORM (i.e. a child of the ADDRESS-linked main/master form) linked to your VENDOR table so you could create a new VENDOR on the same form.

[db]

I have one ADDRESS for many CONTACTs. I also use the ADDRESS table as a child to several other tables in my database such as VENDORS, etc. If I flip the relationship thie issue that I see arising is that I would have to have multiple foreign keys on the ADDRESS table, CONTACTID, VENDORID, etc.This besides from making it a one to many in the other directions this would cause more issues with other forms.

[db]

The vendor can only have one address and the contact can only have one address per record hence the reason to have the foreign key addressid on both the vendor and contact table. This seems like a standard relationship where I can have many contacts that use the same address. The trick is when I create the contact I want to also be able to create the child address record and populate the primary key of the address in the contact table. How would base normally handle a situation like this.