Rules: validation & keys
Rules keep your data clean — required fields, bounds, patterns, keys and more. Add them on a Rules_ sheet (the singular Rule_ and Validation_ also work), one rule per row, across these columns: Field, Rule Type, Value, Message, Section, Form. The Rule Type says what to enforce:
| Rule Type | Effect |
|---|---|
| required | Field must have a value. The Value column may add a comparison — e.g. on end_date: "greater or equal to start_date". |
| min / max | Bounds. The limit can be a number, an ISO date, or another field's name ("end_date | min | start_date" reads end ≥ start, date-aware). Only a numeric min/max also makes the field required. |
| min_length / max_length | String length bounds. |
| pattern | Value must match a regular expression. |
| key | Marks the field as the form's business key — it becomes required, and imports match existing records on it. A form can have only one key. |
| subkey | Secondary part of the business key — one per form, and only alongside a key. When both exist, imports match on key + sub-key together. Also accepts "sub key", "sub_key", "sub-key". |
| sensitive / mask / pii | Flags the field (any type) as sensitive: masked in the grid, form, print and exports — and encrypted at rest when your operator has field encryption enabled. Name a master-data list in the Form column to flag one of its columns instead — it's masked in Master Data Maintenance, and any form field that references that column (a dropdown or auto-filled value) inherits the masking automatically. |
| conditional / validation | e.g. "required if status is 'Active'". Conditions can be compound — AND / OR / XOR — and use symbols (=, !=, >=, <=, >, <) or words; the compared value can be another field. Reads naturally with or without the leading "if", and tolerates parentheses — "(total > 100) then required" and "if total > 100 then required" are the same (e.g. a receipt required only for expenses over 100). |
| between / range | between <low> and <high>. Each bound is a literal, a field name, or "system date" / "today" (the save date). A line-item date can be bounded by HEADER fields ("between start_date and end_date from HEADER"). |
| auto / autogen | Auto-numbered: the app assigns the next number when a record is created — starting at 1 by default, or at the number in the Value column (e.g. 100 → 100, 101, 102…). If the field's Data type is a generated id (ulid / nanoid), it generates that id with its own mechanism instead of a number. Display-only; existing values are never reassigned. An auto field is implicitly the key, so a section can't also carry a separate key field. |
| derived | The field's value is computed, not typed by hand — from master data (a whole record with from Products, or a single column with Product.UOM / UOM from Product when the field name differs from the column, e.g. a Unit picker fed from uom) or a date calculation. |
| calc / calculation | The field is computed by the app and stored — a currency or unit conversion (see Currency & unit conversions below). Read-only, recalculated on every save; a value typed or imported into it is ignored. Also spelled calculation. |
| display | Read-only — the field is shown but never accepts typing. Use it for a value that comes from a default rather than the user: e.g. a submission_date that is required + display with system date in the Value column shows today's date, uneditable. (Unlike calc/auto, the app doesn't compute or assign it — it just presents whatever default it carries.) |
Rules_ row for each. For example, a sales_order that is both the business key and auto-numbered is two rows with the same Field.start_date/end_date, from_date/to_date…), the app enforces end ≥ start automatically — no rule needed.Default values. Any rule's Value column can carry a default: default value "Active" or default = X. Quoted or plain text is a literal; a qualified reference (default = HEADER.currency_code) or a calculation (default = amount / 100) is evaluated when the form or line row opens. The words system date / today / now fill the record's creation date — pair with display to show it read-only.
Money and quantity fields can convert automatically — an amount into another currency, a quantity into another unit — with the result stored and kept current. See Currency & unit conversions.
For calculated fields — totals, arithmetic, and IF / Boolean logic on a Formula_ sheet — see Formulas.