Writing a Formula for a Calculated field

This section explains how to write a Formula for a Calculated Field when designing a Form in ActivityInfo. Calculated Fields can calculate a value based on a Formula and the value of other fields. Depending on your needs you can write a Formula to automate the calculations and save time. To write a Formula you need to add a Calculated field to your Form and open the Properties tab of the field.

A list of all available Functions can be found in the Formulas Reference Manual.

How to write a Formula
  • To write a Formula, you can either use the Formula section on the Properties tab (e.g for a short formula) or open the Formula editor (e.g. for a longer formula or for a full list of your options) from the Properties tab of a Calculated field.
Understanding the components

A Formula consists of:

Symbols

Symbols are references to other fields which we will use in our calculations. Symbols can be field Labels or Codes. Every calculation will use the value of the field on that Record to produce a result.

Constants

Constants are constant values which do not change. Every calculation will use the same value to produce a result.

Constants can be for example a text string, numbers, logical or boolean value (e.g. TRUE/FALSE).

Functions

Functions are instructions to perform a specific task. These tasks can be mathematical operations (e.g. addition + or subtraction -), finding a word within a piece of text, or checking a value is greater than another.

The values which a function operates on are called arguments. These arguments can be Symbols or Constants.

Once the function has performed its task using the provided arguments, it returns a result. This result can then be used as an argument to another function, or returned as the Calculated Value.

In our example the Calculated field will add up - using the addition function ("+") - the amount Desks, Chairs, Blackboards and Chemistry lab equipment delivered using the codes "D", "C", "Bl"and "Ch" respectively. This will happen after the user adds a Record and fills in the Quantity fields.

Using a field of a Form as a Symbol

Apart from assigning a Code to a field, there are other ways to use a field as a Symbol. When writing a Formula use one of the following ways to add as many symbols as needed in it.

Use the field Label

  • If a Label is a single word (e.g. "Desks"), then we can use the Label directly in the Formula.
  • If a Label contains one or more spaces (e.g. "Number of Desks delivered"), then we can use the Label by surrounding it with square brackets - i.e. [Number of Desks delivered].

Use the field Code

  • If a field has a defined Code (e.g. "Number of Desks delivered" has Code "D"), then we can include the Code directly in the Formula.
Using a Constant

To use a Constant in our Formula, we can:

  • Include text strings by surrounding them with double-quotes (e.g. "Your Text Here")
  • Include numbers by including them directly (e.g. 10)
  • Include Logical or Boolean values (e.g. TRUE/FALSE) by including them directly, fully capitalized.
Using a Function

To use a Function in our Formula, we must:

  1. Include the Function Name (e.g. IF)
  2. Include all of the Function Arguments within parentheses - e.g. IF(arg1, "yes", "no"). Function Arguments can be Symbols or Constants.

A list of all available Functions can be found in the Formulas Reference Manual.

Using the Subform fields in a Formula

You can write a Formula in your Parent Form to make calculations based on the Records added in the Subform of the Parent Form. You can only use Aggregate Functions with Records of Subforms.

Add a Calculated field in the Parent Form and in the Formula editor use functions (such SUM, AVERAGE etc.) along with the Code or Label of the Records of the Subform you want to use.

You can currently use the Records of a first level Subform. If a Subform is within a Subform you will not be able to use the Records of the second level Subform.

How to verify a Formula
  • Once you have created your Formula, the system will inform you whether there are any issues with the syntax of your Formula. If there are no issues with the syntax of your Formula, it will appear with a green glow.
  • If there are syntax issues with the Formula the system will not allow you to save the field.
  • Logical inaccuracies or incorrect references are not checked by the system. Therefore, you may find that after creating your Calculated Field, it will only return "#VALUE!" values.

If this occurs, return to the Form Designer and check your Calculated Field Formula:

  • Ensure you are using the correct Symbols
  • Ensure your Function Arguments are of the correct type