Calculated fields

When you create a new field, you can mark this field as calculated by ticking the calculated box. This means that instead of entering a new value for this field, the value is calculated from the other fields in the row based on a formula you provide. Calculate fields still have a type (string, integer, boolean) etc. and the type determine which formulas are valid.

Formulas are JavaScript expressions. This means you can use simple arithmetic, round parentheses to bracket expressions, use row values as variables, and you can use comparison operators < > etc.

Formulas are not JavaScript statements. This means that they should not include a semicolon at the end and they cannot contain multi-line instructions. In particular, you cannot use loops. Instead of if expressions , you should use the ternary operator ?:. As an example: given a table with two ordinary fields x and y of type Float, here are some valid formulas:

  • x+y: a formula of type Float, the sum of x and y
  • x>=y: a formula of type Bool, which is true if x is greater than or equal to y
  • x>=y ? "x is larger (or equal)" : "y is larger": a formula of type String, which equals x is larger (or equal) if X is greater than or equal to y or y is larger if y is greater than x.

The input dialogue for the formula will show some valid examples based on the type chosen for the formula and the other fields present in the table.

Functions

You can use built-in JavaScript functions such as Math.sqrt(x) for the square root of a field x or s.toLowerCase() for the lowercase of a string field s.

Additional functions can be provided by plug-ins. Functions from plug-ins are declared either as synchronous or asynchronous. Synchronous functions are the standard functions which perform a simple transformation of the input. Asynchronous functions typically contact some external resource, for instance make an API call, to obtain their value. Synchronous and asynchronous functions are used in the same way, by simply calling them and using their result. For instance, say you have a function TwitterFollowers provided by a plug-in that returns the number of followers of the account named in the argument. If you have a table with a field twitter_handle, and you want to calculate a Boolean calculated field isFamous based on whether the account has more than 10,000 followers, your formula for isFamous would be TwitterFollowers(twitter_handle)>10000.

(If you are used to programming in JavaScript, you may find it unusual that synchronous and asynchronous functions are treated in the same way. This is implemented by parsing the formula and inserting awaits before any asynchronous function calls and then marking the whole formula as asynchronous.)

Stored vs not stored calculated fields

Calculated fields are either stored or not stored. Stored calculated fields are calculated when the row is inserted or updated and stored in the database. Not stored fields are as the name indicates not stored in the database and recalculated every time a row is retrieved for display - that is, far more frequently. Choosing between stored and not stored is thus a trade-off between compact storage and faster display. However, there are some important differences between stored and not stored calculate fields:

  • Stored fields can be used in joins and aggregations. Non-stored fields cannot
  • Stored fields can use asynchronous functions. This is because asynchronous function evaluation is assumed to consume significantly more resources, not only compute time, but also count towards rate limits in APIs.