Calculated fields evaluation

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
dathanasios
Posts: 25
Joined: 2020-12-26 10:17

Calculated fields evaluation

Post by dathanasios » 2021-04-02 15:37

Hello.
Is it possible a calculated field to be evaluated before the user displays the affected table?
E.g. I am working on 'employees' table which has a calculated field (lets say the working hours) which is update the 'totals' table with the working cost.
Is it possible that the working cost can be updated before the user displays the 'totals' table in screen?
Or it can update only with code inside function in hooks directory? E.g.

Code: Select all

function employees_after_update($data, $memberInfo, &$args)
Regards,

pfrumkin
Veteran Member
Posts: 131
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: Calculated fields evaluation

Post by pfrumkin » 2021-04-05 21:10

Hi,

Your question is not entirely clear to me. If you are not displaying a table, how would you see or access the calculated value?

If you are considering being able to access this calculated value from a database view (PHPAdmin) perhaps you would want a view, in which the calculation is made in creation of the view, not using the AG functionality.

If you are looking at some other table in the UI and want to view some calculation based on other tables, you can do that in a hook.

~Paul

dathanasios
Posts: 25
Joined: 2020-12-26 10:17

Re: Calculated fields evaluation

Post by dathanasios » 2021-04-07 08:17

Hi Paul,
If you are considering being able to access this calculated value from a database view (PHPAdmin) perhaps you would want a view, in which the calculation is made in creation of the view, not using the AG functionality.
If you are looking at some other table in the UI and want to view some calculation based on other tables, you can do that in a hook.
As I saw all were clear :D
My case: The user inputs some data into `shifts` table using the AG functionality using some calculation fields which updates the `totals` table.
After input will exiting the system without display the `totals` table and run the reporting system which is a desktop application (exe).
The calculated fields weren't updated.
So, if i understood well the only way to update the calculated fields is by hooks.
Correct?

Thanks
Regards,

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1080
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Calculated fields evaluation

Post by jsetzer » 2021-04-07 09:25

Calculated fields get updated...
  1. when the record is opened in DV
  2. when the record is visible in TV for at least a few milliseconds (depending on various parameters like performance or bandwidth)
  3. when you are using cli-update-calculated-fields.php
  4. 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
  1. I did not test the following with your database tables. There may be typos
  2. You should add some debugging to check content of variables if this does not work at first
  3. I assume your tables are named "totals" and "shifts"
  4. in shifts_before_delete, the shift still exists, so the calculation will return unexpected (but correct) values
  5. 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.
Kind regards,
<js />

dathanasios
Posts: 25
Joined: 2020-12-26 10:17

Re: Calculated fields evaluation

Post by dathanasios » 2021-04-10 09:55

@jsetzer
Very clear and well documented!
Thanks a lot!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1080
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Calculated fields evaluation

Post by jsetzer » 2021-04-10 10:10

Thanks for your feedback!
Kind regards,
<js />

Post Reply