Calculated field question

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

Calculated field question

Post by dathanasios » 2021-03-11 15:25

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,

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

Re: Calculated field question

Post by pfrumkin » 2021-03-11 17:56

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

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

Re: Calculated field question

Post by dathanasios » 2021-03-11 18:32

@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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated field question

Post by pbottcher » 2021-03-11 19:54

Try

SELECT SUM(`shifts`.`WorkingHours`) FROM `shifts` left join `payroll` on `shifts`.`ShiftDate` = `payroll`.`DateToCalc`
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated field question

Post by dathanasios » 2021-03-12 07:26

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,

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

Re: Calculated field question

Post by pfrumkin » 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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated field question

Post by pbottcher » 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%)
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated field question

Post by dathanasios » 2021-03-13 07:45

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

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

Re: Calculated field question

Post by dathanasios » 2021-03-13 07:47

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

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

Re: Calculated field question

Post by dathanasios » 2021-03-13 08:15

Image

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated field question

Post by pbottcher » 2021-03-13 11:15

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
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Calculated field question

Post by dathanasios » 2021-04-01 08:34

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

Code: Select all

function employees_after_update($data, $memberInfo, &$args)
function in hooks directory?
Regards,

Post Reply