When a system you assembled yourself finally fails, it is usually not because a feature was missing. It is because some of the numbers depend on a person remembering to update them.
An order total changes, a batch of stock ships, a customer's lifetime spend needs recalculating — as long as those are typed in by hand, sooner or later one cell gets missed, and from then on the whole dataset is untrustworthy.
Links and formulas hand those "should be calculated automatically" fields back to the system.
The 30-second version
| Feature | What it does | Typical use |
|---|---|---|
| Link | Connects two tables | Orders ↔ Customers |
| Lookup | Pulls a field from the linked side in for display | Seeing the customer's phone number on an order |
| Rollup | Aggregates across the linked records | A customer's lifetime spend |
| Formula | Calculates from fields in the same row | Unit price × quantity = line total |
Of the four, links are the foundation — Lookup and Rollup both depend on them, and without a link in place those two will always be empty.
Links: decide which kind you need first
One-to-many
The most common. One customer has many orders, one project has many tasks, one item has many stock movement records.
Many-to-many
An article has several tags, and a tag belongs to several articles.
Ask one question before creating a link
"Will this field show up repeatedly across many rows?" If it will, it should be its own table, connected with a link.
Typing the customer name into every single order is the spreadsheet approach. When the customer changes their name, you have a hundred rows to edit, and you will definitely miss some.
Lookup: view the data, do not copy it
To see a customer's phone number on the orders table, you do not need another phone field to fill in by hand. Pull it across from the linked customer with a Lookup.
The point is that it is live — when the customer changes their phone number, every order shows the new one. That is exactly what copying by hand cannot do.
Rollup: many records into one number
A Rollup aggregates over all the linked records. The common ones are sum, count, average, maximum and minimum.
| What you want to know | How to set it up |
|---|---|
| How much this customer has spent in total | SUM over the order amounts |
| How many orders this customer has placed | COUNT over the orders |
| How much of this item is left right now | SUM over the movement quantities (shipments recorded as negatives) |
| When we last made contact | MAX over the contact dates |
The third row is the key design decision: stock on hand should not be a field; it should be the sum of the movement records. That way, whenever a number looks wrong, you can trace back to the entry that caused it. The full approach is in NocoDB for inventory management.
Formulas: calculations within one row
A formula handles arithmetic between fields in the same row, which is different from a Rollup's cross-table aggregation.
Common uses:
- Unit price × quantity = line total
- Line total × tax rate = amount including tax
- End date − start date = number of days
- Assign a tier by amount and return "Grade A" or "Grade B"
Formula fields cannot be edited by hand
That is a property, not a limitation — a field you cannot edit is a field nobody can break. If you want a number that is "usually calculated but occasionally overridden by hand", it should not be a formula; it should be a regular field with a checking mechanism around it.
Build order: getting this backwards hurts
- Create the tables first, with the right field types
- Then create the links
- Only then the Lookups and Rollups
- And finally the formulas
The classic symptom of getting the order wrong is a Rollup field that stays empty because the link was never made. It is where beginners get stuck most often, and the interface will not tell you why.
When not to use any of this
Honestly: if your data is just one list with no relationships across tables, you will not need links or Rollups at all. Forcing it into three tables only makes something simple complicated.
The test is straightforward: is the same thing being entered over and over across many rows? If not, one table is enough.
A complete example: an order system
Seeing the four features assembled together is clearer than taking them one by one:
Three tables
- Customers — name, contact details
- Orders — date, customer (link), status
- Order lines — order (link), item, unit price, quantity
Four automatically calculated fields
| Table | Field | Built with |
|---|---|---|
| Order lines | Line total | Formula: unit price × quantity |
| Orders | Order amount | Rollup: SUM over the line totals |
| Orders | Customer phone | Lookup: pulled from the customers table |
| Customers | Lifetime spend | Rollup: SUM over the order amounts |
Not one of these four fields needs a person to maintain it. Change the quantity on a single line and the line total, the order amount and the customer's lifetime spend all update up the chain.
Compared with the spreadsheet approach
The same requirement in a spreadsheet is usually one big sheet, with the customer name retyped on every row and amounts pulled across sheets with SUMIF. It works, but a customer name change means editing a hundred rows, and nobody knows which row got missed.
The difference is not in what it can do, it is in whether you can tell when it is wrong.
FAQ
Q: My Rollup field is empty?
Nine times out of ten the link has not been set up, or there is no data on the linked side. Start by confirming the link field really points at records.
Q: Can a formula reference a field in another table?
No. A formula's scope is the same row. To use data from another table, pull it across with a Lookup first and have the formula reference that Lookup field.
Q: Do Rollups get slow with a lot of data?
They become noticeable when the number of linked records is large. If a Rollup has to aggregate tens of thousands of records, consider calculating it on a schedule and storing it in a regular field instead of computing it live every time.
Q: Can I use conditionals in a formula?
Yes, and it is one of the most used features — tiering by amount or showing different text by status both rely on it. Check the official documentation for the syntax; it varies slightly between versions.
Performance: when to stop calculating live
Rollups and formulas are calculated live, which you never notice at small data volumes.
Where it starts to show: a single Rollup aggregating tens of thousands of linked records, or a table with many stacked layers (a formula referencing a Lookup that itself comes from a Rollup).
Two directions for dealing with it:
- Reduce the layers — store intermediate results in a regular field instead of chaining all the way down
- Switch to scheduled calculation — have an automation compute it once a day and write it into a regular field, rather than recomputing on every open
The second involves a trade-off: the numbers lag. Whether "yesterday's correct number" or "live but slow" is more usable depends on your actual situation.
Sources and further reading
- NocoDB official documentation — the formula function list and field type reference
- NocoDB source code and release notes
Further reading
- Introduction to NocoDB: the open-source Airtable alternative
- NocoDB tutorial: build your first table and view
- NocoDB views: how to use grid, kanban, calendar and form
- NocoDB for inventory management: what actually works for small teams
- NocoDB as a CRM: when it is enough and when to move on
- Migrating from Airtable to NocoDB: which field types will not line up
- RoamerHost managed NocoDB plans
Want someone to build it for you?
Once the data volume grows, or the system has to plug into what your company already runs, this stops being a question of picking a tool. Roamer Tech takes on enterprise system customization and API integration:
