Error updating a joined view

PHPMaker General Discussion (PHPMaker)
1

I’ve got an odd error that i can’t figure out.
I have a time entry system developed, and to help out part of the review process, i have created 2 views in the database (MYSQL) Code Below.
The views are exactly the same, with the only exception being the where clause picks a different sort code to display different employees to different managers.
I’m pulling data from 2 tables, (employees and timecard) code below:

select pci_time.employees.Full_Name AS Full_Name,
pci_time.employees.Sort_Code AS Sort_Code,
pci_time.timecard_payroll.Emp_ID AS Emp_ID,
pci_time.timecard_payroll.Card_ID AS Card_ID,
pci_time.timecard_payroll.Date AS Date,
pci_time.timecard_payroll.Job_Num AS Job_Num,
pci_time.timecard_payroll.Job_let AS Job_let,
pci_time.timecard_payroll.Job_Code AS Job_Code,
pci_time.timecard_payroll.Comment AS Comment,
pci_time.timecard_payroll.Reg_Hrs AS Reg_Hrs,
pci_time.timecard_payroll.OT_Hrs AS OT_Hrs
FROM (pci_time.employees JOIN pci_time.timecard_payroll) WHERE pci_time.timecard_payroll.Emp_ID = pci_time.employees.Employee_no AND pci_time.employees.Sort_Code = ‘2’

The only difference between the views is: Sort_Code = ‘6’ at the end of the second view.
the views work fine.

I have them setup for multi edit
under Code-Table Specific- Edit- Page load, i have $this->UpdateTable = “timecard_payroll”;

I can successfully update the table “timecard_payroll” in the first view, but not in the second. both are setup identically otherwise… I have rebuilt the views , setup a second time with the same results.
“Failed to execute SQL. Error: Can not modify more than one base table through a join view ‘pci_time.view_plumbing_cards’ (1393)” is the error. searching on the forum results indicate my code in the page load event should overcome this issue. Not sure why it does this on one view, and not the other.

Thanks,
Brian

2

The views are called “view_labor_code”, and “view_plumbing_cards”

view_labor_code, is using timecard_payroll as it’s update table ($this->UpdateTable = “timecard_payroll”:wink: without any issues.
–Supervisor uses this to update specific labor codes for a few union laborers prior to payroll.
view_plumbing_cards just does something similar for the plumbing supervisor.
–I made the separate views to make things simple for me to manage and change if either one needed different information from the other(down the road).

I guess technically i should be able to script by user login information the sorting process for each person from a single view.

It’s just odd how this works for one and not the other.
using ver 2020.0.15 at the moment.

3

If you use User Level Security, make sure the plumbing supervisor has Edit permission for the table timecard_payroll.

4

Thanks,
I figured out why it was different. I had several fields set to ‘read-only’ in the first setup…(labor codes) because he only needed to update one field.

the key was that if fields that are not part of the update table are set to read-only, then the update worked in my case.

eg. they needed to update a labor code in the timecard_payroll table. If fields from the employees table are set to read only, the update works fine.
it may also make a difference that the key field from the timecard table is part of the view as well. I am not certain of that yet.

5

brianheim wrote:

if fields that are not part of the update table are set to read-only, then the update
worked in my case.

Make sure in your second database view (the one that still has the issue), you have already unset the field that is not part of table in your UpdateTable, and put this code in “Row_Updating” server event, for example:

unset($rsnew[“YourFieldName”]); // adjust YourFieldName to your actual field name