Writing an Expression

This section explains how to write an Expression 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 an Expression to automate the calculations and save time.

To write an expression you need to add a Calculated Field to your Form. You can find the Expression Property in the Properties Panels on the left-hand side of the Form Designer.

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

How to write an Expression

On the Properties Panel, find the "Expression" Property Field. This field defines the Formula we use to calculate the Calculated Field value.

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 Form 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.
  • 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.
Using a Symbol
Referencing a Form Field

To use a Form Field as a Symbol in our Formula, we can:

Use the Form Field Label

  • If a Label is a single word (e.g. "Quantity"), then we can use the Label directly in the Formula.
  • If a Label contains a space (e.g. "Total Quantity"), then we can use the Label by surrounding it with square brackets - i.e. "[Total Quantity]".

Use the Form Field Code

  • If a Form Field has a defined Code (e.g. "Total Quantity" has Code "total"), then we can include the Code directly in the Formula.
Referencing a Sub-Form Field

Sub-Form Fields can be included in order to calculate aggregate values. That is, we can calculate a result from all of the values of a Sub-Form Field, given by their Sub-Form Records. It is not possible to reference a specific Sub-Form Record in this way.

To use a Sub-Form Field as a Symbol in our Formula, we must include a Sub-Form Symbol and a Field Symbol. Similarly to our Form Field Symbols, we can:

Use the Sub-Form Field Label

  • If a Sub-Form Label (e.g. "SubForm") and the Field Label (e.g. "Quantity") are single words, then we can use both Labels directly in the Formula.
  • We first include the Sub-Form Label, followed by a ".", and then finally include the Field Label. 
  • If a Sub-Form Label (e.g. "Sub Form") or a Field Label include a space, then we can use both Labels by surrounding them with square brackets - i.e. "[Sub Forms]".
  • We first include the Sub-Form Label, followed by a ".", and then finally include the Field Label. 

Use the Sub-Form Field Code

  • If a Sub-Form Field has a defined Code (e.g. "Total Quantity" has Code "total"), then we can include the Code directly in the Formula.
  • We first include the Sub-Form Label, followed by a ".", and then finally include the Field Code.
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. 12)
  • 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.

Verifying 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.

However, logical inaccuracies or incorrect references are 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 expression:

  • Ensure you are using the correct references
  • Ensure you are using Sub-Form Fields only for Aggregate Functions  
  • Ensure your Function Arguments are of the correct type