Subform, Join and View

[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

[kev1n]

Could you create a minimal example containing these 3 tables and some dummy data and upload the db dump here?

[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

[BDemeuse]

Hi,

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

Bri

[kev1n]

I've added your tables to nuBuilder and created 3 forms. Could you complete it by turning it into a working example?

[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

[kev1n]

Restart the browser after modifying nuconig.php. Also make sure the user exists and the password is correct.

[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

[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);

[BDemeuse]

Many Thanks.
I will try.
Regards,

Bri