Change field in one table updates field in another table

[manifold]

Hello all,

I have a database with two tables: vehicles, and workorders. The default 'status' field in the vehicle table is 'available'; however, if a workorder is filled out for that vehicle, I'd like the new workorder entry to be able to adjust the status of the vehicle as appropriate. This way, the department owning the vehicle can see all the details regarding the vehicle problem report and repair status, but other departments (who can look at the vehicle table) would only see the current availability status. Thus, entering a workorder in the workorder table allows a user to automatically change the status in the vehicle table.

Any suggestions?

TIA,
-manifold

[manifold]

Would something like this work?

function afterSave(&$record){
$record->setValue('workorder.inservice'), $record->val('apparatus.status');
}


Thus, after the new record in the workorder database is save (including the field 'inservice', indicating if the vehicle is available or not), the 'status' field in the separate apparatus table would be changed.

[ADobkin]

I'm not sure if the syntax you proposed would work, but someone else just recently posted a very similar situation. Refer to this post for more details:

Adding Multiple Relationship Records w/one form
viewtopic.php?f=4&t=6406

Per Jean's reply, you could do something like this:

Code: Select all

function afterSave(&$record){
   $this->app =& Dataface_Application::getInstance();
   $otherrcontent=$record->val('field');
   $result= mysql_query("UPDATE table set field='$content' where field='$otherrcontent'", $this->app->db());
}

[manifold]

Something like this seems like it should work... but doesn't... yet...

Code: Select all

UPDATE apparatus
INNER JOIN workorder ON workorder.vin = apparatus.vin
SET apparatus.status = workorder.inservice
WHERE workorder.vin = '$record';



While the default setting for a vehicle (apparatus) is 'available', any new work order can indicate that
the vehicle is now out-of-service (OOS). When the work is completed, the work order can be updated,
and the availability can indicate that the vehicle is now available.

At least, that is my goal!

[ADobkin]

I think there are a couple of issues with that approach. One is that the $record variable is an internal reference to the Xataface record, which is not the same as the primary key value in the database as you are using it to represent the workorder.vin. Also, I don't think you need to do a join since you are doing the update entirely on a separate table.

Perhaps you could try this (in the delegate class on the workorder table):

Code: Select all

function afterSave(&$record){
   $this->app =& Dataface_Application::getInstance();
   $vin = $record->val('vin'); // refers to value of workorder.vin
   $inservice = $record->val('inservice'); // refers to value of workorder.inservice
   $result = mysql_query("UPDATE apparatus SET status='$inservice' where vin='$vin'", $this->app->db());
}

[manifold]

In the various interations of this that I've tried, this throws an error.

From the wiki entry on the 'beforeSave' trigger (http://xataface.com/wiki/beforeSave), I'm trying variations of this:

Code: Select all

class tables_workorder {
    function afterSave($record){
    $this->app =& Dataface_Application::getInstance();
        $record->setValue('apparatus.status',
            $record->val('workorder.inservice') WHERE 'workorder.vin' = 'apparatus.vin'
        );
    }
}

But... so far this isnt working either!

[manifold]

Closer!

This SQL query works (in phpAdmin):

Code: Select all

UPDATE apparatus, workorder
SET apparatus.status = workorder.inservice
WHERE apparatus.vin = workorder.vin;

I think there are two last steps:
1) Specify just the specific record that is changed (added, or edited). The query above does seem to only change apparatus record when I change a workorder record, but it seems like I should be explicitly specifying this one record only.

2) Getting this sql query into the proper xataface afterSave trigger format...

[manifold]

This works, though I can't help but feel that could be made better/more robust:

Code: Select all

class tables_workorder {
     function afterSave($record) {
          $result = mysql_query("UPDATE apparatus, workorder SET apparatus.status = workorder.inservice WHERE apparatus.vin = workorder.vin");
        }
}

[ADobkin]

Glad to hear you have a working solution. The reason this works is because it is updating ALL of the records in the apparatus table every time a single work order is saved. This may affect your database performance and possibly cause other issues (record locking, inaccurate last modified time stamps, etc.)

I still think the earlier code I posted should work as well, and it is a specific query to update only that one record. I haven't tested it myself, but it is similar to other working examples. Did you try an exact copy and paste of that code? One thing in particular is that I noticed your next post did not include the ampersand (&) for the $record variable. It should be &$record, not $record in the function call. What was the error you received in this case?

[manifold]

ADobkin's solution in fact worked perfectly! Many thanks, ADobkin!

The code I had posted earlier was indeed flawed, though clumsily functional...

Here is his code, again:

Code: Select all

function afterSave(&$record){
   $this->app =& Dataface_Application::getInstance();
   $vin = $record->val('vin'); // refers to value of workorder.vin
   $inservice = $record->val('inservice'); // refers to value of workorder.inservice
   $result = mysql_query("UPDATE apparatus SET status='$inservice' where vin='$vin'", $this->app->db());
}

A great product, and a great supporting community....

-manifold