What this is, and who it is for
A formula makes a field's value from other fields: a body mass index from weight and height, an age from a date of birth, a number of days between two visits, a band from a score. The person entering data does not type it; they see it, tagged Calculated. Formulas are written by the eCRF Designer in the builder and are part of the version that is tested and signed off. If a calculation should refuse or query something rather than make a value, use a check or a rule.
Where to find it
Builder → select a Number field → Logic → Calculation formula → Set formula opens the Formula Builder. For a table, set the formula of a Calculated column from the column's Set formula button (Tables and repeating groups). Only a Number field offers a formula; no other type does.
What you can do
- Open the Formula Builder. The left panel lists the variables you can use (the Number fields and Date fields of the form, not the field itself) and nine quick recipes.
- Click a variable or a function to insert it where the cursor is, or type in the expression box. The operator pad inserts
+ - * / ^ ( ). - Type a sample value for each variable under test inputs and read the live result. Cannot evaluate — check expression or test values means a variable is blank, the expression is incomplete, or something divides by zero.
- Set Decimal precision (0 to 10, blank for no rounding) and choose Apply formula. Remove formula takes it off.
The result appears on the canvas as the field's value, and the field becomes read-only for the person entering data.
Every option
The language
| Part | Notes |
|---|---|
| Variables | A field's variable name, such as WEIGHT. A name that is not on the form is an error (Formula for "BMI" references unknown variable "WGT") and blocks making the form active |
| What a variable can be | The picker lists Number, Scale, Date and Date & Time fields (and Range, which reads as blank because it is stored as from|to text). By typing the name you can also use a Checkbox or Toggle (true is 1, false is 0) and a Radio or Dropdown whose option values are all numbers. Text, Multi-Select, Time, Email and Phone fields have no numeric reading and are blank in a formula |
| Numbers | 12, 0.5, .5 |
| Arithmetic | + - * / ^. ^ is power and binds to the right (2^3^2 is 2^9). - before a number or bracket is a sign: -2^2 is -4, 2*-3 is -6 |
| Comparison | > < >= <= = <> (and !=). They give 1 for true and 0 for false, and bind looser than arithmetic, so A + 1 > B means (A + 1) > B. They are not on the operator pad but the engine accepts them |
| Brackets | ( ). Function arguments are separated by a comma |
| True and false | Any value other than 0 is true |
The 18 functions
| Function | Arguments | Result |
|---|---|---|
ABS(x) | 1 | Absolute value |
SQRT(x) | 1 | Square root |
FLOOR(x), CEIL(x) | 1 | Round down, round up to a whole number |
ROUND(x, d) | 2 | Round x to d decimal places |
MIN(a, b), MAX(a, b) | 2 | The lesser, the greater |
AGE_YEARS(date) | 1 | Whole years from the date to today |
AGE_MONTHS(date) | 1 | Whole months from the date to today |
YEARS_BETWEEN(a, b) | 2 | Whole years from date a to date b (age at consent: YEARS_BETWEEN(DATE_OF_BIRTH, CONSENT_DATE)) |
MONTHS_BETWEEN(a, b) | 2 | Whole months from a to b |
DAYS_BETWEEN(a, b) | 2 | Days from a to b |
TODAY_DIFF(date) | 1 | Days from the date to today (past is positive) |
TODAY() | 0 | Today's date as a number |
IF(cond, a, b) | 3 | a when cond is not 0, otherwise b |
AND(a, b), OR(a, b) | 2 | 1 or 0 |
NOT(x) | 1 | 1 when x is 0, otherwise 0 |
Dates are read as calendar dates (year, month, day), so a result does not shift with the time zone, and an age is the younger age until the birthday has passed. DAYS_BETWEEN(a, b) is negative when b is earlier than a. "Today" is the calendar date where the value is being calculated.
Examples the builder offers: AGE_YEARS(DATE_OF_BIRTH), AGE_MONTHS(DATE_OF_BIRTH), WEIGHT / (HEIGHT / 100) ^ 2 (BMI), TODAY_DIFF(VISIT_DATE), DAYS_BETWEEN(START_DATE, END_DATE), YEARS_BETWEEN(START_DATE, END_DATE), YEARS_BETWEEN(DATE_OF_BIRTH, CONSENT_DATE), MONTHS_BETWEEN(START_DATE, END_DATE), MIN(SCORE, 100). A band is a chain of IF: IF(BMI >= 30, 3, IF(BMI >= 25, 2, 1)).
Blank inputs: a calculation is never made from nothing
An input is blank when it is unanswered, empty, or has no number in it. A blank is unknown, so:
- arithmetic and comparisons with a blank give a blank (the field stays empty, with no error). A total with one item unanswered is blank, not a partial sum;
- every function is strict:
ABS,SQRT,MIN,MAX,ROUND, the date functions andNOTgive blank if any argument is blank.MINandMAXdo not skip a blank the way a spreadsheet does; IF,ANDandORdecide what they can:IFis blank only when its condition or its chosen branch is blank;ANDis 0 when either side is 0;ORis 1 when either side is not 0; otherwise blank. SoIF(PREG = 1, GESTWK, 0)is 0 for a participant who is not pregnant even thoughGESTWKis hidden and empty;- a ticked checkbox counts as 1 and an unticked one as 0.
Errors
A division by zero, an unknown variable, an unknown function, an unbalanced expression or a result that is not a finite number is an error, not a blank. The field is left empty and the form reports it. In the builder these are also errors in the issues list: Formula … has a parse error, references unknown variable, is part of a circular formula dependency, and a formula that uses a deprecated field (this value will be blank). A form with such an error cannot be made active.
Order and circular formulas
A formula that uses another formula's field is calculated after it. Two formulas that depend on each other are a circular dependency: both are flagged, and neither is calculated.
Calculated table columns
A Calculated column has a formula whose variables are the table's own columns, taken from the same row. It may instead (or also) have value assignment rules (Conditional logic). A calculated column is not entered by a person and has no edit checks.
What is stored, and who checks it
The computed value is saved with the record like any other value. When the record is saved, the server recalculates every formula field and every calculated table cell with the same engine, and stores its own result. If it differs from what the browser sent (an old tab, a different build, a hand-made request), the stored value is the server's and the difference is written to the audit trail (form.derived_value_overridden) with the value sent, the value stored and the reason. The server does not reject the save for a difference, because a difference means the two sides run different builds, and refusing would stop entry at every site until each browser reloaded.
What the system records
The formula (its expression and precision) is part of the form's design revision and snapshot. Each calculated value is an item of the record. A value the server replaced is form.derived_value_overridden. The age functions depend on today's date, so a stored age is the age on the day it was calculated, not a value that keeps changing.
What you cannot do, and why
- Put a formula on a field that is not a Number. Calculation is offered on Number only; use a value assignment for other types.
- Type over a calculated value. The field is read-only to the person entering data, so the record shows only what the design calculated.
- Use text in a formula. Only numbers and dates are variables; a text field cannot be a variable.
- Count answered fields. There is no
COUNTorSUM; every function takes one to three numbers. - Make a result that depends on today without it changing.
TODAY()and the age functions read the date when the value is calculated.
Compliance Q&A
No. The field is read-only for the person entering data, and the server recalculates it from the other values on every save with the same engine and stores its own answer; any difference is recorded in the audit trail.
Limits: a calculation is only as right as its design. Test it in UAT with known inputs (the Formula Builder's test inputs help); the system does not check a formula's meaning, only that it can be evaluated.
Evidence: server/src/modules/study/data-capture/domain/formula-recompute.tssave-responses.ts (form.derived_value_overridden)client core/engine/formula-engine.ts (same engine vendored into the server)
Related
Field properties · Conditional logic · Tables and repeating groups · Validation, edit checks and their severities · Audit events