Page 1 of 2
Subform, Join and View
Posted: Thu Jul 15, 2021 6:55 pm
by BDemeuse
Hi,
I need to add additional information(contact data from tr_contact related to the member) on an edit form on the table 'tr_membre';
However, these 2 tables are not directly linked together via a foreign key.
Indeed, I have got the browse on
Code: Select all
select * from tr_membre
join tr_role on rl_external_id = mb_id and rl_role = 'Membre'
join tr_contact on rl_contact_id = ctt_id
Looking through the forum, I have seen that I could create a view and link the subform to.
It does not work also; nothing is display as before
Also, I have create a browse&edit on the view. Updates work well but not add function.
It always uses the same key... this already exists.
For me, both solutions (subform or browse&edit on the view) could be fine if I can add and update membre and associated contact data.
I hope my question is clear enough
Thanks in advance
Bri
Re: Subform, Join and View
Posted: Thu Jul 15, 2021 9:25 pm
by kev1n
Could you create a minimal example containing these 3 tables and some dummy data and upload the db dump here?
Re: Subform, Join and View
Posted: Fri Jul 16, 2021 9:37 am
by BDemeuse
Hi,
I will do it.
Just to mention ... Yesterday I continue to investigate by adding a record on the 'tr_membre' only and I had a problem because browse was working on join with a foreign keys.
Thanks in advance
Bri
Re: Subform, Join and View
Posted: Fri Jul 16, 2021 4:26 pm
by BDemeuse
Hi,
Here is the dump with the small example.
Thanks,
Regards,
Bri
Re: Subform, Join and View
Posted: Sat Jul 17, 2021 6:19 am
by kev1n
I've added your tables to nuBuilder and created 3 forms. Could you complete it by turning it into a working example?
Re: Subform, Join and View
Posted: Mon Jul 19, 2021 5:00 pm
by BDemeuse
Hi,
To make it simple in order to test, I have created a new schema, imported and updated nuconfig.php.
However, I have got "Connection failed: SQLSTATE[HY000] [1044] Access denied for user ''@'localhost' to database 'testnubuilder'"
Thanks,
Regards,
Bri
Re: Subform, Join and View
Posted: Mon Jul 19, 2021 5:38 pm
by kev1n
Restart the browser after modifying nuconig.php. Also make sure the user exists and the password is correct.
Re: Subform, Join and View
Posted: Tue Jul 20, 2021 5:20 pm
by BDemeuse
Hi Kev1n,
I have tried different solutions. You will find it into the dump attached
-) (Browse&)Edit on the tr_membre with a subform to tr_contact or tr_v_membre_contact (view)
the subform to tr_contact/tr_v_membre_contact does not work because there is a "indirection" via the tr_role table.
The foreign key is in this table and not in the initial one, tr_membre
-) (Browse&)Edit on the tr_role
I have tried because the foreign key (rl_contact_id) to tr_contact is in this table but it does not work.
-) (Browse&)Edit on the view (tr_v_membre_contact)
Update works but not the add function.
It seems that I cannot update more than on tables with a view.
I am not an sql expert and I am just starting working with view
Do you think that I could solve between different possibilities ?
My DB model is too complex ??
Solution via custom code ?
Thanks in advance,
Regards,
Bri
Re: Subform, Join and View
Posted: Wed Jul 21, 2021 7:23 am
by kev1n
At the moment, unfortunately, I don't see any way to do this without using custom code.
PHP Example code in BE (Before Edit), to load the column
sob_all_top of the zzzzsys_object table into an Object of an Edit form.
Code: Select all
function getSQL() {
$sql = "SELECT sob_all_top FROM `zzzzsys_object` WHERE `zzzzsys_object_id` LIKE 'nu5bad6cb341fdfc3' ";
return $sql;
}
$t = nuRunQuery(getSQL());
$r = db_fetch_object($t);
$v = base64_encode($r->sob_all_top);
nuAddJavascript(" $('#sob_all_top').val(atob('$v'));");
You will need a Number Object with Object ID sob_all_top on your form.
And then in the AS (After Save) event, to write the field back to the DB:
Code: Select all
$sql = "UPDATE zzzzsys_object SET sob_all_top = #sob_all_top# ";
nuRunQuery($sql);
Re: Subform, Join and View
Posted: Wed Jul 21, 2021 9:28 am
by BDemeuse
Many Thanks.
I will try.
Regards,
Bri