Adding calculations to form fields

Calculations are a type of field automation. With the calculation action, a numeric field is filled in automatically with the result of a formula based on other numeric fields in the same form. Instead of calculating values in Excel and typing in only the result, you enter the raw measurements in the form and GIS Cloud calculates the rest.

This guide builds on Adding field automation to forms. A calculation is added to the automationRules of a field in JSON View, in the same way as the other automation actions, so read that guide first if you haven’t worked with automation rules before.

Formulas use a syntax similar to Excel, and support arithmetic, exponents, logarithms, rounding, constants, IF logic, AND/OR/NOT/ISNUMBER checks, MIN/MAX and TREND.

The JSON structure for calculations

A calculation is an automation rule whose action type is calculation. In the example below, the Area field is calculated by multiplying the Length and Width fields.

{
  "type": "numeric",
  "uuid": "45c48cce-2e2d-4fbd-9f1a-7e5b0c3d6a12",
  "name": "area",
  "persistent": false,
  "required": false,
  "readOnly": false,
  "title": "Area (m2)",
  "description": "",
  "automationRules": [
    {
      "condition": {
        "all": [
          {
            "field": "1b4e28ba-2fa1-41d2-883f-0016d3cca427",
            "as": "L"
          },
          {
            "field": "6fa459ea-ee8a-4ca4-894e-db77e160355e",
            "as": "W"
          }
        ]
      },
      "action": {
        "type": "calculation",
        "formulaRaw": "L * W"
      }
    }
  ]
}

The rule has two parts:

condition lists the fields the formula uses and gives each of them a short name (an alias).
action sets the type to calculation and contains the formula, written with those aliases.

Condition

In a calculation, the condition object declares the input fields of the formula. Each entry has two keys:

field is the UUID of the input field.
as is the alias you will use for that field in the formula.

"condition": {
  "all": [
    {
      "field": "1b4e28ba-2fa1-41d2-883f-0016d3cca427",
      "as": "L"
    },
    {
      "field": "6fa459ea-ee8a-4ca4-894e-db77e160355e",
      "as": "W"
    }
  ]
}

Important: In calculations, the condition works differently than in the other automation actions. It doesn’t decide when the action runs, and all doesn’t mean that all fields must be filled in. It only links aliases to fields. Always use the all key, but keep in mind that the value is recalculated whenever one of the input fields changes, and the formula itself decides what is calculated (for example, with ISNUMBER, described below).

Keep these rules in mind when declaring fields:

Every alias used in the formula must be declared in the condition.
Each alias must be unique within the rule. Use short names such as L, W or DEPTH.
Do not add a value key to the entries. That key is used only by other automation actions.

Action

"action": {
  "type": "calculation",
  "formulaRaw": "L * W"
}

The action has two keys:

type must be calculation.
formulaRaw contains the formula as text.

If the formula has an error (for example, a missing bracket or an alias that is not declared), the form will not be saved and you will see a message describing the problem.

Writing formulas

Formulas look like Excel formulas, with a few differences:

Don’t start the formula with =. Write L * W, not =L * W.
Refer to fields by their aliases, not by cell references such as A1.
Use a dot as the decimal separator: 3.14, not 3,14.
Write function names in capital letters, for example ROUND, IF, PI().
Spaces are optional: L*W and L * W give the same result.
Use brackets to control the order of operations: (L + W) * 2.

Leaving the field empty

Use “” (empty text) when the field should stay empty, as in Excel. Because the formula is written inside a JSON string, the quotes must be escaped with a backslash:

"formulaRaw": "IF(ISNUMBER(W), L / W, \"\")"

The calculated field also stays empty when:

a field used in the formula is empty or doesn’t contain a valid number (unless it is checked with ISNUMBER),
the calculation is not possible, such as a division by zero or LOG10 of zero or a negative number,
the result is not a number, for example a formula that returns only TRUE or FALSE.

Rounding

Results are not rounded automatically. A formula such as 10 / 3 gives 3.3333333333. Use ROUND to limit the number of decimals: ROUND(10 / 3, 2) gives 3.33.

Supported operators and functions

All examples below use two input fields with the aliases A and B, where A = 6 and B = 3.

Arithmetic operators

OPERATOR	MEANING	EXAMPLE FORMULA	RESULT
+	Addition	A + B	9
–	Subtraction	A – B	3
*	Multiplication	A * B	18
/	Division	A / B	2
^	Exponent	A ^ 2	36
– (in front of a value)	Negative value	-A	-6

