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 change the result.

Try a calculation

In a Books table, add Integer fields named Pages and Pages read. Create a Formula field with result type integer and 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 saving the field.

Language boundary

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

Expression syntax

• Reference a Field with its exact display name: "Reading seconds".
• Use SQLite || for text concatenation.
• Test null 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 uses +, -, *, /, and %; comparisons use =, ==, !=, <>, <, <=, >, and >=.

The declared result type must still be exactly 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. Their accepted calls use SQLite 3.45 value, null, conversion, indexing, and formatting behavior. Notable details:

• CONCAT converts non-null values to text and skips nulls.
• SUBSTR and SUBSTRING are one-based, like SQLite; negative positions and lengths retain SQLite behavior.
• Built-in LOWER and UPPER fold ASCII characters only.
• FORMAT and PRINTF use SQLite formatting, not the device locale.
• Date/time functions require a time value. Formats and modifiers must be string literals; now, localtime, utc, and auto are rejected so results do not depend on the clock, timezone, or Host SQLite version. UNIXEPOCH also rejects subsec and subsecond so its result remains an Integer.

Reading-duration example

This expression formats 309299 seconds as 85小时55分钟, rounding the 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 the minute component when you want to discard remaining seconds; for 309299, that result is 85小时54分钟.

Deliberately unsupported

Formula excludes SQL statements, subqueries, aggregate/window functions, table-valued functions, randomness, connection-state functions, extension loading, JSON functions, Host-defined functions, and functions added only by a newer SQLite version. Former Eidos-only names such as IF, IS_NULL, LOWER_ASCII, and DATE_ADD_DAYS are not aliases; use the SQLite spelling above.

When updating an older draft File, migrate expressions explicitly:

Former spelling	SQLite profile spelling
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)