Edit multiple tables (1:1 relationship)

[israelwebdev]

I'm trying to create an edit form that can update 2 tables with a 1:1 relationship.
I saw this post and tried a few things, but still have a few questions:

1. Using After Save works for me, but how do I populate the fields when the edit form loads?
2. What JavaScript can I use to fill out a Lookup type, given the ID?
3. On a side note, can I prompt the autocomplete of a lookup if I only have a code or description?
4. I also tried using a subform, and setting Addable=No, which works nicely for editing, but how can I get the subform to appear for a New Record?

Thank you. I'm eager to master this software.

[admin]

israelwebdev,

I'm not sure I understand what you are trying to do.

Could you provide a screenshot or more detail?

Steven

[israelwebdev]

Here is #4 above, with Addable=Yes

Bitach Exchange.png

Here is the subform with Addable=No

Bitach Exchange (1).png

As in #1, adding the extra fields contained in the subform into the main form is much better visually, and I can save the form data fine using the After Save with:

Code: Select all

...
$location = nuGetPost('LocationID');
$idno = nuGetPost('IDNo');
nuRunQuery("INSERT INTO Users (UserID, sys_user_id, LocationID, IDNo) VALUES ('#RECORD_ID#', '#RECORD_ID#', '$location', '$idno') ON DUPLICATE KEY UPDATE LocationID = '$location', IDNo='$idno'");

but I don't know how to populate the extra fields' data when Editing an existing record.

Perhaps the solution is generating some Javascript in "Before Open", so #2 is asking how do I populate the Location lookup object in JS?

[admin]

israelwebdev,

Changing the User Form (which is an integral part of nuBuilderPro) will mean you will have problems with NBP updates in the future.

I would create a new Form to do what you want.

And on Before Browse I would populate the new table with records for each user that doesn't yet have a "user_detail" record.

Code: Select all

//-- get users with no user_detail record
$s = "
SELECT * FROM zzzsys_user 
LEFT JOIN user_detail ON zzzsys_user_id = use_zzzsys_user_id
WHERE user_detail_id is null
";

$t = nuRunQuery($s);

while($r = db_fetch_object($t)){

   $u = nuID();
   $q = "INSERT INTO user_detail (user_detail_id, use_zzzsys_user_id) VALUES ('$u', '$r->zzzsys_user_id')";  //-- add a user_detail record for users who don't yet have one.

   nuRunQuery($q);

}

This will give you your 1:1 automatically and you will be able to save your "user_detail" fields normally.

Steven

[alvittos]

This is very usefully for me, thank you!

[israelwebdev]

Thank you for answering my Q#4.
I made a copy of the user form from the DB records (forms, objects, browse) so as not to break updates, and using a separate table for extra data as well with this in mind.

I found the (undocumented) functions nuLookupID and nuLookupCode - is it safe to use these?
It seems I can do something like

Code: Select all

$('#CustomerID').val('f373bc1f-0eaa-40d5-9800-fdae4f4a8f88');
nuLookupID($('#CustomerID')[0]);

or actually calling

Code: Select all

$('#CustomerID').val('bd43158e-de5b-4cfe-b9cd-ecc4794b7023').change()

in this case would probably be better.

For Q #1, should I need to load or fetch extra DB data for a form, is nuAjax a good solution?

1 more comment: I like the "nu" prefix for your functions, but maybe the JS and PHP functions should have some differentiating property. Food for thought.

[admin]

You can use nuLookupID and nuLookupCode safely.

To get other data you can use the Display Object to get a field from another table that has some relation to this record.

Steven

[israelwebdev]

Could the Display Object be made editable (to be saved in conjunction with an After Save action) ?

[admin]

i,

Try this..

Code: Select all

function nuLoadEdit(){

   var id = 'invoice_number';
   $('#'+id).removeClass('nuReadOnly');
   $('#'+id).prop('tabIndex','50');  //-- (or whatever)

}

Steven