How data tables work
How data tables work
Who can do this: anyone with Read on a table sees how it behaves. Edit on the table adds, changes and deletes rows and saves views. Changing columns, the table's purpose or its double-booking protection needs Owner on the table (the per-table access dialog calls that right Manage). Clear table data… needs Owner on the space. Rights are defined in the .
What it's for
Almost everything you run in a space is a data table: customers, orders, invoices, tasks, bookings, stock. This page explains the engine behind them in plain terms: what a table is, what happens when data is written, what the server calculates for you, and what to watch for before you change a table. It describes behaviour. It has no click-by-click setup.
Before you start
You do not need to set anything up. Every point below is how tables already behave.
Examples use the tables «Customers», «Orders» and «Invoices» (with «Invoice lines»). The column names in quotes are the defaults of the built-in Invoices template. You can rename them.
One setting needs your administrator. It is marked.
1. What a data table is
A data table holds rows (one per thing: one invoice, one customer) and columns (one per fact about it: number, customer, date, total). Each column has a type. The type decides how a cell is entered, checked, shown, filtered, sorted and exported. A date column holds a date, not free text.
A table is an item in the navigator, like a document. Create one with New data table, or start from a ready-made one with Use a template. See .
Most business objects are tables because the rest of the product is built around them. A form writes rows into a table. A dashboard reads rows. The portal shows a customer their rows. An automation reacts when a row changes. One invoice is one row in «Invoices». Its lines are rows in «Invoice lines», and each line has a link to its invoice. The link is what lets the invoice add up its lines.
Column types
The engine knows 42 column types. The Add column dialog offers 40 of them, in 13 groups. It does not offer Completeness (completeness, filled in by the engine) or Working calendar (calendarRef, allowed only on the staff directory). The Product (PIM) group appears only on a table that has a parent-link column.
Group in the dialog | Types (internal key in |
|---|---|
Basic | Text ( |
Date & time | Date ( |
Selection | Select ( |
Workflow | Status ( |
Contact | URL ( |
People | Person ( |
Media & files | Image ( |
Relational & computed | Reference ( |
Ledger | Balance ( |
Scheduling & location | Geofence ( |
Advanced | Sequence ( |
Actions | Button ( |
Product (PIM) | Attribute set ( |
Each type is explained, with its settings, in . Links between tables, lookups and rollups are in . The formula functions are listed in .
2. Entering and changing data
Rows get into a table in five ways:
The grid. Choose Add row and type into cells. You cannot type into computed columns.
The server checks every write when the row is created or changed, whichever way it arrives:
Require unique values: a write that repeats another live row's value is refused with "Duplicate value for …". Empty cells never collide. Deleted rows do not count. Bringing a deleted row back is checked again. You can switch it on for any column you can type into, and for Sequence, not for Formula, Rollup or Lookup. Example: the email column in «Customers».
Text rules on a Text column: Min length, Max length, Pattern (regex, full-match), and Validate as (barcode and ISBN checksums).
Number rules: Minimum, Maximum, and Fixed values (the cell becomes a dropdown).
File limits on a Files column: count, size, allowed types.
Status rules: locks, required fields and allowed moves (section 5), and Period lock… in the Data menu, which refuses changes and deletes for rows in a closed period.
Empty cells pass the text and number rules.
Careful: a table column has no "required" switch. To force a value, use a form that marks the field required (), or Required to enter this state on a status (section 5).
Default values. In a column's menu, Default value & auto-fill → Default value fills the cell when a new row is created and the cell is empty. Dates can default to today or now. A select default must be one of its options. Auto-fill is different: it keeps writing the cell while its condition holds. With Always, while the condition holds, it replaces a value someone typed by hand.
The check runs on writes from the grid, import and restore.
3. Derived data and recalculation
Formula, Lookup and Rollup columns are calculated by the server. So are Balance, Sequence, Rank, Time log and Completeness. The result is stored in the row. Filters, sorts, exports and dashboards read the stored value.
Example in «Invoices»:
«Line total» on an invoice line is a formula: quantity times unit price.
«Subtotal» on the invoice is a rollup: the sum of its lines' totals.
«Outstanding» is a formula: the total plus the signed payments and credits.
When values are recalculated:
When a source row changes. Edit a line's quantity. Its total is recalculated in the same save. Then the invoice's subtotal and outstanding follow. A status rule may then move the invoice. The server follows the chain from table to table until nothing changes. A rollup is refreshed only when the column it reads, a column in its filter, or the link itself changed.
When a row is deleted or restored. Rollups drop or take back the row. Lookups to it go empty or fill again.
When a name changes. Rename a customer and the customer cell on every invoice shows the new name.
When you change a column's definition. Editing a formula, a rollup or a lookup queues a background job that refills every row. The column header shows Recomputing… until it finishes. Until then the column is partly old and partly new, so do not read totals from it.
When time passes. A formula that uses TODAY() or NOW() is refreshed once a day, just after local midnight. Between runs it shows the value "as of last recalculation".
When it is a snapshot. With Disable automatic recalculation a column is never refreshed by itself. Use Refresh calculations on the row, Recalculate this cell, or the Recompute row automation action. Some formula functions require this mode.
When a row is locked. A locked row freezes its computed cells too, unless the column is listed under Fields that stay live while locked.
Needs your administrator: the hour of the daily refresh is an installation setting (
FORMULA_RECOMPUTE_HOUR, default 0). Your administrator can move it or turn it off. If «Aging» on invoices is stale in the morning, ask.
Formulas that match another table by value instead of through a link (LOOKUP_TABLE, SUM_TABLE, COUNT_TABLE, FIND_VALUE) refresh after a short delay and cost more on big tables. Use a Lookup or Rollup when the rows are linked. Large background refills take longer.
When a formula fails. The cell holds an error with a message instead of a value. Any formula that reads that column fails too, until you fix the source. IFERROR and ISERROR catch the error. Exports contain the message text. If the failing column is a helper (Use only in formulas (hide by default)), the toolbar shows "{n} row(s) with errors" so you can find it. The formula bar marks a formula that points at a deleted column as "broken reference".
4. Views
A view is a saved way of looking at one table. The Views button lists the saved views and the View type choices. The authoritative list:
View | What it is for |
|---|---|
Grid | The default: rows and columns, with filter, sort, pinned and hidden columns, column groups and an optional detail pane for linked child rows |
Kanban | Cards grouped by a Select, Status or Person column. Dragging a card changes the value. On a status column that runs the workflow. Example: orders by stage |
Calendar | Rows placed by a date or date-and-time column, with an optional end date |
Timeline | Bars from planned start and end dates, with lanes by status or by resource (person, external user, or a linked row such as a room) |
Pivot | A read-only crosstab of any two dimensions with a measure. Up to 3 row levels and 2 column levels. Offered on every table. Example: revenue by customer by month |
Matrix | For one master product, its variants in a grid of two axes |
Price pivot | Products against price-list columns. Offered only on a Price list table |
Roster | Resource rows against days, with scheduled shifts. Offered on a shift-schedule table, or any table with a resource column and planned start and end dates |
Booking calendar | Resource against time slots, optionally published as a public booking page. Offered only on booking-appointment tables |
Filters, sort, hidden and pinned columns, row height, column groups and colour rules are saved with the view. Colour rules show only in the grid, not in exports or the portal. Views belong to the table, not to a person. Your changes stay yours until you save: the view shows "You have unsaved changes to this view", and Discard my changes drops them. Save view stores a new named view. Update with current settings and Save for everyone change a saved view for everyone who uses the table. Set as default and Delete view do what they say. Seeing views needs Read. Saving, updating and deleting them needs Edit.
5. Statuses and workflows
A Status column puts each row in one state, for example an invoice in Draft, Sent, Partially paid, Paid or Void. Each state has a name, a colour and a meta-status: New, Draft, In Progress, Finished or Declined. Meta-statuses give different workflows the same five buckets for badges and board columns. Declined also counts as cancelled: on a table with double-booking protection it frees the booked slot, and Finished does not.
Open Configure states on the column. It is the same editor as Document States (). What you can set:
Enforce transition workflow: off (the default) means any state can move to any other. On means a row moves only along Allowed next states, must meet Required to enter this state, and must satisfy the Entry condition.
Lock rows in this state: a locked row is read-only and cannot be deleted. Only a move out is allowed. Owners can still force-delete with Delete anyway.
Conditional transition: on every recalculation of the row, including one caused by another table, the first rule whose condition is true moves it. Example: the invoice template moves «Sent» to «Paid» when «Outstanding» is 0 or less and the subtotal is above 0. The move is made by the system. The target state's required fields and entry condition still apply, and an unmet one means the rule does not fire. It works only with Enforce transition workflow on.
Timeout and conditional auto-transition: moves the row or notifies people after it stayed in a state for a set time. Example: "unpaid for 30 minutes".
Group permissions: Can transition TO this state, Can change state FROM this state, and the edit and comment rights per state, by group.
The deeper rules, approvals and timeouts are in . A state can also carry a ledger role (posted, pending, reserved or void) that decides how its movements count in a balance (section 7). The state editor has no field for the role: it comes from the built-in lifecycles.
6. Table purposes and behaviours
A purpose says what a table means: customers, invoices, price list. Modules, links between tables and some features use it to find "the customers table". The registry lists dozens of purposes. Most are plain labels with a minimum set of required columns. A few also switch on an engine behaviour.
To set one, open Table behaviour & roles, then Purpose, and choose Set or Change. The list has Engine behaviours and Roles. Pick None (ordinary table) to clear it. The picker shows Required columns as present, will be created or wrong type. Create & apply adds the missing ones. A purpose the table cannot satisfy is refused. It needs Owner on the table.
A behaviour is an engine capability. A table has at most one. The toolbar badge shows it. There are six:
Behaviour (badge) | What it gives you | When to turn it on |
|---|---|---|
Ledger (Append-only) | An append-only log of movements. Posted rows cannot be edited, only reversed. Feeds Balance columns | Stock movements, store credit, account postings |
Exclusive scheduling (No double-booking) | Refuses a row whose resource and time window overlap an existing one | Work orders, classes, reservations, trips |
Price list | Lets the | A tiered price table |
Exchange rates | Feeds | A currency-rate table |
Person directory | The staff table: exactly one per space, with working calendars and an org chart | |
Secondary directory (Directory) | A people list without working calendars | Vendors, contractors |
Except Exclusive scheduling, a behaviour follows from the purpose (Ledger from the ledger purpose, Price list from the price-list purpose, and so on). Exclusive scheduling is switched on by hand: Configure double-booking protection inside Table behaviour & roles, then Prevent double-booking, Booked resources and Time window. A table made from a built-in module may already have it. A table you built yourself with a booking-type purpose does not. It guards only new writes, so overlaps already in the table stay.
Recurring billing is no longer a behaviour. It is the Issue recurring invoice automation action (). For depth see behaviours vs. automations. That analysis is partly older than the code.
7. Auto-numbering, balances and stock costing
Auto-numbering. A Sequence column stamps a number such as INV-2026-0042 on every new row. Nobody can type it. Settings are under Auto-number settings: Prefix (it can include {YYYY}, {YY}, {MM}), Pad width, Start at, Reset counter and Key columns (a separate counter per key). The number is issued inside the save, so two people saving at once never get the same one. A deleted row's number is not handed out again. New settings apply to new rows only. Reset and renumber all rows…, Refresh out-of-date numbers… and Generate numbers for empty rows repair older ones.
Balances. A Balance column on a master table, such as products or customers, adds up the signed amounts from a ledger table. Set it up with Configure balance: Ledger table, Partition by (optional) (for example per warehouse), Allow negative balances, and Track reservations (pending → reserved bucket). Only movements whose state is marked "posted" count. Posting a movement locks it. To correct one, choose Reverse: a new entry with the negated amount is created, and the original stays. The balance changes in the same save as the movement. If a balance disagrees with its movements, for example after a bulk import, run Recompute balances. With Allow negative balances off, a write that would take a balance below zero is refused. Balance lookup shows another table's balance on a row, such as stock on an order line.
Stock costing. Enable FIFO-stock costing (under Ledger behaviour) treats each receipt as a cost layer. The Post ledger (FIFO) automation action then writes issues from the oldest layers and records the cost.
Careful: an issue entered by hand lowers the balance but draws on no cost layer, unless you fill Cost layer yourself. Stock on hand and the sum of layer remainders then differ. Both numbers are correct. They answer different questions.
8. Access inside a table
Rows and columns can be narrowed per person and per group: who sees which rows, who sees which columns, who may write which columns. Customers are scoped separately, by a Row filter and column visibility on the table. The rules, and the screens that set them, are in the .
9. Limits and things to know first
Deleting a row is reversible. The row goes to Recently deleted (the 200 newest). Restore brings it back with the same ID, and links to it work again. If it had child rows in a tree, they move up one level, and restoring does not move them back. Delete anyway is the owners' override for locked rows. A row referenced by a locked row, or in a closed period, cannot be deleted. See .
Changing a column's type. Change type changes the definition. It does not rewrite the values already stored. Type-specific rules are dropped (text rules; the unique flag where the new type cannot hold it). A change to Select or Multi-select goes through Convert to list or Convert to multi-list, which builds the options from existing values. Media types convert only within the same storage family. Otherwise a column that has values is refused with The column type cannot be changed. Pointing a Reference column at a table for the first time asks you under Existing values in this column, and clears links to rows that do not exist there. Changing the target of a column that already holds links is refused: clear the column first. Try a type change on a copy of the table first.
Careful: a column is protected by a guard when you delete it. The server looks for formulas, rollups, lookups, links, views, dashboards, forms, automations and connected apps that read it. Most of them show This column is still in use. You can choose Keep the column or Delete anyway. Connected apps ask separately (Connected apps will stop working). If an automation reads the column by name, you see An automation needs this column and cannot delete it. A broken formula or view is visible. A broken rule fails quietly, possibly at night, on a table nobody is watching. Edit the rule, then delete the column.
Remote tables. A table added with Connect remote table lets you rename and reorder columns. It does not let you add, remove or retype them.
How to check it worked
Open «Invoices», change a line's quantity, and check that the line total, the invoice subtotal and the outstanding amount follow. If one stays old, look for Disable automatic recalculation on it or Recomputing… on its header.
Where to go next
Platform concepts and process modules for the engineering view