Lookups, Rollups and Counts

Once a relation links rows together, three field types read across that link:

• Lookup — show a field from the rows the relation points at.
• Rollup — summarise a field across those rows.
• Count — how many rows the relation points at.

All three are configured the same way, in one panel, and all three are read-only: the value is derived from the other table and recalculated when that table changes.

Pick the simplest tool that answers the question#

A rollup does not copy its answer into an editable cell. BankSync stores the computed result, invalidates it when an input changes, and replaces it after recalculation. While that work is outstanding, the previous value is labelled Recalculating instead of disappearing.

Configuring one#

The aggregations#

A rollup's aggregation list is filtered to what the target field can answer — you are not offered Sum over a text column. The full set:

The configurator opens on a sensible default for the field you picked: Sum for a number, Latest date for a date, Percent checked for a checkbox, Count per option for a select, and Show original otherwise.

An aggregation over no linked rows at all answers honestly: the numeric ones are blank, the counts are 0, "All are checked" is true and "Any is checked" is false.

Conditions#

Any of the three can carry a condition over the rows on the other side, so you can build "open invoices" as a count of linked invoices whose status is not Paid, or "spend this quarter" as a sum restricted by date. The condition is part of the field, applies everywhere the value is read, and is not the same thing as a relation's picker filter — which only narrows a search box.

Lookups and long lists#

A lookup shows values, not a summary, so it can produce a list. You can cap how many items it displays, up to 100. Lists render as chips in the grid; per-option results render as chips with their counts.

Open as formula#

Underneath, a lookup, rollup or count is a formula that BankSync writes from your choices. Open as formula shows you that expression:

sum(map({Invoices}, current.{Amount}))count(filter({Invoices}, current.{Status} != "Paid"))

You can read it to check the field does what you meant, and you can take it over and edit it. Editing it turns the field into an ordinary formula — the configurator's dropdowns no longer describe it, so they stop being offered. There is no way back other than building a new preset field.

Over the REST API the field still reports which preset produced it, so a consumer can tell a rollup from a hand-written formula.

Chaining, and where it stops#

A rollup may read a field that is itself a rollup — line item to invoice to customer to region is a legitimate chain, and BankSync allows it. What it does not allow is an unbounded one:

• a computed field may reach at most 5 hops away from its own row. Further than that is #DEPTH! on the cell, never a partial answer;
• a cycle — two fields that would need each other's answer — is rejected when you save the field, not discovered later;
• each relation cell it traverses is bounded at 100 links.

Reading the cell#

• A value that is waiting to be recalculated says Recalculating. It never shows a blank it has not earned.
• #REF! means the field or table it reads has been deleted or trashed. Restoring it clears the error.
• #TYPE! means the target field was retyped into something the aggregation cannot read.
• #DEPTH! means the chain is more than 5 hops long.

Because the results are stored on the row rather than computed on the fly, you can sort by a rollup, filter on one, chart and group one on a dashboard, and read one over the API exactly as you would a stored column.

Where to go next#

• Formulas — the full function list and error states.
• Building dashboard widgets on a table — chart a rollup.