Writing a Formula

Depending on your needs you can write a Formula to automate the calculations and save time. This section explains how to write a Formula for a Calculated Field or for a validation or relevance rule when designing a Form in ActivityInfo.

Calculated Fields can calculate a value based on a Formula and the value of other fields.  To write a Formula you need to add a Calculated field to your Form and open the Field card editor of the field. Relevance and Validation rules can be further defined thanks to Formulas.

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

Learn everything you need about Formulas in Validation and Relevance rules in our Webinar  "Using formulas in validation rules and relevance rules".

Do you wish to understand better Regular Expressions (RegEx) so as to use them in your Formulas in Validation rules? Make sure to check our Webinar "Learning Regular Expressions for validation rules and quality data".

How to write a Formula
  • To write a Formula, you need to add a Calculated field. You can either use the Formula section on the Field Card editor (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) by clicking on "Formula editor".
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 enters 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].
  • If a Label includes special characters (that is characters that are not alphanumeric, such as ?,!, etc.) then it should be within square brackets [ ]
  • If a Label consists of 20 or more characters, it will be converted into a field Id.

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 as SUM, AVERAGE etc.) along with the Code of the field of the Subform and the Subform name 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 Design page and check your Calculated field Formula:

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