Two things to watch out for with exponents, both of which work the same way as in Excel:

Several exponents are calculated from left to right: 2 ^ 3 ^ 2 gives 64. To get 512, write 2 ^ (3 ^ 2).
A minus sign in front of a value is applied before the exponent: -A ^ 2 gives 36. To get -36, write -(A ^ 2).

Comparison operators

Comparisons return TRUE or FALSE, so use them inside IF, AND, OR or NOT.

Operator	Meaning	Example formula	Result
=	Equal to	IF(A = B, 1, 0)	0
<>	Not equal to	IF(A <> B, 1, 0)	1
>	Greater than	IF(A > B, 1, 0)	1
<	Less than	IF(A < B, 1, 0)	0
>=	Greater than or equal to	IF(A >= 6, 1, 0)	1
<=	Less than or equal to	IF(B <= 2, 1, 0)	0

You can’t chain comparisons. Instead of 0 < A < 10, write AND(A > 0, A < 10).

Functions

Function	What it does	Example formula	Result
IF(condition, value_if_true, value_if_false)	Returns one value if the condition is met, and another if it isn’t	IF(A > 5, A * 2, 0)	12
Nested IF	Checks several conditions in order	IF(A > 10, 3, IF(A > 5, 2, 1))	2
AND(condition1, condition2, …)	TRUE if all conditions are met	IF(AND(A > 0, B > 0), 1, 0)	1
OR(condition1, condition2, …)	TRUE if at least one condition is met	IF(OR(A > 10, B > 10), 1, 0)	0
NOT(condition)	Reverses a condition	IF(NOT(A > B), 1, 0)	0
ISNUMBER(field)	TRUE if the field contains a number, FALSE if it is empty	IF(ISNUMBER(B), A * B, A)	18 (6 if B is empty)
MIN(value1, value2, …)	Smallest value	MIN(A, B, 10)	3
MAX(value1, value2, …)	Largest value	MAX(A, B, 10)	10
ROUND(value, decimals)	Rounds to the given number of decimals	ROUND(A / 7, 2)	0.86
LOG10(value)	Base-10 logarithm	LOG10(A + 4)	1
PI()	The constant π (3.14159…)	ROUND(PI() * A ^ 2, 2)	113.1
TREND(known_y, known_x, new_x)	Estimates a value from a set of known points	TREND({0, 20, 40}, {0, 4, 8}, B)	15

The number of decimals in ROUND must be a whole number written directly in the formula, such as 2. PI() must always be written with brackets.

Using ISNUMBER to choose between formulas

ISNUMBER lets you use a different formula depending on which fields are filled in. In the example below, the result is calculated from the lab value (LAB) if it was entered, from the estimated value (EST) if it wasn’t, and stays empty if neither is entered:

“formulaRaw”: “IF(ISNUMBER(LAB), LAB * 2, IF(ISNUMBER(EST), EST * 2, \”\”))”

TREND

TREND takes two lists of the same length (at least two values each) and a new value:

known_y is the list of known results,
known_x is the list of matching known inputs,
new_x is the value you want to find the result for.

Lists are written in curly brackets with values separated by commas, for example {0, 20, 40}. In the example above, the known points are (0, 0), (4, 20) and (8, 40), so for B = 3 the result is 15.

Chained calculations

A calculated field can be used as an input for another calculation. You declare it in the condition like any other field. For example, if Area is calculated from Length and Width, Volume can then use Area and Depth:

"condition": {
  "all": [
    {
      "field": "45c48cce-2e2d-4fbd-9f1a-7e5b0c3d6a12",
      "as": "AR"
    },
    {
      "field": "8f14e45f-ceea-467a-9575-3a5c1d2b1e01",
      "as": "D"
    }
  ]
},
"action": {
  "type": "calculation",
  "formulaRaw": "AR * D"
}

When you change Length, Area is recalculated first and Volume right after it. If you clear Length, both Area and Volume become empty.

Each field in the chain must come after the fields it uses, so a calculation can never refer back to itself.

Example: Area, volume and size class are calculated from measured length, width and depth

The form below has three input fields and three calculated fields:

Area = Length × Width
Volume = Area × Depth, rounded to 2 decimals, and left empty until Depth is entered
Size class = 3 if Volume is over 10, 2 if it is over 5, otherwise 1

