How to use calculated fields

Frequently, in addition to a simple data output, it may be necessary to perform different kinds of data manipulation, such as summation, calculation of simple average, etc.

Let's discuss a few concrete cases by the example of "Cars" table in test MS Access database (examples.mdb) that comes with ASPRunner Pro. Depending on complexity of solving problem you need to make some changes on Events tab, on Edit SQL query tab or on the Visual Editor tab.

1. Adding the field which calculate simple average of EpaCity and EpaHighway fields.

	Open ASPRunner/PHPRunner project and proceed to Edit SQL Query tab
	Switch to the SQL mode
	In SQL mode add string ([EPACity] + [EPAHighway])/2 as Expenses

	Proceed to Choose fields tab
	For Expenses field unselect following checklists: Add, Edit, Inline Add, Inline Edit
	Build your project and view results in browser

For next two items you should modify your database for a bit. Add to Cars table numeric field Tax. It's important, that this field should be able to hold fractional values.

2. Adding the field which calculate imposed tax after editing or adding a new record.

Click on Before Record Updated Event

Modify sample event code by putting following code snippets to appropriate place

Make the same for Before Record Added Event

3. Adding the field which immediately calculate imposed tax when Price or Horsepower fields are edtiting
for the PHPRunner 5.2/ASPRunnerPro 6.2.

	Open your ASPRunner/PHPRunner project and proceed to the Events tab
	Click on JavaScript Onload event
	Add following code:

var ctrlPrice = Runner.getControl(pageid, 'Price'); var ctrlHorsepower = Runner.getControl(pageid, 'Horsepower'); var ctrlTax = Runner.getControl(pageid, 'Tax'); function func() { ctrlTax.setValue(0.01*(+ctrlPrice.getValue()) + 2*(+ctrlHorsepower.getValue())); }; ctrlPrice.on('keyup', func); ctrlHorsepower.on('keyup', func);

Now you can edit or add new records and every time when you make changes in Horsepower or Price field correspondingly Tax field has changed. It's very comfortable 'cause right away you see imposed tax and can modify price depending on it.

Applies to:
ASPRunner Pro
PHPRunner

Back to top