This topic is locked

Editing table with custom view

10/16/2015 1:23:14 PM
PHPRunner General questions
N
nti author

Table (tblClients) utilized with custom view, utilizing "specific field value" (RepName) from add-on table (tbl_Invoices) within custom view.
I use this "specific field value" (RepName) within phpr, Security Settings, Advanced security settings, "Users can see and edit their own data only" option.
The "specific field value" (RepName) from "Query Designer Additional Table" is not editable, is marked as read only in Visual Editor, and not editable at phpr Fields Options.
How can I avoid the php error: (<<< Record was NOT edited >>> Unknown column 'tbl_Invoices.RepName' in 'where clause') while editing the (tblClients) custom view ?
Is there an event script I can utilize or another method within Query Designer that I can utilize to prevent php error from occurring?
I think the (Unknown column 'tbl_Invoices.RepName' in 'where clause') is connected to>>> "Users can see and edit their own data only"
I do not utilize (tbl_Invoices.RepName) in my Query "Where" Clause., see below.
I am attempting to edit just the fields in (tblClients).
Any suggestions?
My Query Designer Custom View below:
SELECT

Lu_InvStatus.status,

tblClients.FName,

tblClients.LName,

tblClients.address,

tblClients.Zip,

tblClients.City,

tblClients.State,

tblClients.PhHome,

tblClients.PhCellMs,

tblClients.PhCellMr,

tblClients.YpBook,

tblClients.editdt,

tblClients.editor,

tblClients.editip,

tblClients.SvcCompany,

tbl_Invoices.RepName,

tblClients.client_id,

tbl_Invoices.Active

FROM tblClients

LEFT OUTER JOIN tbl_Invoices ON tblClients.client_id = tbl_Invoices.client_id

LEFT OUTER JOIN Lu_InvStatus ON tbl_Invoices.Status = Lu_InvStatus.DynId

WHERE (Lu_InvStatus.status !="Complete" AND Lu_InvStatus.status !="Cancelled") AND (tbl_Invoices.Active =1)

ORDER BY tblClients.client_id DESC

lefty 10/16/2015



Table (tblClients) utilized with custom view, utilizing "specific field value" (RepName) from add-on table (tbl_Invoices) within custom view.
I use this "specific field value" (RepName) within phpr, Security Settings, Advanced security settings, "Users can see and edit their own data only" option.
The "specific field value" (RepName) from "Query Designer Additional Table" is not editable, is marked as read only in Visual Editor, and not editable at phpr Fields Options.
How can I avoid the php error: (<<< Record was NOT edited >>> Unknown column 'tbl_Invoices.RepName' in 'where clause') while editing the (tblClients) custom view ?
Is there an event script I can utilize or another method within Query Designer that I can utilize to prevent php error from occurring?
I think the (Unknown column 'tbl_Invoices.RepName' in 'where clause') is connected to>>> "Users can see and edit their own data only"
I do not utilize (tbl_Invoices.RepName) in my Query "Where" Clause., see below.
I am attempting to edit just the fields in (tblClients).
Any suggestions?
My Query Designer Custom View below:
SELECT

Lu_InvStatus.status,

tblClients.FName,

tblClients.LName,

tblClients.address,

tblClients.Zip,

tblClients.City,

tblClients.State,

tblClients.PhHome,

tblClients.PhCellMs,

tblClients.PhCellMr,

tblClients.YpBook,

tblClients.editdt,

tblClients.editor,

tblClients.editip,

tblClients.SvcCompany,

tbl_Invoices.RepName,

tblClients.client_id,

tbl_Invoices.Active

FROM tblClients

LEFT OUTER JOIN tbl_Invoices ON tblClients.client_id = tbl_Invoices.client_id

LEFT OUTER JOIN Lu_InvStatus ON tbl_Invoices.Status = Lu_InvStatus.DynId

WHERE (Lu_InvStatus.status !="Complete" AND Lu_InvStatus.status !="Cancelled") AND (tbl_Invoices.Active =1)

ORDER BY tblClients.client_id DESC


Make sure the security settings are setup for all tables in join correctly or it won't give accurate data . Also as a test go to appsettings and set debug to true and run app . to see where query has a problem. I think the problem is on this line

LEFT OUTER JOIN tbl_Invoices ON tblClients.client_id = tbl_Invoices.client_id as if security on client table is setup different you cannot grab data from tblInvoices.

Sergey Kornilov admin 10/16/2015

When your edit page is based on query that pulls data from multiple tables you need to make sure that fields from joined tables are removed from UPDATE SQL query. You need to unset() all fields from joined tables using BeforeEdit event:

http://xlinesoft.com/phprunner/docs/update_multiple_tables.htm