Xataface Forums

I am not sure how to do this but here are the details.
I have 2 tables with a one to many relationship.
The first table has the following fields

Table name "main"
Cardno
frequency
duedate
...
...
etc

Second table "subtable"
Cardno
mtnce_date
...
...
etc

What I would like to do is, when the subtable field "mtnce_date" is updated, I would like to update the field in the "main" table called "duedate" with mtnce_date+frequency. The frequency field contains a number ie "12" which is the number of months between service periods, the other two are date field of course.
I think I should use a trigger but I am not sure if I should use SQL or PHP or how to reference the fields and of course only update the related record.

Thank you in advance for any help.

Both will work fine. If you want to do it with PHP (i.e. a Xataface trigger), you would use the beforeSave() trigger:

Code: Select all

class tables_subtable {
    function beforeSave($record){
        $mainRecord = df_get_record('main', array('main_id'=>'='.$record->val('main_id')));
        if ( $mainRecord ){
            // main record was found ok
            $freq = $mainRecord->val('frequency');
            $dueDate = date('Y-m-d H:i:s', strtotime($record->strval('mtnce_date').'+'.floatval($freq).' month'));
            $mainRecord->setValue('duedate', $dueDate);
            $res = $mainRecord->save();
            if ( PEAR::isError($res) ) return $res;
        }
    }
}


That works perfectly, Thank you. This is an excellent framework and I look forward to its continued development, Well done.