Subform, Join and View

Questions related to using nuBuilder Forte.

Subform, Join and View

Unread postby BDemeuse » Fri Jul 16, 2021 2:25 am

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
Last edited by kev1n on Wed Jul 21, 2021 5:13 pm, edited 1 time in total.
Reason: Added Code Tags
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby kev1n » Fri Jul 16, 2021 4:55 am

Could you create a minimal example containing these 3 tables and some dummy data and upload the db dump here?
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby BDemeuse » Fri Jul 16, 2021 5:07 pm

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
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby BDemeuse » Fri Jul 16, 2021 11:56 pm

Hi,

Here is the dump with the small example.
Thanks,
Regards,

Bri
Attachments
Dump20210716nuBuilder.txt
sql extension remplaced by txt extension ... it seems that sql extension is not allowed ??
(6.54 KiB) Downloaded 15 times
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby kev1n » Sat Jul 17, 2021 1:49 pm

I've added your tables to nubuilder and created 3 forms. Could you complete it by turning it into a working example?
Attachments
test.zip
(150.46 KiB) Downloaded 15 times
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby BDemeuse » Tue Jul 20, 2021 12:30 am

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
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby kev1n » Tue Jul 20, 2021 1:08 am

Restart the browser after modifying nuconig.php. Also make sure the user exists and the password is correct.
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby BDemeuse » Wed Jul 21, 2021 12:50 am

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
Attachments
DumpTestNuBuilder2007.txt
(755.63 KiB) Downloaded 13 times
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby kev1n » Wed Jul 21, 2021 2:53 pm

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);
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Subform, Join and View

Unread postby BDemeuse » Wed Jul 21, 2021 4:58 pm

Many Thanks.
I will try.
Regards,

Bri
BDemeuse
 
Posts: 20
Joined: Mon Apr 12, 2021 10:39 pm
nuBuilder Version: 4.5

Next

Return to General

Who is online

Users browsing this forum: No registered users and 16 guests