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.
?:. As an example: given a table with two ordinary fields
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 largerif 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.
Math.sqrt(x) for the square root of a field
s.toLowerCase() for the lowercase of a string field
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
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.