Copy date from one record to another with offset

Archived from the Xataface Users forum.

ilnewell — Thu Dec 16, 2010 9:49 am

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 — Thu Dec 16, 2010 11:17 am

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 — Fri Dec 17, 2010 3:15 am

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