---
title: "Tables and Fields: Flexible Data Modeling for Operations | Lotics Docs"
description: "Learn how to structure your operational data with Lotics tables: typed fields, computed fields, record linking, and views. Build connected databases that power your workflows."
---

# Tables and Fields

Tables are the foundation of your Lotics workspace. Each table defines a schema with typed fields, stores records (rows of data), and supports multiple views: filtered and sorted perspectives on the same underlying data. Everyone works against the same records, so there are no private copies to reconcile.

## Table Structure

A Lotics workspace contains tables, and each table has three layers:

- **Fields** define the schema — what data can be stored and in what format.
- **Records** hold the actual data — one row per entity.
- **Views** provide different perspectives on that data — which records to show, in what order, with which fields visible.

This separation means you define your data model once and create as many views as your team needs. Fields are typed and validated. When you add a Number field, only numeric values are accepted. When you add a Select field, values must come from the defined option list.

Tables can hold tens of thousands of records and remain performant. The query engine supports server-side filtering, sorting, and pagination, so views load quickly even on large datasets.

## Field Types

Lotics supports the field types below, which cover the full range of operational data. Choosing the right field type matters for downstream features: a Number field enables rollup aggregations, and a Record Link field enables lookups and rollups across tables.

| # | Field Type | Internal Type | Variants / Formats | Value Format | Use Cases |
|---|---|---|---|---|---|
| 1 | Text | `text` | Plain text, URL/link, Markdown | `string` | Names, descriptions, notes, URLs, formatted content |
| 2 | Number | `number` | Number, Currency (ISO 4217 code), Percentage | `number` | Quantities, prices, margins, completion rates |
| 3 | Date | `date` | Date, Datetime, Date range, Datetime range | `string` (ISO 8601) | Deadlines, ship dates, scheduling, duration tracking |
| 4 | Boolean | `boolean` | True/false checkbox | `boolean` | Approval flags, completion status, active/inactive toggles |
| 5 | Select | `select` | Single select, Multi-select (`multi: true`) | `string[]` (option keys) | Status, priority, category, tags |
| 6 | Member | `select_member` | Single member, Multi-member (`multi: true`) | `string[]` (member IDs) | Owner, assignee, reviewer |
| 7 | Record Link | `select_record_link` | Link to another table, optionally bidirectional | `[{id, display}]` | Shipment-to-customer, invoice-to-order |
| 8 | Lookup | `lookup` | Read-only, pulls values from linked records | `unknown[]` (native array) | Show customer name on shipment |
| 9 | Rollup | `rollup` | Read-only, aggregates linked record values | `number \| string` | Total order value, average delivery time |
| 10 | Formula | `formula` | Read-only, computed from expressions | `number \| string \| boolean` | Profit margin, days until deadline, conditional labels |
| 11 | Files | `files` | Documents, images, PDFs (multiple per cell) | `[{id, filename, mime_type}]` | Contracts, photos, invoices, certificates |
| 12 | Autonumber | `autonumber` | Read-only, auto-incrementing with an optional prefix and zero-padding | `string` | Customer codes, ticket numbers, sequential references |

### Text

Stores string values. Three display formats:

- **Plain text** — default, unformatted text.
- **URL/link** — rendered as a clickable hyperlink.
- **Markdown** — supports formatted content (bold, lists, etc.).

Can enforce **unique values** across records, preventing duplicates.

### Number

Stores numeric values with configurable display format:

- **Number** — standard numeric display.
- **Currency** — displays with a currency symbol. Takes an ISO 4217 currency code (e.g., `USD`, `VND`, `EUR`).
- **Percentage** — displays as a percentage value.

### Date

Stores date and time values as ISO 8601 strings. Four formats:

- **Date** — date only, e.g., `2024-01-15`.
- **Datetime** — date and time, e.g., `2024-01-15T14:30`.
- **Date range** — start and end dates, e.g., `2024-01-15/2024-01-20`.
- **Datetime range** — start and end datetimes, e.g., `2024-01-15T09:00/2024-01-20T17:00`.

Supports **timezone configuration** using IANA timezone identifiers (e.g., `Asia/Ho_Chi_Minh`, `America/New_York`).

### Boolean

True/false checkbox. Simple on/off toggle for binary states like approval flags, completion status, or active/inactive toggles.

### Select

Dropdown selection from predefined options. Each option has:

- A **key** (`opt_xxx`) — stable identifier used in data storage.
- A **name** — the display label users see.
- A **color** — for visual distinction in the grid.

Two modes:

- **Single select** (default) — one option at a time.
- **Multi-select** (`multi: true`) — multiple options allowed.

