Copy date from one record to another with offset

[ilnewell]

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.

[shannah]

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;
        }
    }
}


[ilnewell]

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