Dynamically update calculated field

[wyhauyeung]

When adding records in a form of my table, is it possible to have one calculated field instantly updated when I am updating the dependant fields?

say, in the form, field c is field a plus field b, when I am editing field a or field b, field c will be updated instantly so that I can also see its updated calculations in the form..

Regards,

[shannah]

You can do this in the beforeSave() trigger.

Code: Select all

function beforeSave(&$record){
   $record->setValue('field1', $record->val('field2')+$record->val('field3'));
}


[grageot]

I have the same problem but with fields of different tables
field1 is opend with a relationship.ini from tableB => tableA (common field is "id")
I have the field1 in the new added record belonging to tableA and I want to update the filed2 in tableB with :

Code: Select all

tableB.field2 = tableB.field2 + tableA.field1

What is the code of beforeSave() trigger and in which file.PHP (tableA or tableB) ?
Thanks for help
Guy

[shannah]

Since this field depends on values in both tableA and tableB, you would need to update the value whenever either table is changed. I suspect there is probably a better way to skin this cat than to store this value in one of the tables as this is duplication of data. Can you give me a more concrete example of what these fields are used for and how the structure of the tables are?

-Steve

[grageot]

Hi Steve and thank you for the response.
I manage a base of people donating to an NGO,
- i have a table 'donateurs' (id) with the records concerning bio and total of donation per year field 'Vers_10'
- a table 'transac10' (Id_Dons) which records each donation 'MontantDon10' (maybe several per year)
- a relationship from 'donateurs' to 'tranasc10' for recording the donation through a view table 'id.Id_Dons'
I want : summarize the field 'donateur.Vers_10' after recording the last previous donation : 'donateurs.Vers_10 = 'donateurs.Vers_10' + 'transac10'.MontantDon10'.
I think i can use a trigger but d'on't know how to do it between different tables.
I really appreciate your Xataface more simple than "Joomla + LFRM".
Best reagrds

[shannah]

Ok.. You could do it in a trigger. Keep in mind that if you were to make changes outside of Xataface the trigger wouldn't be fired and your totals could go out of sync.

Issues to consider in the trigger:

1. If a donation amount is changed, then you need to be able to update the total accordingly.
2. If a donation is deleted, then you need to update the total accordingly.

Hence, what I would do is run the total calculation each time a donation is added, edited, or deleted. This will ensure that it keeps it in sync (rather than just dealing with the increment).

For this I would use the afterSave() and afterDelete() triggers in the donations table.

You could create a function say (update donations) that would update the donations totals. Then call this function from the afterSave() and afterDelete() triggers.

e.g.

Code: Select all

function updateDonationsFor($donateurID){
    $res = mysql_query("select sum(donationAmount) from donations where donateurID='".addslashes($donateurID)."'", df_db());
    if ( !$res ) throw new Exception(mysql_error(df_db()));
    list($totalDonations) = mysql_fetch_row($res);
    @mysql_free_result($res);
    $res = mysql_query("update donateurs set totalDonations='".addslashes($totalDonations)."' where donateurID='".addslashes($donateurID)."' limit 1", df_db());
    if ( !$res ) throw new Exception(mysql_error(df_db()));
}


Then your donations delegate class would contain triggers:

Code: Select all

function afterSave(&$record){
    updateDonations($record->val('donateurID'));
}

function afterDelete(&$record){
    updateDonations($record->val('donateurID'));
}


}

[grageot]

Thank you Steve for your answer.
Its works well
Regards