If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
-
dathanasios
- Posts: 26
- Joined: 2020-12-26 10:17
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
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
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
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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
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
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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
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
-
pbottcher
- AppGini Super Hero
- Posts: 1638
- Joined: 2018-04-01 10:12
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
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,