For example, if you enter Length 4, Width 2.5 and Depth 0.8, the form fills in Area 10, Volume 8 and Size class 2.

{
  "name": "Calculation example - Excavation volume",
  "title": "Calculation example - Excavation volume",
  "description": "Area, volume and size class are calculated automatically from the measured length, width and depth",
  "items": [
    {
      "type": "numeric",
      "uuid": "1b4e28ba-2fa1-41d2-883f-0016d3cca427",
      "name": "length",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Length (m)",
      "description": ""
    },
    {
      "type": "numeric",
      "uuid": "6fa459ea-ee8a-4ca4-894e-db77e160355e",
      "name": "width",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Width (m)",
      "description": ""
    },
    {
      "type": "numeric",
      "uuid": "8f14e45f-ceea-467a-9575-3a5c1d2b1e01",
      "name": "depth",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Depth (m)",
      "description": ""
    },
    {
      "type": "numeric",
      "uuid": "45c48cce-2e2d-4fbd-9f1a-7e5b0c3d6a12",
      "name": "area",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Area (m2)",
      "description": "",
      "automationRules": [
        {
          "condition": {
            "all": [
              {
                "field": "1b4e28ba-2fa1-41d2-883f-0016d3cca427",
                "as": "L"
              },
              {
                "field": "6fa459ea-ee8a-4ca4-894e-db77e160355e",
                "as": "W"
              }
            ]
          },
          "action": {
            "type": "calculation",
            "formulaRaw": "L * W"
          }
        }
      ]
    },
    {
      "type": "numeric",
      "uuid": "d3d94468-02a4-4ad3-a5c9-0f4e7b8a9c23",
      "name": "volume",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Volume (m3)",
      "description": "",
      "automationRules": [
        {
          "condition": {
            "all": [
              {
                "field": "45c48cce-2e2d-4fbd-9f1a-7e5b0c3d6a12",
                "as": "AR"
              },
              {
                "field": "8f14e45f-ceea-467a-9575-3a5c1d2b1e01",
                "as": "D"
              }
            ]
          },
          "action": {
            "type": "calculation",
            "formulaRaw": "IF(ISNUMBER(D), ROUND(AR * D, 2), \"\")"
          }
        }
      ]
    },
    {
      "type": "numeric",
      "uuid": "9bf31c7f-f062-4369-8b2c-5e6d7a8f9b34",
      "name": "size_class",
      "persistent": false,
      "required": false,
      "readOnly": false,
      "title": "Size class",
      "description": "",
      "automationRules": [
        {
          "condition": {
            "all": [
              {
                "field": "d3d94468-02a4-4ad3-a5c9-0f4e7b8a9c23",
                "as": "V"
              }
            ]
          },
          "action": {
            "type": "calculation",
            "formulaRaw": "IF(V > 10, 3, IF(V > 5, 2, 1))"
          }
        }
      ]
    }
  ]
}

Note how each calculated field comes after all the fields it uses, and how the condition of each rule refers to the UUIDs of those fields.

General guidelines

Calculations follow the same general guidelines as the other automation actions. They work both in the Mobile Data Collection app (version 10.6.103 or above) and in the web apps (Map Editor), they can only use fields that come before the calculated field in the form, and they can’t be set on fields with the Persistent or Default value options enabled.

Differences from the other automation actions

Field types: calculations can only be set on numeric fields, and all fields used in the formula must also be numeric.
Condition: the condition only declares the input fields. It doesn’t decide when the calculation runs (see the Condition section above).
Manual changes: once a value is calculated, you can still change it manually.

Other things to keep in mind

A calculation can only use fields of the same feature. Values from other features, layers or projects can’t be used.
Changing a formula doesn’t recalculate existing features. Their values are updated only when the feature is opened and one of its input fields is changed.
Calculations don’t run when features are imported (for example, from CSV) or created through the API.
There is no preview of the calculation in the Forms Manager. To test a formula, save the form, add a new feature and enter some values.
The formula syntax is similar to Excel, but not all Excel functions are supported. Cell ranges (such as A1:A10), text values, percentages (such as 5%) and lookup functions (such as VLOOKUP) can’t be used.
Your JSON needs to stay valid, ie. keep track of the brackets, commas and escaped quotes (\”\”). You’ll be notified if you try to save an invalid JSON.