2. PHPRunner General questions
  3. Calculated fields in List Page
This topic is locked

Calculated fields in List Page

 		2/13/2015 4:02:52 PM
PHPRunner General questions
J
 jpbconsulting author

I have a list page where there is a starting value on the main record and individual mortality records as child records. I want to add a calculated field where the starting value has the sum of the child records removed from it to render a remaining amount presented on the list page.
I have tried adding a PHP_Snippet as a field on the list page, but cannot obtain a value. The code in the PHP_Snippet field is:
global $data,$conn;

$query = "select sum(mortalitycount) from Mortality_Information where batchnumber='".$data["batchnumber"]."'";

$request = db_query($query,$conn);

$resultValue = db_fetch_array($request);

$value = $data["batchcount"]-$data[0];

echo $value;
I have looked the other results on this forum and none of the solutions posted seem to work. There was a recommendation in one of the other posts which stated that this value could be updated when any child record is added/updated. If I were to take this route, how would that be accomplished and how would I be assured that the count presented on the list page is correct? I would prefer to have the amount calculated on the fly.
Admin 2/14/2015

We would need more details. It's not clear if you need to add this calculated field to each record or just once somewhere at the top and at the bottom of the page. If you talking about adding this field to each record you cannot use PHP code snippet, use 'View as' Custom instead.
More info:

http://xlinesoft.com/blog/2011/04/28/taming-the-beast-events-buttons-and-code-snippets/
J
 		jpbconsulting author 2/16/2015



We would need more details. It's not clear if you need to add this calculated field to each record or just once somewhere at the top and at the bottom of the page. If you talking about adding this field to each record you cannot use PHP code snippet, use 'View as' Custom instead.
More info:

http://xlinesoft.com/blog/2011/04/28/taming-the-beast-events-buttons-and-code-snippets/


I have tried the "view as" custom field and I am unable to obtain a result. If I run the same query in mySQL directly, it renders the result I am looking for, but once I put it in the custom field formatting, it only will respond with the value of batchcount. In order to set this up, I selected to insert a field, selected batchcount as the field and added the below to the custom field settings:
global $conn;

$query = "select sum(mortalitycount) from Mortality_Information where batchnumber='".$data["batchnumber"]."'";

$rsExists = db_query($query,$conn);

$resultValue = db_fetch_array($rsExists);

$value = $data["batchcount"]-$resultValue[0];
Either the query is not running or there is no result for some reason. How do I troubleshoot this?
UPDATE

I have found that inserting any field on the record and using a custom format will modify all instances of that field on the page. Therefore, adding a second instance of "batchcount" and changing the custom settings will modify both batchcount fields on the list view. The only way to add a field on the list view is to add it via the "Insert PHP Snippet" menu option and, as stated earlier, this doesn't work either.
Admin 2/16/2015

You can add a dummy field to your SQL Query, set 'View as' type to 'Custom' and put your code there.

Select ...,

'' as dummy

from ...
J
 		jpbconsulting author 2/16/2015



You can add a dummy field to your SQL Query, set 'View as' type to 'Custom' and put your code there.

Select ...,

'' as dummy

from ...



That works for adding the field to the list page. I am still having the problem with the SQL query not executing or returning a result. Do I need anything further than the $value? How can I see if the query is being sent correctly?
UPDATE

The query is being executed, but no result is being sent to the screen. Here is the packet result for the query:
0x0000: 4508 0090 79b1 4000 4006 b7b4 0a01 fa7b E...y.@.@......{

0x0010: 0a01 fa7c e7ff 0cea 8c86 d9ea 0ff8 f396 ...|............

0x0020: 8018 0133 ec9f 0000 0101 080a 8ffc b84f ...3...........O

0x0030: 2e8c 0efa 5800 0000 0373 656c 6563 7420 ....X....select.

0x0040: 7375 6d28 6d6f 7274 616c 6974 7963 6f75 sum(mortalitycou

0x0050: 6e74 2920 6672 6f6d 204d 6f72 7461 6c69 nt).from.Mortali

0x0060: 7479 5f49 6e66 6f72 6d61 7469 6f6e 2077 ty_Information.w

0x0070: 6865 7265 2062 6174 6368 6e75 6d62 6572 here.batchnumber

0x0080: 3d27 3031 3231 3135 3031 2d32 3434 273b ='01211501-244';

14:52:50.025167 IP (tos 0x8, ttl 64, id 53333, offset 0, flags [DF], proto TCP (6), length 128)

xx.xx.xx.xx.3306 > xx.xx.xx.xx.xxxxx: Flags [P.], cksum 0x096d (incorrect -> 0x0410), seq 267973526:267973602, ack 2357647942, win 186, options [nop,nop,TS val 780930810 ecr 2415704143], length 76

0x0000: 4508 0080 d055 4000 4006 6120 0a01 fa7c E....U@.@.a....|

0x0010: 0a01 fa7b 0cea e7ff 0ff8 f396 8c86 da46 ...{...........F

0x0020: 8018 00ba 096d 0000 0101 080a 2e8c 0efa .....m..........

0x0030: 8ffc b84f 0100 0001 0129 0000 0203 6465 ...O.....)....de

0x0040: 6600 0000 1373 756d 286d 6f72 7461 6c69 f....sum(mortali

0x0050: 7479 636f 756e 7429 000c 3f00 2100 0000 tycount)..?.!...

0x0060: f680 0000 0000 0500 0003 fe00 0022 0004 ............."..

0x0070: 0000 0403 3630 3605 0000 05fe 0000 2200 ....606.......".
The sum result is the 606 in the last line above. Why does it not display?
J
 		jpbconsulting author 2/16/2015

This appears top be a bug in PHPRunner 8.0.
When the dummy field on the list page goes to retrieve the value in $data["batchnumber"] it is retrieving the value in $data["batchcount"] instead. We can get static values to appear in the field, but never the result from the SQL query.
UPDATE

After multiple rebuilds, this started to work. For whatever reason, a FULL BUILD was required. Nothing was changed between the incremental and full builds, but when the code was tried after the full build, it worked.