Rules: validation & keys
Rules are how you keep bad data out — an amount that can't go negative, an end date that can't come before the start, an invoice number that must follow your format. You write them in your workbook, on a sheet named Rules_ (the singular Rule_ and Validation_ work too). One row per rule, using these columns: Field, Rule Type, Value, Message, Section, Form. The Rule Type column says what to enforce:
| Rule Type | What it does |
|---|---|
| required | The field can't be left blank. The Value column can also add a comparison — on end_date, for example: "greater or equal to start_date". |
| min / max | Sets the lowest or highest allowed value. The limit can be a number, a date (written 2026-07-01), or another field's name — "end_date | min | start_date" keeps the end date on or after the start. Only a numeric min or max also makes the field required. |
| min_length / max_length | Limits how short or long the text can be — say, a cost-center code of at least 3 characters. |
| pattern | The value must match a text pattern (a regular expression) — handy for fixed formats like invoice or VAT numbers. |
| key | Marks the field as the record's business key — a purchase order number, an employee ID. It becomes required, and imports use it to find the record to update instead of creating a duplicate. A form can have only one key. |
| subkey | A second part of the business key — one per form, and only together with a key. With both set, imports match on the pair (say, employee ID + week). Also written "sub key", "sub_key" or "sub-key". |
| sensitive / mask / pii | Protects private data — salaries, ID numbers, bank details. The field (any type) shows masked in the grid, the form, printouts and exports, and is stored encrypted when your operator has field encryption switched on. To protect a column of a lookup table instead, name that list in the Form column. It's then masked in Master Data Maintenance, and every form field that reads that column — a dropdown or an auto-filled value — is masked too. |
| public | public — for a published app (Settings → Publish to the web): the public page shows only the fields marked public on that form. With no public rules, the public page shows what a viewer sees. Marking a sensitive field public shows it masked — never the real value. No effect inside your workspace. |
| 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 | Keeps a value inside a range: between <low> and <high>. Each bound can be a fixed value, a field name, or "system date" / "today" (the day the record is saved). A line-item date can even be bounded by fields on the header ("between start_date and end_date from HEADER") — useful for keeping timesheet lines inside the pay period. |
| auto / autogen | The app numbers each new record for you — starting at 1, or at the number in the Value column (100 → 100, 101, 102…). If the field's Data type is a generated id (ulid / nanoid), the app generates that id with its own mechanism instead of a number. The field is read-only, and numbers already given out never change. An auto field also acts as the key, so its section can't carry a separate key field. |
| derived | The value is looked up or worked out, never typed. It can come from master data — a whole record with from Products, or a single column with Product.UOM (or UOM from Product when the field name differs from the column, e.g. a Unit picker fed from uom) — or from a date calculation. |
| calc / calculation | The app computes and stores the value — a currency or unit conversion (see Currency & unit conversions). Read-only and refreshed on every save, so it never drifts out of date; anything typed or imported into it is ignored. Also spelled calculation. |
| display | Shown, never typed. 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 or auto, the app doesn't compute or assign anything here — it presents whatever default the field carries. |
Rules_ row for each. 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 makes sure the end never comes before the start — no rule needed.Default values save typing. Any rule's Value column can pre-fill the field: default value "Active" or default = X. Text — quoted or plain — is used as written; a reference to another field (default = HEADER.currency_code) or a small calculation (default = amount / 100) is worked out when the form or line row opens. The words system date / today / now fill in the record's creation date — pair with display to show it read-only.
Defaulting a unit or a currency. Both are set on the small picker beside the value, not on the value column itself — so a rule for Quantity would pin the number, not its unit. Name the picker instead: Unit (also UoM, Units, or unit_of_measure) and Currency, with default value "KGM" or default value "USD" in the Value column. Set the Section so the right table gets it — line items and the header each carry their own picker. Simpler still when the code never varies: type it into the Unit or Currency column of your sample row and it becomes the default with no rule at all; a rule wins over that sample value when both are present.
Money and quantity fields can convert themselves — an order total into euros, a weight into kilograms — with the result stored and kept current. See Currency & unit conversions.
For calculated fields — totals, arithmetic, and IF / true-false (Boolean) logic on a Formula_ sheet — see Formulas.