Form based on muliple tables

Code Examples Form
,
#1

Hi,

In mysql I did create a view based on 3 tables. In SC I designed a form based on that view. When I do open the form and try to edit the data I do receive the error message: "Can not modify more than one base table through a join view "

Do you know how to solve this?

Thanks,

Marco

#2

[QUOTE=Marcot;19679]Hi,

In mysql I did create a view based on 3 tables. In SC I designed a form based on that view. When I do open the form and try to edit the data I do receive the error message: "Can not modify more than one base table through a join view "

Do you know how to solve this?

Thanks,

Marco[/QUOTE]

This is actually not a SC issue, this message is caused by MySQL. The only way to solve it (afaik) is to breakup the insert on the view into 3 different inserts. The onbeforeinsert is not a valid location afaik you cannot prevent the insert on the view to be executed. So I guess you need to use the onvalidate event where you can ‘replace into’ instead of ‘insert into’ so that you are able to insert/modify data and then do an sc_redir to prevent the other events being triggered. Bit fishy though.

1 Like
#3

@Marcot: a view is always readonly and can’t update.

#4

@Marcot, aducom, RHS, I have the same issue. I am able to update the view while in MySql but when i attach to a form in SC it is giving that error message, please help if you get a solution., Jephta

#5

@RHS: http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

1 Like
#6

@aducom, my view only has two tables and i am able to edit/update it in MySql. But in SC i get the message “Error
Error updating database - Can not modify more than one base table through a join view”, please help

#7

@aducom : yes …, but not in real live (see the error message in post #1).

#8

It looks like SC use all the fields of the table/view in a form when applying SQL INSERT/UPDATE. When updating a view, only one underlying table is allowed. I think that why you can update in MySQL but not SC.

User Giu taught me to avoid the SQL Insert/Update by :
Edit Fields -> DB value (Insert) / (Update) -> Calculated by the database.

See if it can solve your problem.

Although it work, it’s far from perfect. Like I use SQL trigger update some fields, I always need to do the above setup even the field/s not in the form.

What is the pros to use all the fields in SQL INSERT/UPDATE ?

Regards,

Almond Wong

1 Like