Editing fields in one table for TWO JOINED tables

When I've done this previously I've used the update in the database API to update/insert the joined fields, I just unset the elements from the joined fields which I don't want to get passed to the save on the main table..

Could it be that you're not unsetting a field?
I use the code below to see the data which is getting passed to the update event.
echo "
File:" .basename(FILE) ."
Line:" .LINE . "<pre>" . print_r($values,true) . "</pre>";

I see two issues here.

1. Your SQL code intends to update the whole tbl_Invoices table. I doubt that this is what you looking for. On most cases you need to update a single record or a few records. That can be done by adding a proper WHERE clause to to the SQL query or by using Database API that Chris suggested.

   
   

2. The error message looks incomplete. PHPRunner doesn't have "not editable" error message and database error messages are usually more descriptive. You either didn't post the complete error message here or it is coming from your own code i.e. from BeforeEdit event or from a database trigger.

   
   

The multi-table custom LIST view has headers from two tables. One table requires TWO fields to be editable in the two table custom-view.

Table-one "Dispatch" fields are called "active" and "date".

"Dispatch" table is Master and "tbl_Invoices" is Details table.

I only need to edit TWO FIELDs on the LIST View. Field Names = "active" and "Date".

Both of the field names are in ONE TABLE "Dispatch" in the custom view.

table-two "" has 5 fields that require un-set event.

Link Field is "inv_id".

/// ============================ BEGIN code
$sql = "update othertable set joinedfield=".$values["joinedfield"]." ... "; // which table = othertable? and what replaces the TWO spaces called "joinedfield" ? and what am I doing with " ... " ?
db_exec($sql);
unset($values["joinedfield"]); // do I replace "joinedfield" with "inv_id" ? Should I replace the THREE instances of the word "joinedfield" with Link Field "inv_id" ?
// =========================
// Unset the joined fields so it's just the Edit table fields that get written back.
// the below 5 fields are in table-two "tblClients" in the custom view.
unset($values["LName"]);
unset($values["address"]);
unset($values["PhHome"]);
unset($values["PhCellMs"]);
unset($values["PhCellMr"]);

return true;
/// ============================ END code

Questions:

1. is "othertable" equal to "details table" (tbl_Invoices)
2. is "joinedfield" equal to Link Field (inv_id)
3. Replace all three instances of "joinedfield" with Link Field (inv_id)

What else am I doing wrong? This question might require writing a book.

I only need to edit ONE Record at the time. I def do not require all records to be edited, just one.

Note: previously mentioned error message "Error occurred >> not editable" is the first two words for PHPR 11 error messge.

The complete error message is: error occurred >> Record was NOT edited. The target table Dispatch of the UPDATE is not updatable.

And the error message is expected because I can not get the the manual instructions figured out.

Thank you Chris and Admin for your replies.

Very much appreciated.

There still multiple issues here.

1. You need to learn how to properly write a SQL query that would update something in the different table.
2. There is something strange that is a part of your error message:
   The target table Dispatch of the UPDATE is not updatable.

It doesn't really make much sense as Dispatch table is not even a part of your setup. This is also something that you need to resolve to make this working.

I think there's a bit of confusion on which table is which. If you post the query, that may help to give an idea on which fields belong to which table.

It would also be helpful to see which fields are contained within the $values which is getting passed to the "beforeRecordAdded/Updated" event.

If you drop this line into the "beforeRecordAdded/Updated" event, it will echo out the $values array, we can then see which fields from the query should be getting saved.

If the main table is "dispatch" and the fields of "date" and "active" are in that table, I am then thinking you don't need the other query, you just need to unset the fields in the "tbl_Invoices" which are getting passed in $values.

Thank you, Chris, for your Echo suggestion. I will try it.

Thank you, Admin. (Your SQL code intends to update the whole tbl_Invoices table.) >> Yes, indeed, it deleted over 40k Client Ids from my Invoice table, and luckily, I had a backup for the Invoice Table.

And another thank you to our Admin for the suggestion: (You need to learn how to properly write a SQL query that would update something in a different table.) I am in 100% agreement.

IMO: PHP Runner is a great PHP builder and an excellent program, and I appreciate the exceptional intellectual genius responsible for PHP Runner.

I have been renewing my PHPR registration/license for approximately 15-17 years. I am in love!

If I discover a solution, I will post it here.

Thank you, Sergy, you are the best.