Formula expressions

Use a Formula field to calculate a value from other fields in the same table. The result is read-only. Change the source fields to update the result.

Try a calculation

In a Books table, add two Integer fields named Pages and Pages read. Create a Formula field with result type integer and enter this expression:

"Pages" - "Pages read"

Enter 120 for Pages and 45 for Pages read. The result is 75. Change Pages read to 60. The result becomes 60. If either input is empty, this expression returns null.

Check: Review the expression preview before you save the field.

Language boundary

Formula uses quoted field names and a fixed set of SQLite 3.45 scalar functions. The Runtime parses the expression and checks data types before generating SQL. A Formula cannot execute a full SQL statement.

Expression syntax

• Reference a Field with its exact display name: "Reading seconds".
• Use SQLite || for text concatenation.
• Check null values with IS NULL or IS NOT NULL.
• Convert values with CAST(value AS TEXT|INTEGER|REAL).
• Use IIF(condition, trueValue, falseValue) or searched CASE WHEN condition THEN value ELSE value END.
• Arithmetic supports +, -, *, /, and %.
• Comparisons support =, ==, !=, <>, <, <=, >, and >=.

The declared result type must be one of: text, number, integer, checkbox, date, datetime, or url.

Supported SQLite functions

Category	Functions
Null and choice	COALESCE, IFNULL, IIF, NULLIF
Numeric	ABS, CEIL, CEILING, FLOOR, ROUND, SIGN, scalar MAX, scalar MIN
Text	CHAR, CONCAT, CONCAT_WS, FORMAT, PRINTF, INSTR, LENGTH, OCTET_LENGTH, LOWER, UPPER, LTRIM, RTRIM, TRIM, REPLACE, SUBSTR, SUBSTRING, UNICODE
Pattern	GLOB, LIKE
Inspection	HEX, QUOTE, TYPEOF
Date and time	DATE, DATETIME, TIME, JULIANDAY, UNIXEPOCH, STRFTIME, TIMEDIFF

Function names are ASCII case-insensitive. Accepted calls use SQLite 3.45 evaluation rules:

• CONCAT converts non-null values to text and skips null values.
• SUBSTR and SUBSTRING use 1-based indexing like SQLite. Negative offsets follow SQLite rules.
• Built-in LOWER and UPPER fold ASCII characters only.
• FORMAT and PRINTF use standard SQLite format strings.
• Date and time functions require a valid time value.
• Formats and modifiers must be string literals. The Runtime rejects now, localtime, utc, and auto to guarantee reproducible calculations.
• UNIXEPOCH rejects subsec and subsecond so its output remains an Integer.

Reading duration example

This expression formats 309299 seconds as 85小时55分钟, rounding remaining seconds to the nearest minute:

IIF(
  "Reading seconds" IS NULL,
  NULL,
  FORMAT(
    '%d小时%d分钟',
    FLOOR("Reading seconds" / 3600),
    ROUND(("Reading seconds" % 3600) / 60)
  )
)

Use FLOOR instead of ROUND for minutes if you want to discard remaining seconds. For 309299, that result is 85小时54分钟.

Deliberately unsupported features

Formula excludes full SQL statements, subqueries, and window functions. It excludes table-valued functions, randomness, and connection-state functions. It excludes extension loading and JSON functions. Names like IF, IS_NULL, and DATE_ADD_DAYS are not aliases. Use standard SQLite function names:

Previous syntax	SQLite standard syntax
IF(C, A, B)	IIF(C, A, B)
IS_NULL(X)	X IS NULL
LOWER_ASCII(X) / UPPER_ASCII(X)	LOWER(X) / UPPER(X)
DATE_ADD_DAYS(D, 7)	DATE(D, '+7 days')
DATE_DIFF_DAYS(A, B)	CAST(JULIANDAY(A) - JULIANDAY(B) AS INTEGER)
DATETIME_ADD_MILLISECONDS(D, 1000)	DATETIME(D, '+1 second', 'subsec')
DATETIME_DIFF_MILLISECONDS(A, B)	CAST((JULIANDAY(A) - JULIANDAY(B)) * 86400000 AS INTEGER)