List the total of a detail column on a master list

One way would be:

1. Add another column in your master table by manually editing SQL ie "1 AS EmployeeEarnings" - don't allow add/edit on this field
   
2. You will need to "unset" this field in Add and Edit "before record updated" event
   
3. On the master table List view, before record processed event add code:
   

• query employees table based on $data values for that employee to calculate the total earnings
  
• write this to the $data["EmployeeEarnings"] field

One way would be:

1. Add another column in your master table by manually editing SQL ie "1 AS EmployeeEarnings" - don't allow add/edit on this field
   
2. You will need to "unset" this field in Add and Edit "before record updated" event
   
3. On the master table List view, before record processed event add code:
   

• query employees table based on $data values for that employee to calculate the total earnings
  
• write this to the $data["EmployeeEarnings"] field

I think I am beginning to understand.

My application is an inquiry only, so I have already shut off add/update/delete capabilities & I have located where the query code is to be inserted.
Can you give me an idea of what the MySQL query would look like?

I have looked through the forum & I'm getting a bit confused as to how to write the query.

I went ahead & gave it a shot with the following code & got an error "type 2 - htmlspecialchars() expects parameter 1 to be string, resource given":

Master is "user" & Detail is "buck"

buck.balance is decimal(8,2) type

user.skDemoUser is int(11) type
global $conn,$strTableName;

$str = "Select sum(buck.balance) from buck where buck.ssn = '".$_SESSION[$strTableName."_masterkey1"]."'";

$rs = db_query($str,$conn);

$data["skDemoUser"] = $rs;

return true;
Rather than adding a new master field, since I shut off everything except inquiry I thought I might be able to just use an existing field.

Could the fact that the data types are different be my problem or is my script below missing the mark?

Looks like you are missing a database call for a start - try:
$rs = db_query($str,$conn);

$row = db_fetch_numarray($rs); // can also use db_fetch_array then refer to elements by name rather than number

$data["skDemoUser"] = $row[0];

Can you not set up a custom view on the Earnings, have the master table Employee set to Employee_ID and detail to Earnings set to Employee_ID and edit the sql to give you this.

This would show the Employee_ID and the Earnings for that Employee?

Looks like you are missing a database call for a start - try:

I made the change as follows, but the value for $data appears with the same value on every employee row.

Also, each employee may have one or more earnings record, so that's why I summing them.
[codebox]global $conn,$strTableName;

$str = "Select sum(buck.balance) from buck where buck.ssn = '".$_SESSION[$strTableName."_masterkey1"]."'";

$rs = db_query($str,$conn);

$row = db_fetch_numarray($rs); // can also use db_fetch_array then refer to elements by name rather than number

$data["skDemoUser"] = $row[0];

return true;[/codebox]

There is a bit of an inconsistency here as picked up by swanside. In your original post you imply that there can be multiple earning records for each employee but in your definition of the earnings table, you state the key as being EmployeeID. You should have another primary key field here, say EarningID, and then the EmployeeID is a foreign key linking multiple records back to your master table.
To debug, capture or output the SQL you generate in your code. If still stuck, you can use an SQL tool like SQLyog to independently check the SQL is working as you expect.

There is a bit of an inconsistency here as picked up by swanside. In your original post you imply that there can be multiple earning records for each employee but in your definition of the earnings table, you state the key as being EmployeeID. You should have another primary key field here, say EarningID, and then the EmployeeID is a foreign key linking multiple records back to your master table.

I'm sorry for the confusion I have caused, so let me restate what I have done so far.



Data Sources Table

I selected a table called "user"

I selected a table called "buck", and

selected "user" as the master with primary key = pass_code, and

select selected "ssn" as the foreign key on "buck"

(pass_code & ssn are the same fields but with different names)
Note: Both the user & buck tables house information for multiple companies. Each table has a column "emp_dir" that identifies the company.

Since it's my understanding that PHPRunner can only handle one key & since the web hosting server does not have MySQL version 5, I looked at the generated code & since I only only need information for one specific company & this applications is inquiry only, I modified the following statement in user_variables.php & added a specific company name . . . $gsqlWhere="user.emp_dir = 'companyabc"; . . . and just to be safe I also made the following change in buck_variables.php . . .$gsqlWhere="buck.emp_dir = 'companyabc'"; .
Everything seems to work perfect.

The "user" screen lists pass_codes & employee names for each employee from "companyabc" only.

The "buck" screen lists multiple records each containing a dollar value called "balance" for the employee selected from "companyabc".

I also set up a total on balance & it appears at the bottom on the screen.
NOW, what I am trying to is somehow have place the sum of "balance" from each of the "buck" records for the selected employee on the "user" screen next to the employee's name.
In effect, this is what I am trying to do:
SELECT SUM(buck.balance) FROM buck

WHERE buck.emp_dir = user.emp_dir AND buck.ssn = user.pass_code
Do you think I am on the right track or is it that I just can't get there from here?

I dont think you should be using $_SESSION[$strTableName."_masterkey1"] as the comparison variable. The event you should be using is attached to the master table and this variable would only be set if you were in the child table list view. The event is being processed for every record of the master table and therefore I think your SQL should be:
$str = "Select sum(buck.balance) from buck where buck.ssn = '".$data["pass_code"]."'";
For debugging this I would be capturing/outputting the $str variable at this point to confirm the SQL is what you expect. I have set up a special debug page that just lists all the SESSION variables in a convenient format - then you just need to put in a debug line:
$_SESSION["debug"][] = $str; // this will capure every row processed in an array

I dont think you should be using $_SESSION[$strTableName."_masterkey1"] as the comparison variable. The event you should be using is attached to the master table and this variable would only be set if you were in the child table list view. The event is being processed for every record of the master table and therefore I think your SQL should be:

Oh, you are good. Thank you so much.
I do have a couple of related issues that I need some help on.

1. Is it possible to include this same balance column "$data["skDemoUser"]" when I export or print the master records?
   
   It does export this column, but the values are zero.
   
2. I display the master information on the the detail screen, but this same balance column "$data["skDemoUser"]" appears with a zero balance.
   Again, thank you for your help.

The reason that the field is zero is that it is only the list event code that is populating it for you to see. You could probably do a similar trick with print as there are events associated but the export could be a bit more tricky. A better way may be to try a subquery in the SQL - can't help you much with that I'm afraid but if you could get the SQL to give you the right number then it would automatically be right on export and print (and would also show on the master details.
On the detail screen you could generate the same figure by adding a TOTAL for the "balance" field.

The reason that the field is zero is that it is only the list event code that is populating it for you to see. You could probably do a similar trick with print as there are events associated but the export could be a bit more tricky. A better way may be to try a subquery in the SQL - can't help you much with that I'm afraid but if you could get the SQL to give you the right number then it would automatically be right on export and print (and would also show on the master details.

I used the same event code I used for in list for print, but did not see any results. any ideas?

global $conn,$strTableName;

$str = "Select sum(buck.balance) from buck where buck.ssn = '".$data["pass_code"]."'";

$rs = db_query($str,$conn);

$row = db_fetch_numarray($rs);

$data["skDemoUser"] = $row[0];

return true;
And for export, what do you mean by "subquery"?
Also, when I select to display master table information on the detail list, is there a way to only display selected master columns?

I was able to remark out a column in xxx_masterlist.php but that only removed the varilable & not column position or heading.