Relations, roll-ups, formulas and field access in Notes databases
Link the rows of two databases, count or sum through the link, calculate a value from other fields, and decide who may see or change a field.
Four things make a database in Notes more than a table: a Relation links its rows to rows of another database, a Roll-up counts or adds up through that link, a Formula calculates a value from the row's other fields, and every field's Access decides who may see it and change it. All four are set when you add a field: click + at the end of the table's header, or the view's ⋯ → New field.
Link two databases with a Relation
- In New field, type the Field name and choose Relation as the Type.
- In Linked database, choose the database to link to (Which database?) — any database of the workspace.
- Click Add field.
In a cell of the new field, click to open the list of the linked database's rows and tick the ones that belong to this row. The cell shows how many rows are linked.
Count or add up with a Roll-up
A roll-up needs a Relation in the same database first — until there is one, Roll-up is not in the Type list.
- In New field, choose Roll-up as the Type.
- Through the relation — the Relation field to follow.
- Calculate — Count, Sum, Average, Minimum or Maximum.
- For anything but Count, Of the field — a number field of the linked database (Which field?).
- Click Add field.
For example, in a Clients database with a Relation to Orders, a roll-up with Sum of the order's Amount gives each client's total.
Calculate with a Formula
- In New field, choose Formula and type it in Formula — fields go in braces, for example
round({Price} * 1.2, 2). - Click Add field.
What a formula can use:
- numbers, text in double quotes,
true,false, and{Field name}for a field of the same row; + - * / %—+joins text when one side is text;== != < <= > >=, andand,or,not;if(condition, then, else),round(x)orround(x, digits),abs(x),min(…),max(…),len(text),lower(text),upper(text),concat(…).
A formula's value is calculated, never typed.
Decide who sees a field
Every new field has an Access level, judged by each person's access to the page that holds the database:
- Standard access — everyone who can open the page sees it; everyone who can edit changes it.
- Write: full access only — everyone sees it; only people with Full access to the page change it.
- Hidden from viewers — people with only Can view do not see it at all.
- Full access only — only people with Full access see it and change it.
A restricted field has a lock in the table's header. Fields that viewers may not see are not sent to the AI either when you use AI autofill: AI in Notes. Who has which access to a page: Sharing pages and links that expire.
If something doesn't work
"There is no other database in this workspace yet."
A Relation links to another database. Create the second database first — in another page or the same one — then add the Relation.
Roll-up is not in the Type list
The database has no Relation field yet. Add the Relation first.
A formula shows nothing
The formula has a mistake — a field name that does not match the field exactly, a missing brace or quote — or it cannot be calculated for that row. A broken formula shows an empty cell rather than an error. Check the field names, including capitals and spaces.