Create/update child records

[Tinka]

Hi

I had another thread in custom code about how to create/update child records in a subform.
Updating is no problem when the child record (foreign key = record-id) is already existing since the sql statement gets the right child record by inner join and FK in the sql statement on both the form and subform.

But if I fill the data field, when I want to create a new, not previosly existing child record on the subform Nubuilder crashes. I guess I need to add the FK from the parent to this child record at the same time somehow.
How to achieve this?

You have no examples of tables linked by foreign keys in a subform in the sample_b.sql example or the tutorial videos, only lookups of existing child records/tables (customers, products, invoice items).
My sql database uses FK constraints in the Inno-db structure.

BR, Tinka

[nuGent]

I'm still experimenting with subforms but you do not need custom code to add new child records in a subform, nuBuilder does it for you (like in MSAccess). It uses the parent's primary key and adds it to the new child record as a foreign key. First, check that you have the two keys correctly set on your subform tab (see my screenshot of the settings for a working customer/invoice subform) and also see this post about a small problem and a workaround with the subform wizard http://forums.nubuilder.cloud/viewtopic.php?f=13&t=8363

You may need to turn on error messages to debug further but it sounds like something simple, hopefully as above. Good luck.

John (in the UK)

[Tinka]

Hi John

yes, my subform is set up exactly as yours.

ScreenShot516.jpg

But the form field I want to add is not showing when I want to make a new entry (because the sql query does not return a result?). It makes no difference if it is addable or not.

I can edit existing child records without a problem.

ScreenShot517.jpg

ScreenShot518.jpg

Where do I turn the debug messages on?

Tinka

[Tinka]

Hi

I figured out the problem: I had set foreign key constraints in the mysql db (Inno db) - as soon as I removed these the child records are created/updated/deleted accordingly by Nubuilder. (Before the parent record would be added but without the child record). But, after save the subform field is not updated/showing the information just entered - a new problem.

Tinka

[admin]

Tinka,

What does the sql statement for this subform look like?

Steven

[Tinka]

The first picture above shows the subform, I just added a table qualifier c:

SELECT * FROM chromosome c
where c.chrom_geneidFK = '#record_id#'

[admin]

Tinka,

Try '#RECORD_ID#'

(capitals)

Steven

[Tinka]

Yes, that worked! Thank you.

[admin]

cool.