Calculated fields get updated...
- when the record is opened in DV
- when the record is visible in TV for at least a few milliseconds (depending on various parameters like performance or bandwidth)
- when you are using cli-update-calculated-fields.php
- when you execute calculation manually (see code below)
If you need to update a totals-calculation without viewing that totals-record (in TV or DV) due to changes in dependent tables here: shifts), you can execute the calculations and database updates separately (triggered manually).
Yes, you can use shifts_* hooks for this. In your scenario, if I got it right, you should call the calculation for "totals" table after insert, update or delete on "shifts" table.
Insert / Update
After insert/update of a "shift"-record, you actually don't know which "totals" record should be updated. So you will have to find out first. As soon as you know it, you can use the following function for updating a totals-record after modification of a shifts-record.
Challenge: Delete
There is one big challenge: Deleting a shift.
Before delete, you can find out the PK of the related totals record. But when updating the totals, the shift still exists. So the calculation will return a correct but unwanted result.
After delete, the shift does not exist any more, but you can not find out the PK of the related totals record.
So you need a way for getting the totals_id related to the shift-to-be-deleted
before delete and execute the re-calculation of totals
after delete of a shift.
Notes
- I did not test the following with your database tables. There may be typos
- You should add some debugging to check content of variables if this does not work at first
- I assume your tables are named "totals" and "shifts"
- in shifts_before_delete, the shift still exists, so the calculation will return unexpected (but correct) values
- in shifts_after_delete, this shift has been deleted, but it may be hard to find out the related totals-id, see alternative below
Code: Select all
// (1) add the following function to hooks/shifts.php
// (2) in shifts_after_insert and shifts_after_update get the primary key of the related totals-record
// and call the function like this: shifts_update_totals($totals_id);
// (3) for delete you will have to find a way to get the totals-id before_delete,
// perhaps store it in $_SESSION variable, then, in shifts_after_delete, get it from $_SESSION and
// call the function like this: shifts_update_totals($totals_id);
function shifts_update_totals($totals_id)
{
$tn = "totals";
$calcs = calculated_fields();
$formulae = $tn && $calcs ? $calcs[$tn] : null;
if ($formulae && $totals_id) update_calc_fields($tn, $totals_id, $formulae);
}
Alternatives for delete-challenge
- Instead of finding and somehow passing over the $totals_id you may consider updating all totals records or all totals records in a certain status. This may become time consuming on large databases but may be easier to implement.
- On shifts-table you may implement the so called "soft-delete" approach by NOT deleting the shift-records physically but by marking a deleted shift as "deleted". Therefore, just add an additional "is_deleted" (unsigned int, checkbox, default 0) field and change your calculation SQL command to summarize only shifts where is_deleted != 1
- for delete-challenge: add a new field totals.requires_update (unsigned int, checkbox, default 0). In shifts_before_delete() hook, get the PK of the related totals record, then UPDATE `totals` SET `requires_update`=1 WHERE `YOUR_PK_COLUMNNAME`='{$totals_id}' LIMIT 1. In shifts_after_delete() hook, fetch all totals-records WHERE requires_update = 1 and execute shifts_update_totals on every row. Afterwards, SET requires_update = 0 for each re-calculated totals-record.