This topic is locked
[SOLVED]

 total from details table to master table field

7/20/2017 8:23:09 PM
PHPRunner General questions
H
Hertz2P author

I have a master table "TimeCard" and a details table "timeClock"
Both have a field called "hoursWorked" which is a sql calculated field in the details table and multiple entries are allowed within the same day.
In both tables the primary key field is "id", and the detail key is "timeCardId"
I'm trying to use this code to populate the "hoursWorked" field in the master table, and it doesn't seem to be working (no errors either.) Can anyone see what I'm doing incorrectly?



global $strTableName,$dal;

$rs = CustomQuery("select sum(hoursWorked)from timeClock where timeCardId=".$_SESSION[$strTableName."_masterkey1"]);

$data = db_fetch_numarray($rs);
$dal->Table('TimeCard')->Value["hoursWorked"] = $data[0];

$dal->Table('TimeCard')->Param["id"] = $_SESSION[$strTableName."_masterkey1"];

$dal->Table('TimeCard')->Update();
admin 7/21/2017

You need to troubleshoot your code. I.e. instead of executing the query print it on the page, run it manually and see it it returns correct data. The same thing with UPDATE SQL query. Print, test manually, see if it works.

H
Hertz2P author 7/21/2017

Thanks Sergey, can you give a quick example of how that's done? Searching for 'print' is pulling up a lot of printer related issues..

H
Hertz2P author 7/21/2017

Okay, I've figured out that the sql is the part that's not working. I think it may be due to the fact that the field I would like the totals from is a calculated field (uses startTime and endTime to calculate hoursWorked). However, using the totals page to get the totals works. So I guess my question is can the DAL access the totals field of a details table directly?
Here are the codes that don't work together:



SQL: round(time_to_sec(timediff(endTime,startTime))/ 3600,2) AS hoursWorked,
JavaScript: $rs = CustomQuery("select sum(hoursWorked)from timeClock where timeCardId=".$_SESSION[$strTableName."_masterkey1"]);
admin 7/21/2017

Instead of hoursWorked you need to use the same expression in your custom query:

$rs = CustomQuery("select sum(round(time_to_sec(timediff(endTime,startTime))/ 3600,2)) from timeClock where timeCardId=".$_SESSION[$strTableName."_masterkey1"]);
H
Hertz2P author 7/21/2017

Thanks Sergey, that works great!
Initially it was throwing an error because I also had a page redirection on the after record added/updated events.. Removing the redirect prevents the error, but is there still a way to redirect after this code runs?

admin 7/21/2017

Yes, you can do redirect in your event code after running this update.

H
Hertz2P author 7/21/2017



Yes, you can do redirect in your event code after running this update.


If I add the redirect code under the (working) above code, it throws an error and doesn't redirect... In other words, either piece of code works on its own, but if I put the redirect under the DAL code it's no good.

H
Hertz2P author 7/22/2017



If I add the redirect code under the (working) above code, it throws an error and doesn't redirect... In other words, either piece of code works on its own, but if I put the redirect under the DAL code it's no good.


Nevermind, it was stupid to try to redirect from a details table, because the Master table was still opened and unsaved. This one is resolved, thanks Sergey!