This topic is locked

Add button to get sum of percents

8/28/2014 5:15:04 PM
PHPRunner General questions
stanl author

I have two tables employeegoals and manager. There is a parent child relationship in that the manager has employees that have mulitple goals in employeegoals table. Each goals has a percentage and I am trying to sum percentage for an employee from employeegoals and update the field employeepercent in the manager table. I added a button and put this code in the server event to update the items selected. What am I doing wrong with the following code?
Thanks
global $dal;
while ( $data = $button->getNextSelectedRecord() ) {
// The following sql statement works to update if I hard code the percent
//$sql = "Update manager set EmployeePercent='55' where manager_pk=".$data["manager_pk"];
//But I want to use the following to sum from the other table when the employee is selected and then update.
$sql = "select SUM(Percentage) FROM employeegoals where employeegoals.employee='manager.employee'";
CustomQuery($sql);
$sql2 = "Update manager set EmployeePercent='$sql' where manager_pk=".$data["manager_pk"];
CustomQuery($sql2);

}

$result["txt"] = "Records were updated.";

stanl author 9/1/2014

I think this is very simple, I just can't get the last piece. The two lines below work, but I have supplied the employee id number found in the selected record for the manager table.
= '316';
$sql = "Update manager set EmployeePercent=(select SUM(Percentage) FROM employeegoals where employee=".[color="#FF0000"]$EmpID.") where manager_pk=".$data["manager_pk"];
CustomQuery($sql);
Can someone tell me how to read the value for employee in the selected record in manager table and furnish it in the sql statement?
Thanks

stan



I have two tables employeegoals and manager. There is a parent child relationship in that the manager has employees that have mulitple goals in employeegoals table. Each goals has a percentage and I am trying to sum percentage for an employee from employeegoals and update the field employeepercent in the manager table. I added a button and put this code in the server event to update the items selected. What am I doing wrong with the following code?
Thanks
global $dal;
while ( $data = $button->getNextSelectedRecord() ) {
// The following sql statement works to update if I hard code the percent
//$sql = "Update manager set EmployeePercent='55' where manager_pk=".$data["manager_pk"];
//But I want to use the following to sum from the other table when the employee is selected and then update.
$sql = "select SUM(Percentage) FROM employeegoals where employeegoals.employee='manager.employee'";
CustomQuery($sql);
$sql2 = "Update manager set EmployeePercent='$sql' where manager_pk=".$data["manager_pk"];
CustomQuery($sql2);

}

$result["txt"] = "Records were updated.";

admin 9/1/2014

Check example at http://xlinesoft.com/phprunner/docs/update_multiple_records.htm
$data["FieldNameHere"] gives access to any field of selected record on the List page.

stanl author 9/2/2014

Sergey,
Thanks for the response, and that is what I had been trying, but for some reason I keep getting a null value for Sum(Percentage). Do you see what I am doing wrong in the code below? If I use the line that is specifying the value 316 the percentage is correct, but if I use $data["employee"]; I get null.
Stan
__

global $dal;

while ($data = $button->getNextSelectedRecord() )

{

// set Employee goal percentage'
//$EmpID = '316';
$EmpID = $data["employee"];
$sql = "Update manager set EmployeePercent=(select SUM(Percentage) FROM employeegoals where employee=".$EmpID.") where manager_pk=".$data["manager_pk"];
CustomQuery($sql);
}

$result["txt"] = "Records were updated.";

__



Check example at http://xlinesoft.com/phprunner/docs/update_multiple_records.htm
$data["FieldNameHere"] gives access to any field of selected record on the List page.

admin 9/2/2014

Print your SQL query instead of executing it to see what went wrong. This is the only way to find what is wrong.
See this article for inspiration:

http://xlinesoft.com/blog/2012/05/16/troubleshooting-custom-buttons-in-phprunner-and-asprunnerpro-applications/