Values are **always stored as arrays of option keys**, even for single select. A single-select value looks like `["opt_abc123"]`. No selection is an empty array `[]`.

### Member

Assign workspace members to a record. Works like Select but draws from the workspace member list instead of predefined options.

- **Single member** (default) — assign one person.
- **Multi-member** (`multi: true`) — assign multiple people.

Values are arrays of workspace member IDs.

### Record Link

Links to records in another table, forming a relational graph within your workspace. Key behaviors:

- **Bidirectional** — optional. Turn on two-way sync when you create the field and Table Y gets a paired field showing the reverse link; leave it off and the link is one-way.
- **Display fields** — configure which fields from the linked table are shown alongside the link.

Values are arrays of `{id, display}` objects, where `display` is auto-computed from the linked record's display fields.

### Lookup

Read-only computed field that pulls values from records linked via a Record Link field. If your Shipments table links to a Customers table, a lookup field on Shipments can display the customer's email, phone number, or any other field — without duplicating data.

- Returns **native arrays** with one element per linked record.
- Each element preserves the source field's type: text lookups return `["Alice", "Bob"]`, number lookups return `[100, 200]`.
- Updates automatically when the source data changes.

### Rollup

Read-only computed field that aggregates values across linked records. If an Orders table links to Order Lines, a rollup on Orders can compute the total order value (sum), number of items (filled), or the largest line item (max).

**Available operations by field type:**

| Source Field Type | Aggregate Operations |
|---|---|
| Text, Select, Member, Files, Autonumber | `empty`, `filled`, `percent_empty`, `percent_filled`, `unique`, `percent_unique` |
| Record Link | `empty`, `filled`, `percent_empty`, `percent_filled` |
| Number | the six presence operations above, plus `sum`, `avg`, `median`, `min`, `max`, `range` |
| Date | the six presence operations above, plus `earliest`, `latest`, `date_range` |
| Boolean | `checked`, `unchecked`, `percent_checked`, `percent_unchecked` — and nothing else |
| Formula, Rollup | whatever its computed output type allows: the number set, the date set, or the boolean set |
| Lookup, Button | none — these cannot be a rollup source |

Rollups can **filter linked records** before aggregation. The filter uses the same condition system as view filters, so you can aggregate only line items in a specific status or date range.

### Formula

Read-only computed field that evaluates expressions referencing other fields in the same record. Expression syntax uses field names in curly braces:

```
{Price} * {Quantity}
```

Supports arithmetic, string manipulation, date calculations, and conditional logic. Output types:

- **Number** — arithmetic results like `{Revenue} - {Cost}`.
- **Text** — string operations and concatenation.
- **Date** and **Datetime** — date calculations.
- **Boolean** — conditional expressions.

The output type is inferred automatically from the expression and determines which rollup operations are available when the formula is used as a rollup source.

Errors produce diagnostic strings in the cell, like `#ERROR: {Total} is not a field on this table`.

### Files

Attach documents, images, PDFs, and other files. Multiple files per cell. Each file attachment stores the filename, MIME type, and a storage reference. Files are served through an authenticated URL that stays valid, so you can save it in a document or a record and it keeps working.

### Autonumber

Read-only sequential identifier, assigned when the record is created and never edited by hand. Configure a **prefix** and a zero-padded **width** (`KH-` with width 3 gives `KH-001`), or a **template** with placeholder tokens — `HM-{YEAR}-{N:3}` gives `HM-2026-001`. Changing the prefix or width re-formats every existing value; changing a template does not, because its date tokens would lose the original creation date.

## Records

Records are individual data rows within a table. Each record's data is a key-value object where:

- **Keys** are field keys (e.g., `fld_abc123`) — stable identifiers that never change even if you rename the field.
- **Values** follow the rules of each field type.

Example record data:

| Field | Key | Example Value |
|---|---|---|
| Company Name (text) | `fld_name` | `"Acme Corp"` |
| Revenue (number, currency) | `fld_revenue` | `150000` |
| Is Active (boolean) | `fld_active` | `true` |
| Status (select) | `fld_status` | `["opt_active"]` |
| Assigned To (member) | `fld_assigned` | `["mbr_abc123"]` |
| Customer (record link) | `fld_customer` | `[{id: "rec_456", display: "Acme Corp"}]` |
| Customer Email (lookup) | `fld_email` | `["alice@acme.com"]` |
| Founded (date) | `fld_founded` | `"2024-01-15"` |
| Contract Period (date range) | `fld_period` | `"2024-01-15/2024-12-31"` |

Records also track `created_at` and `updated_at` timestamps, and can be **locked**. A lock covers the whole record, not individual fields: anyone who can see it can still open it, but an edit becomes a change request that the table's approvers review before it is applied.

