Updating multiple records with Row_Updating event

[thalassa3003]

Hi,

I have a listpage 'vwauditbysupplierproductslist' based on a view with the 'Multi-page'-update enabled.
When I selected multiple records on the listpage and when I than select the button 'update selected records', only 1 record is updated (the first). The other records are not updated.

I have the following script on the 'Row_Updating' server event:

$sql = "
UPDATE auditDatasets
SET
assignedTo = '". $rsnew['assignedTo'] . "'
WHERE id = '". $rsold['auditDatasetsID'] . "';
";
ew_Execute($sql);
$this->setSuccessMessage("Done");
$this->Page_Terminate("vwauditbysupplierproductslist.php"); // return to List page
return FALSE;

=============================================================

Can someone please assist, so I can multi-update all selected records of this view?

Thx!!

[sangnandar]

1. Why do you set return FALSE on this function?
2. I guess the culprit is on this line,
   $this->Page_Terminate("vwauditbysupplierproductslist.php"); // return to List page
   It caused the page to be left off at/after the first update run. The rest of the records then can't do update process since the page already left off.

[thalassa3003]

sangnandar wrote:

1. Why do you set return FALSE on this function?

Because if you use 'return TRUE', the function tries to update the view, resulting in the error:
"Failed to execute SQL. Error: The target table vwauditbysupplierproducts of the UPDATE is not updatable"

sangnandar wrote:
$this->Page_Terminate("vwauditbysupplierproductslist.php"); // return to List page

It caused the page to be left off at/after the first update run. The rest of the
records then can't do update process since the page already left off.

Thats makes sence indeed. However removing the line does not result in the desired effect: only the value of the first row is updated and not values of all selected rows.

I think I am doing still something wrong. Any suggestions? Maybe using a different function server event?

[kirondedshem]

Because if you use 'return TRUE', the function tries to update the view, resulting in the error:
"Failed to execute SQL. Error: The target table vwauditbysupplierproducts of the UPDATE is not updatable"

In database rules, insert or update can only be run on a table or an "editable view" this is a view where all of its fileds are coming from the same table. So obvioulsy the view you connected to phpmaker and generated add and edit pages is not an editable view(google editable and non editable views to get clear understanding).

When you connect a view to phpmaker it always warns you when you try to enable add & edit page to ensure that your view is editable. If you are using phpmaker add & edit pages always ensure the view is editable.

NOTE: It would be better if you posted your table schema so as to see how your view looks and how it comes about as well and what you wish to update so others can get a better picture. Give clear examples coz maybe it can be aprocahes in a better way. But nway.

Possible solutions:

1.(recommended if applicable), if all the fileds you want to update belong to the same table but you just want to avoid updating some felds. Then either create a view from that table with only the fileds you wan tot update from that table. Now if you connect it to phphmaker it will be editable.

Now if you want to update other related fields from other tables. Just ensure the fields you included in above view have neccessary links(eg they are maybe foreign keys) to the other tables, that way you can run your initial update in row_udating and it should work.

1. Incase Some of the fields you had in your NOn-editable view were to help you see information from other tables or autocalculated information, eg Although you only wanted to update the sales record you joined to the customer table from the main sales table so that you can show him the customer name, or you wanted extra fields on the form to use in autocompleting some values etc etc or something similar.
   You dont need to include them in the sql query to create the main view.
   Instead create the initial editable view from main table of interest. Then add custom fields(read about them in help), because custom fields are still available on add and edit pages, Thier values are still available during row_inserting, row_updating,row_inserted & row_updated events BUT they are not included in the update query.
   So you can create as many custom fileds as you want and use them to help user fill up neccessary info on the form. eg you can make them use lookup, parent child filering etc. As well as assign them static values during loading of add / edit form.
   After this you can use thier values to run any other update queries as you want.

NOTE:Using the above solutions and creativity am sure you can do your required multiupdate query.

[sangnandar]

thalassa3003 wrote:
Because if you use 'return TRUE', the function tries to update the view, resulting
in the error:
"Failed to execute SQL. Error: The target table vwauditbysupplierproducts of the
UPDATE is not updatable"

You can update view using phpmaker, for only one basetable of the view.
This code within Page_Load() of Add/Edit/Delete page would do the job.
$this->UpdateTable = "<yourbasetabletobeupdated>";
and unset the rest of the fields within Row_Inserting()/Row_Updating()
unset($rsnew["<FieldNotBelongToBasetable>"]);

---

I'm guessing, the next culprit would be the return FALSE; itself.
Here's the thing. If you run multi-updates it should be: either all of them pass or not at all. Because, let's say, if you have 5 records, 2 records passed and 3 didn't, how do you know which one did/didn't pass? I think ALL OR NONE rules is applied on this thing. So once it FALSE, the rest would fall.
But how come the first row get updated? Because the ew_Execute() has no condition, it blindly fired until the code arrive at the FALSE line.

Using ew_Execute() is always tempting but better to enclose them with condition like,
Row_Updating() {
if (something) {
ew_Execute();
return TRUE;
}
}
to make sure that it will not blindly fired.