Calculated Fields in Wikis

Some things a document needs to say are not questions you asked. "Is this beneficiary a minor?" is not on the form – the form asked for a date of birth. A calculated field works the answer out before the document renders, and then you use it like any other variable.

Important: This article covers wiki calculated fields. Automize has a second, larger formula system used by processes and bots – see What Calculation Functions Are Supported. The two are not interchangeable: a function from the process list will not parse in a wiki. The complete wiki list is below.

Creating One
Scope – Per Doc or Per Row
The Entity Rule
Writing a Formula
Function Reference
Declaration Order
Worked Examples
Common Mistakes

Creating One

Open the wiki and click Edit → Build.
Open the Calc tab in the sidebar.
Click + Formula.
Give it a name, choose a scope, and type the formula.
Use Preview to check the result against a real submission.

Type an uppercase letter inside the formula box to get suggestions with syntax and descriptions.

Scope – Per Doc or Per Row

Scope	Produces	Used as
Per doc	One value for the whole document	`{name}` or `{IF:name="x"}`, anywhere
Per row	A new column on every row of one list	`{.name}` or `{IF:.name}`, only inside `{EACH:list}`

has_minors   (per doc)   COUNTIF({children}, AGE({.id_or_dob}) < 18) > 0
is_minor     (per row)   AGE({.id_or_dob}) < 18

A per-row field becomes an ordinary column. Once is_minor exists on children, {.is_minor} works inside {EACH:children} exactly like a column the form collected.

The Entity Rule

A per-row calculated field asks which list it attaches to – its entity. This is the rule that catches most authors:

Warning: The entity must be the same list you loop over. A field with entity beneficiaries is invisible inside {EACH:residual_estate} – the column simply is not there, and the reference renders as raw text.

One calculated field attaches to exactly one list. If the same person appears in two lists and you need the same test in both, create it twice, once per list.

Writing a Formula

Referring to values

Reference	Means	Where it works
`{name}`	A form field, or another per-doc calculated field	Anywhere
`{.column}`	The current row's column	Per-row formulas only
`{list.column}`	A whole column, every row	Only as an argument to a function that takes a column

Operators

Group	Symbols
Arithmetic	`+` `-` `*` `/`
Comparison	`=` `!=` `<` `>` `<=` `>=`
Grouping	`( )`

Literals: 42, 3.14, -5, "text", TRUE, FALSE, NULL.

Note: Boolean logic in a formula is functions, not keywords – AND(a, b), OR(a, b), NOT(a). There is no && or || here. (In the wiki body, conditions do use the words AND and OR – see Show or Hide Parts of a Document.)

Function Reference

Logical

Function	Syntax	Notes
`IF`	`IF(condition, then, else)`	Three arguments only. For multi-way, use `SWITCH`.
`AND`	`AND(a, b, …)`	True when every argument is true.
`OR`	`OR(a, b, …)`	True when any argument is true.
`NOT`	`NOT(value)`	Inverts true and false.
`IFNULL`	`IFNULL(value, fallback)`	Uses the fallback when the value is missing, empty or an empty list. A real `0` or a negative number passes through.
`SWITCH`	`SWITCH(value, case1, result1, …, default)`	First match wins. The trailing default is optional.

Working across a list

Function	Syntax	Returns
`SUM`	`SUM({list.column})`	Numeric total; non-numeric rows skipped.
`COUNT`	`COUNT({list})`	Number of rows. Blank rows are not counted.
`COUNTIF`	`COUNTIF({list}, test)`	Rows where the test is true. The test uses `{.column}`.
`ANY`	`ANY({list}, test)`	True when at least one row passes the test.
`LEN`	`LEN(list_or_text)`	Rows in a list, or characters in a piece of text.
`FIRST`	`FIRST({list.column})`	The first row's value. Empty list gives nothing.
`LAST`	`LAST({list.column})`	The last row's value.
`MIN`	`MIN({list.column})`	Smallest number in the column.
`MAX`	`MAX({list.column})`	Largest number in the column.

Sets and lookups

Function	Syntax	Returns
`IN`	`IN(value, {list.column})`	True when the value appears anywhere in that column.
`LOOKUP`	`LOOKUP(value, {list.key}, {list.result})`	Finds the first row whose key matches and returns another column from it. Both references must be on the same list.
`UNIQUE`	`UNIQUE({list.column})`	The column with duplicates removed.
`SORT`	`SORT({list.column})`	Sorted ascending – numerically when all values are numbers.

