2. PHPRunner General questions
  3. Update Field "on the fly"
This topic is locked

Update Field "on the fly"

 		6/24/2010 3:21:22 PM
PHPRunner General questions
M
 Mandy author

Hello.

I have two tables:

player_info = master

player_movements = detail
In player_movements I have fields "investment", "return" and "balance".

I am trying to make the "balance" field update on the fly, being:

balance = old balance - investment + return.
I tried to make balance a custom view using:

$value = $data["return"]-$data["investment"];

but obviously this didn't take account of the previous balance so didn't work.
And, once I've succeeded in the above, I'd like to be able to store this value (ie. the balance) in a session variable to pass to another application.
Help appreciated! :-)
Mandy
A
 		ann 6/25/2010

Mandy,
to update 'balance' field on the fly use JavaScritpt Onload Event on the Events tab. Here is a sample:

var ctrlI = Runner.getControl(pageid, 'investment');

var ctrlR = Runner.getControl(pageid, 'return');

var ctrlB = Runner.getControl(pageid, 'balance');

var oldB=parseFloat(ctrlB.getValue());

function func() {

ctrlB.setValue(oldB-parseFloat(ctrlI.getValue()) + parseFloat(ctrlR.getValue()));

};

ctrlI.on('keyup', func);

ctrlR.on('keyup', func);



Also you can calculate and save balance in the Before record added/Before record updated event. Here is a sample:

$values["balance"] = $oldvalues["balance"]-$values["investment"]+$values["return"];



To save value to a session variable use Before record added event. Here is a sample:

http://xlinesoft.com/phprunner/docs/speed_up_data_entry_using_events.htm
M
 		Mandy author 6/27/2010

Hi Ann.

Thank you kindly for your help. I had a good play with some of the suggestions over the weekend and here are my thoughts and report:

  1. I had no luck at all with the Javascript onload code (it didn't do anything and gave errors when trying to edit data) - what is this supposed to do exactly? Does it actually update the database? And I'd be interested to know if there is a working demo of this event somewhere.
  2. Using the code you gave, the balance didn't update correctly - I beleive the reason for this is that there is no balance in the current row when I add a new entry. Perhaps I didn't explain it clearly enough - my aim is to give a running total (like a bank statement) where every investment is deducted from the previous balance and then the return added to this balance when the investment is sold.

    The table should develop something like this (the settled field is simply a 0/1 switch). Let's assume the initial balance is 200):
    investment|return|settled|balance

    (null), (null), 0, 200

    100, (null), 0, 100
    investment|return|settled|balance

    (null), (null), 0, 200

    100, 200, 1, 300
    investment|return|settled|balance

    (null), (null), 0, 200

    100, 200, 1, 300

    120, (null), 0, 180
    investment|return|settled|balance

    (null), (null), 0, 200

    100, 200, 1, 300

    120, 130, 1, 310
    I did manage to get this event to work on edit page, before record updated (ie. when I input the return):
    if (($values["return"]!=NULL) && ($values["settled"] =="0"))

    {

    $values["balance"] = $oldvalues["balance"]+$values["return"];

    $values["settled"] = "1";

    }

    return true;
    However, I did not manage to find a way to "pull" the old balance from the previous row when inputting the initial investment.
    Regards,

    Mandy
A
 		ann 6/28/2010

Mandy,
please see my comments below:

  1. Javascript code works on the Edit page only (otherwise oldB variable is not defined).

    If you want to fill in field onload you can use only JavaScript code.
  2. In case you have a table ordered according to the ID field you can use the following code to return value from the previous record:

$sql="select ID from tablename order by ID desc limit 1";

$rs=CustomQuery($sql);

$data=db_fetch_array($rs);

$oldvalue=$data["ID"];



where ID is actual key field name.