AppGini Community Forums

Hello to all.
I tried to set a calculated field for total working hours.
Lets say, that i have two tables:
1]. The `shifts` table with the following fields:
`ID`, Primary key
`EmployeeID`, integer
`ShifDate`, date
`WorkingHours`, decimal

2]. The `payroll` table with the following fields:
`ID`, primary key
`DateToCalc`, date and required and unique
`TotalHours`, calculated field, decimal and readonly with the following query:
SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` WHERE `shifts`.`ShiftDate` = `payroll`.`DateToCalc` which is not working.
I also tried the following:
SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` WHERE `shifts`.`ShiftDate` = '%DateToCalc%' without any result.
It is working with a constant value such as:
... WHERE `shifts`.`ShiftDate` = '2021-03-11'
The idea is that the `payroll` table has one record for each date which calculate the total working hours for all employees from the `shifts` table
Any idea?
Best regards,

Maybe the date stored in the database has a time, have you looked at your data stored in the database? I would start with hard coding some dates and doing a BETWEEN type check in the WHERE. For instance, if you know you have data for 2021-03-11, hard code SQL to look at shifts.ShiftDate > '2021-03-111' AND shifts.ShiftDate < '2021-03-12', just to see if you can get some data.

~Paul

@pfrumkin
Thank you for your response.
The stored dates have te same format in both tables.
With hard code dates I receive the correct data, but I use them just for debugging purposes.
So, the question remains.
Is the following query correct in order to retrieve the total houts?
SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` WHERE `shifts`.`ShiftDate` = `payroll`.`DateToCalc`
Regards

Try

SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` left join `payroll` on `shifts`.`ShiftDate` = `payroll`.`DateToCalc`

pböttcher wrote: ↑

2021-03-11 19:54

Try

SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` left join `payroll` on `shifts`.`ShiftDate` = `payroll`.`DateToCalc`

It brings the sum of total records in shifts table to every record in payroll table with the "WHERE `shifts`.`ShiftDate` = `payroll`.`DateToCalc`" statement and without it. I suppose that regarding to join.
I believe that the solution is in the where clause.
Which variable can take the `DateToCalc` value, like the '%ID%' which takes the record's id value?
Does it exists the '%DateToCalc%' or something like the hook's: $data['DateToCalc'];
I understand that I can easily calculated it in function payroll_after_update($data, $memberInfo, &$args) in hooks directory, but it is more convenient in a calculated field.
Regards,

Yes, perhaps you should add
WHERE shifts.EmployeeID = {$data["id"]}

Depends on which hook, maybe it is {$data['selectedID']}

~Paul

Hi,
sorry,
try this corrected version.

pfrumkin wrote: ↑

2021-03-12 13:55

Yes, perhaps you should add
WHERE shifts.EmployeeID = {$data["id"]}

Depends on which hook, maybe it is {$data['selectedID']}

~Paul

Hi Paul,
thanks for the response.
It is not working because the key is not the record id, bue the date
Regards

pböttcher wrote: ↑

2021-03-12 21:49

Hi,
sorry,
try this corrected version.

Code: Select all

SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` left join `payroll` on `shifts`.`ShiftDate` = `payroll`.`DateToCalc` where `shifts`.`ShiftDate`=(select `DateToCalc` from `payroll` where ID=%ID%)

Perfect!
Thank you very much!

One more question, based on your experience:
When the user is in detail view in the desired date in payroll table, is it possible in AppGini to display a link to children records from payroll to shifts?
But the relation must be set up with dates (WHERE `shifts`.`ShiftDate` = `payroll`.`DateToCalc`) instead of record ids
Thanks in advance

Hi,

yes that should be possibe. You would need to call the shifts table with a filter set to the datetocalc field value for the shiftdate

One more question about calculated fields.
Is it possible a calculated field to be evaluated before the user display the affected table 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?
Or it can update only with code inside function in hooks directory?
Regards,