2. PHPRunner General questions
  3. Calculated fields help
This topic is locked

Calculated fields help

 		1/21/2014 6:50:26 PM
PHPRunner General questions
S
 simonuk author

Hi
I'm looking for some pointers with a calculated field I want to use to display a sum total in the view screen of a table. I originally added the sql below to the 'SQL query' for the required table but that has quickly resulted in a slow down of the list page, and so after further investigations and a review of the help examples, it seems clear that I should use the 'Events' feature to perform the calculation before the screen is displayed.
Please note there is no edit requirement, I only need it to display in the view screen so I'm hoping this won't be too complex.
My sql query is as follows but as I've no experience with Javascript I'm a bit stuck with the construct of what I'm assuming will be a function. I'm also assuming that function will be based on the contents of this query but other than I'm not really sure where to start.
(select

round(sum(value), 2)

FROM mytable

) AS Totcap
Cheers

Simon
Sergey Kornilov admin 1/21/2014

Assuming this SQL query is correct and you only need to display this value on the View page you can do something very simple.
Insert 'PHP code snippet' on the page where you want to display it and put the following code there:

echo DBLookup("select round(sum(value), 2) FROM mytable AS Totcap");
S
 		simonuk author 1/22/2014

Ah - I was looking in the wrong direction. Thank you
My query though is a little more complex than I advised as I hadn't included the relationship to the wider query in my original post. The sum isn't of the whole table but only those records related to the parent record being viewed. Therefore.....
echo DBLookup("select round(sum(value),2) FROM table1");
needs to become this.......
echo DBLookup("select round(sum(value),2) FROM table1 where col1 in (select equiv_col1 from table2 where col2 = 'passed_field_value'");
To hopefully decipher the sql a little more, the piece that I'm not sure about is the last bit, passing the 'passed_field_value' as the calculation's parameter. This is the unique identifier id for the overall viewed record. Does that make sense ? I can't see how I should pull in that parent record id into the snippet. Thanks again
Sergey Kornilov admin 1/22/2014

If you are talking about record ID (primary key value) on the View page - you can access it as $_GET["editid1"].