How to use calculated fields

Calculated fields are useful when a value needs to be derived from other fields, such as totals, averages, taxes, or percentages. In PHPRunner and ASPRunner.NET, there are several ways to calculate values depending on whether the result only needs to be displayed, saved to the database, or updated instantly while the user edits a record.

In the examples below, we will use a simple Cars table with fields such as Price, Horsepower, EPACity, EPAHighway, and Tax.

Which method should I use?

  • SQL: use when the calculated value only needs to be displayed and does not need to be stored in the database.
  • Server-side events: use when the calculated value needs to be saved to the database.
  • JavaScript: use when the user should see the calculated value immediately while adding or editing a record.
 

1. Calculate a field in SQL

In this example, we calculate the average of the EPACity and EPAHighway fields. This approach works best when you need to display a calculated value but do not need to save it in the database.

1. Open your project and proceed to the Edit SQL Query screen.

2. Switch to SQL mode.

3. Add the following expression to the query: ([EPACity] + [EPAHighway])/2 as AvgEpa.

Sample SQL query

4. Proceed to the Choose fields screen.

5. For the AvgEpa field, clear the following checkboxes: Add, Edit, Inline Add, Inline Edit.

6. Build the project and view the calculated field in the web application.

 

2. Calculate and save a value using server-side events

In this example, the Tax value is calculated from the Price and Horsepower fields and saved to the database whenever a record is added or edited.

1. Open the Before Record Updated event.

2. Add the following code to calculate the Tax value.

PHPRunner example

ASPRunner.NET example

ASPRunnerPro example

3. Apply the same calculation in the Before Record Added event so the Tax value is also calculated for new records.

 

3. Calculate a value instantly with JavaScript

Sometimes you want users to see a calculated value immediately, before the record is saved. In this example, the Tax field is recalculated whenever the Price or Horsepower value changes.

1. Open your project and proceed to the Events screen.

2. Open the JavaScript OnLoad event.

3. Add the following code:

Now, when a user adds or edits a record, the Tax field is recalculated immediately whenever the Price or Horsepower field changes. The user can see the result before saving the record.

PHPRunner gives you several ways to add calculated values depending on whether you need them for display, storage, or instant feedback on the page. Learn more about PHPRunner.

Applies to:

  • PHPRunner
  • ASPRunner.NET
  • ASPRunnerPro

Back to top