Text

Function	Syntax	Notes
`CONTAINS`	`CONTAINS(text, needle)`	Case-insensitive "does this text contain".
`CONCAT`	`CONCAT(a, b, …)`	Joins values into one piece of text.
`TRIM`	`TRIM(text)`	Removes leading and trailing spaces.
`UPPER`	`UPPER(text)`	Upper case.
`LOWER`	`LOWER(text)`	Lower case.
`JOIN`	`JOIN({list.column}, ", ")`	Turns a column into one readable string. Pairs well with `UNIQUE` and `SORT`.

Numbers

Function	Syntax	Notes
`ROUND`	`ROUND(number, decimals)`	`0` gives a whole number; negatives round to the nearest 10, 100 and so on.
`NUMBERVALUE`	`NUMBERVALUE(text)`	Turns text into a number, stripping `%`, currency symbols and thousands separators. `"R 1,000.50"` becomes `1000.50`.

Dates and South African ID numbers

Function	Syntax	Returns
`AGE`	`AGE(date_or_id)`	Age in years from a date of birth or an SA ID number.
`ID_DOB`	`ID_DOB(id)`	Date of birth from a 13-digit SA ID.
`ID_GENDER`	`ID_GENDER(id)`	`"male"` or `"female"` as registered with Home Affairs.
`ID_CITIZEN`	`ID_CITIZEN(id)`	`"sa"` or `"permanent-resident"`.
`ID_VALID`	`ID_VALID(id)`	True when the number is well-formed and its checksum passes.
`TODAY`	`TODAY()`	Today's date.
`WORDS`	`WORDS(number)`	A number written out – `21` becomes `"twenty-one"`. Up to 999,999.

Note: Anything not on this list is not available in a wiki formula. Common Excel functions such as ABS, AVERAGE and IFERROR will produce an "Unknown function" error. The set is kept small on purpose; ask if you need one added.

Declaration Order

Calculated fields may use earlier ones, so order matters. A field can only reference a field declared above it – a reference to one below is rejected when you save, naming the two fields involved.

1.  is_minor      (per row on children)   AGE({.id_or_dob}) < 18
2.  minor_count   (per doc)               COUNTIF({children}, {.is_minor})
3.  needs_trust   (per doc)               {minor_count} > 0

Worked Examples

Percentages that must total 100

share_total   (per doc)   SUM({beneficiaries.percentage})

{IF:share_total!=100}NOTE: shares total {share_total}%, not 100%.{/IF}

A field that may be blank or zero

A missing answer and a zero are different things, and comparing text to a number rarely does what you expect. Do it in one step:

IFNULL(NUMBERVALUE({estate_value}), 0) = 0

Pulling a value from another list

Instead of nesting loops just to read one value, match on a shared column:

trustee_for_this_child   (per row on children)
LOOKUP({.id_or_dob}, {trusts.child_id}, {trusts.trustee_name})

A readable sentence from a list

JOIN(SORT(UNIQUE({assets.category})), ", ")

Gives Immovable, Movable, Shares in one variable, with duplicates removed.

Equal shares check

shares_equal   (per doc)   MIN({beneficiaries.percentage}) = MAX({beneficiaries.percentage})

Common Mistakes

Symptom	Usual cause
"Unknown function"	A function from the process formula list, not the wiki list above.
The field is always empty inside a loop	The entity does not match the list you are looping over.
Parse error mentioning a list	Filter syntax such as `{list.column="value"}` is for the wiki body, not formulas. Use `COUNTIF({list}, {.column}="value")`.
A test against an empty field never matches	Use `IFNULL(…)` – an unanswered question is not the same as an empty string here.
`SUM(a, b, c)` does not work	`SUM` totals one column of a list. To add values together, write `a + b + c`.
Save rejected, naming another field	Declaration order – move the field being referenced above the one using it.

Next: Show or Hide Parts of a Document · Repeating a Section for Every Row · back to Wiki Logic.

Calculated Fields in Wikis

Calculated Fields in Wikis

Table of Contents

Creating One

Scope – Per Doc or Per Row

The Entity Rule

Writing a Formula

Referring to values

Operators

Function Reference

Logical

Working across a list

Sets and lookups

Text

Numbers

Dates and South African ID numbers

Declaration Order

Worked Examples

Percentages that must total 100

A field that may be blank or zero

Pulling a value from another list

A readable sentence from a list

Equal shares check

Common Mistakes

See it working on your own data