## Computed Fields

Three field types — **Lookup**, **Rollup**, and **Formula** — are computed automatically. They are read-only: you never enter values manually. Instead, they derive their values from linked records, aggregations, or expressions.

**Dependency cascade:** Computed fields form a dependency graph. When a source record changes, every computed field that depends on it recalculates. This cascade is automatic:

1. A change to an order line item's amount...
2. Updates the lookup field showing that amount on the parent order...
3. Updates the rollup field summing all line amounts...
4. Updates the formula field calculating profit margin...
5. The stored result is what every later read returns — no client recomputes it.

Computed field values are persisted at write time (not calculated on read), so queries and filters on computed fields are fast.

## Record Linking

Record Link fields connect data across tables. A shipment record links to a customer record, which links to invoice records, which link to payment records.

Key behaviors:

- **Foundation for computed fields** — Lookup and Rollup fields traverse these links to pull and aggregate data. Without links, these computed fields have nothing to traverse.
- **Cross-table workflow access** — workflows triggered by a shipment status change can read the linked customer record for an email address, then read linked invoice records to attach a PDF.

## Views

Each table supports multiple views with independent configuration. All views share the same underlying data — changes in one view appear in all others.

### View Configuration

| Feature | Description |
|---|---|
| **Filters** | Tree structure with AND/OR groups and per-field conditions. Each field type has its own set of operators (equals, contains, greater than, is empty, etc.). |
| **Sort** | Multi-field sorting. Each sort rule specifies a field, direction (ascending/descending), and configurable blank position (blanks first or last). |
| **Field config** | Per-field visibility (show/hide), column width, and display order. Each view can show different fields in a different arrangement. |
| **Frozen columns** | Pin columns from the left side of the grid. Frozen columns stay visible while scrolling horizontally. |
| **Color rules** | Conditional row coloring based on field values. Defined at the table level and applied across all views. |
| **Summary row** | Aggregations displayed at the bottom of the view — count, sum, average, min, max, etc. Configured per field. |

### How Different Teams Use Views

The operations manager sees all shipments sorted by deadline with overdue items highlighted in red. The sales team sees only their assigned deals. The finance team sees invoices filtered by unpaid status.

## Shared data and freshness

Multiple team members work on the same tables. There is no save button — an edit persists as you make it, and no one holds a private copy to reconcile later. A computed-field cascade runs at write time, so a change to a source value is recalculated through the whole dependency graph before anyone reads it.

Your view refreshes when the app regains focus, when the connection is re-established, and after any action that changes data — so what you see reflects what you and your automations just wrote. An edit made by a colleague reaches an open view on its own, without a refresh. For operational teams this is the distinction that matters: the data is shared and single-sourced, so a screen left open in the corner keeps up with the records it is showing.

## Frequently Asked Questions

### How many fields can a table have?

There is no hard limit on the number of fields per table. In practice, tables work well with 100+ fields. If a table grows beyond that, consider splitting it into linked tables for better organization.

### Can I change a field's type after it already has data?

Yes. Lotics supports field type conversion for compatible types — for example, converting a Text field to a Select. Data is preserved and converted where possible; values the target type cannot hold are cleared. Autonumber is the exception: a field cannot be converted into or out of it.

### What happens to computed fields when I delete a linked record?

When a linked record is deleted, lookup fields that referenced it return empty values, and rollup fields recalculate excluding the deleted record. The link reference is removed automatically from both sides of the relationship. Formula fields that depend on lookups or rollups also recalculate.

### Can different views have different filter permissions?

Views control which records are visible through filters, but they do not enforce access permissions — a member can always create their own unfiltered view. Access is enforced elsewhere: table sharing decides who reaches the table at all, and a table's row-level visibility rules are applied to every query, so records outside a member's scope stay hidden through any view they build.

### How do computed fields handle circular dependencies?

A cycle is caught when the value is computed, not when you configure the field: the cell shows a diagnostic string, `#ERROR: Circular dependency detected for formula "…"`, naming the formula in the loop.

### What is the difference between Single Select and Multi-Select?

They are the same field type (`select`) with a configuration toggle. When `multi` is false (default), users pick one option. When `multi` is true, users can pick multiple options. In both cases, the stored value is always an array of option keys — single select just enforces that the array contains at most one key.

### How do Record Link paired fields work?

When you create a Record Link field in Table A pointing to Table B, turning on two-way sync gives Table B a paired field pointing back to Table A. Linking a record in one direction then automatically updates the other, and deleting the link from either side removes it from both. The paired field's key is stored in the `paired_field_key` property. Without two-way sync the link is one-way and Table B gets nothing.