ijtzib
Posts: 24
|
| Posted: 06/26/2012, 3:24 PM |
|
Hi I want to know how to calculate the sum of a field in a grid after I make a search.
I have a search form with a grid in a page.
Fields: Employee_ID, EmployeeName,DateFrom, DateTo, and DaysEarned. These are fields in the database. The user manually inputs a value in the DaysEarned Field.
The user then does a date range search and the result then appears in the grid for a specific employee. So I want to calculate sum of the DaysEarned field from the search result only!
How can I accomplish this? Im using PHP & Mysql....
Can any one help me please...Or point me to a discussion that can help me with this..
I really appreciate your help
Thanks......
|
 |
 |
bannedone
Posts: 273
|
| Posted: 06/26/2012, 5:08 PM |
|
try creating a label in the grid outside of the rows.
then do a dlookup in the before show event for that label where the expression is
SUM(DaysEarned)
the Criteria be the same search criteria used for the grid search
(This can be done by capturing the SQL in the before execute select by creating a global variable and saving it for use by the dlookup action)
Have the target type be control and put it into the label.
to get the SQL
Add this Custom code in the before execute select event
global $mySQL;
$mySQL=$Component->ds->Where;
to get your total
add this this custom code to the beforeshow event for the new label you created.
global $mySQL;
//code below for debugging comment out after it is proven to get the proper where statement
echo "Criteria ".$mySQL;
Now add your dlookup action in the before show row event for the same label
Dlookup params below
Expression "SUM(DaysEarned)"
Domain "YourDBName"
Criteria $mySQL
Connection (Select Your DB Connection)
Convert result to (Select Type)
Type of Target (Select Control)
Target (SelectYourLabel)
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
ijtzib
Posts: 24
|
| Posted: 06/27/2012, 9:52 AM |
|
Hi bannedone,
Thank you so much for the reply...
I tried to follow your instruction step by step...but i get the following error....
Fatal error: Call to undefined function SUM() in C:\AppServ\www\ProfileManager\VacationLeaveEarned_events.php on line 44
This is what I did:
I put the dlookup in the before show of the label1 and plugged in the params
I then....
Plugged in the following code
//Custom Code @60-2A29BDB7
// -------------------------
global $mySQL;
$mySQL=$test_prefixvacationleave->ds->Where;
// -------------------------
//End Custom Code
in the Before Build Insert of the grid.
and in the before show of the label1 i plugged in:
global $mySQL;
//code below for debugging comment out after it is proven to get the proper where statement
echo "Criteria ".$mySQL;
Thats all i DID...
Where did I screw up? Did I missed something???
Please help...
Thanks.
|
|
|
|
ijtzib
Posts: 24
|
| Posted: 06/27/2012, 10:17 AM |
|
Hi bannedone,
I reviewed again and found my mistake...missing quote in the dlookup params...
Published with no errors
BUT no results appear in the Label1 seems only a 0 appears!
I have data type to integer
Whats wrong now?
|
|
|
|
bannedone
Posts: 273
|
| Posted: 06/27/2012, 12:30 PM |
|
Did the echo debug statement show the proper WHERE SQL???
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
ijtzib
Posts: 24
|
| Posted: 06/27/2012, 12:38 PM |
|
Yeah I see...
This was echoed:
......................................
Criteria Employee_ID = 11
.....................................
Yeah it matched the Employee_ID for the employee I searched for....
|
|
|
|
ijtzib
Posts: 24
|
| Posted: 06/27/2012, 1:57 PM |
|
I changed domain in the dlookup to TableName rather than dbname and Rather that SUM to sum in the expression BUT now it gives me a weird number: not the correct result..
Any ideas what wrong?
|
|
|
|
ijtzib
Posts: 24
|
| Posted: 06/28/2012, 8:50 AM |
|
Sorry Guys...It was an error from my end with my master detail grid page.
Tried the instructions again from bannedone and it worked!!
Thank you bannedone for your HELP!!
|
|
|
|
