Calculations

Overview

A Calculation, like a database Column, belongs to a Table. However its value, rather than being stored, is dynamically computed each time it is requested and whenever the inputs change, so it is always up to date.

Just like real database columns, calculations may be placed as fields on forms, used as data providers for different components, and requested programmatically.

Get Started

To work with Calculations you need to open the Table where you will add them using the Table Editor.

Add a Calculation

To add a calculation to your solution, follow these simple steps:

Add a Calculation

1. In the Table Editor, select the Calculations tab at the bottom after all the Columns

2. Select the solution where you want to add the Calculation and click the Add button

3. Set the property with a valid name.

4. Select the Return from the list

5. Select Open Selected Calculation to edit the calculation in the javascript editor.

A calculation is declared at the Solution level and is available throughout the solution in which it is declared. If it's declared in a Module, it will be available in all solutions that include the module

Edit a Calculation

To edit an existing calculation, you will have to open it in the script editor and modify the code.

1. In the Table Editor, select the Calculations tab

2. Select the Calculation you need to edit (expand the Solution or Module where the Calculation was defined if not visible)

3. You can enter a new name or change the Return Type inline, however, to change the supporting function you need to do the following:

   1. Use the button "Open selected calculation" or double-click on the Calculation column to open the JavaScript file where the corresponding function is defined

   2. Make the necessary changes to the function and make sure it returns a value of the same type as the Calculation definition

4. Save the editor.

Example

In this example, we create a calculation called subtotal on the order_details table. The function will multiply a couple other columns in the record, quantity and unitprice. The result is returned from the function.

/**
 * A simple calculation subtotal on a database table order_details 
 * which yields a Number value, 
 * unit price, multiplied by the quantity, with a discount applied.
 *
 * @properties={type:6,typeid:36,uuid:"644DCF7D-11C7-475E-82CD-F4F60ED00D77"}
 */
function subtotal()
{
    return unitprice * quantity;
}

Each table can have a corresponding calculations file. For example, the orders table can have an orders_calculations.js file, wherein each calculation has a function by the same name. The function should return a value, which will be displayed wherever the calculation is referenced.

Delete a Calculation

Similar to editing a Calculation, open the Table Editor, select the one you need to delete, and click on the button "Remove selected" at the bottom of the list (you will be prompted to confirm)

When deleting a calculation the supporting JavaScript function will also be deleted.

Unresolved Data Bindings When you edit the name of a calculation or delete it, you may also create errors in your application if you have objects bound to it, such as a field on a form. These errors can be resolved in Servoy Developer.

Remarks

The scope of the JavaScript function is an individual record object. Therefore any of the record's other data providers (including other calculations), and Relations are immediately available, as well as global variables and globally related Relations.

Performance

Calculations may be called often. Therefore, developers should use discretion when implementing their JavaScript function. Most in-memory operations are very fast. However, actions that result in SQL queries or file operations may be slower and less predictable. For example, a calculation may be shown in a Grid, in which case it may be called hundreds of times.

Pro Tip

Calculations can be created with no return value so they can be used as extra columns at record level to store temporary data that belongs to a particular record.

/**
 * A calculation of type INTEGER with no return value 
 * that can be used in a grid to give users the possibility to 
 * select/mark individual records for later use
 * without having to define a "selected" column in the database
 *
 * i.e. a list of documents to be approved can have a checkbox so the user
 * can select individually which ones should be marked as approved
 *
 * @properties={type:4,uuid:"60B354B2-A0D6-4BEC-A3BF-D9ED6A008FFA"}
 */
function selected() {
    // No return value. Use this calculation as a dataprovider in a grid
}

/**
 * Function in a form to loop all records in the foundset 
 * and approve only the selected ones
 */
function approveDocuments() {
    for (var i = 1; i <= foundset.getSize(); i++) {
        var record = foundset.getRecord(i);
        if (record.selected) {
            approveDocument(record);
        }
    }
}

Additional Resources

Getting Started Guide

We highly recommend trying our Get Started Guide, which has both a simple example of a calculation and an advanced example.

Reference Documentation

Please refer to the reference guide section on Calculations for a complete list of properties.