Relations, Lookup, Rollup & Formula

Pachurros supports 6 advanced column types for linking tables and computing values: Relation, Backlink, Lookup, Rollup, Count, and Formula. Together they enable a complete relational database model, without knowing SQL.

---

Relation

A relation links rows in this table to rows in another table. It corresponds to a classic 1:n or n:m relationship.

What you use relations for

• Shooting → Clients (each shooting is assigned to a client)
• Tasks → Projects (each task belongs to a project)
• Invoice line items → Invoice (multiple items belong to one invoice)
• Equipment → Shootings (which gear is used where)

Setting up

1. Add new column → choose type Relation
2. Select the target table (must be in the same workspace)
3. Save

Linking rows

Click on a Relation cell → dropdown opens → type to filter → click row(s) to select. Multiple links per cell are supported.

Complete example

Scenario: You have a “Shootings” table and a “Clients” table. Each shooting should know which client commissioned it.

1. In “Shootings” → new column → type: Relation → target table: Clients
2. In the cell for shooting “Autumn Collection” → dropdown → select “Anna Bauer”
3. The cell now shows: Anna Bauer (clickable)

---

Backlink

A Backlink is the reverse side of a Relation: it shows in table B all entries from table A that point to a given row. The Backlink is read-only, since links are always set on the Relation side.

What you use backlinks for

• See which shootings belong to a client in the Clients table
• See all related tasks in the Projects table
• List all line items for an invoice

Setting up

1. New column → type Backlink
2. Choose the source table (the table that contains the Relation column)
3. Choose the Relation column in that source table
4. Save

Complete example

Scenario: You want to see, in your Clients table, which shootings each client has.

Prerequisite: A Relation column “Client” already exists in the Shootings table.

1. In “Clients” → new column → type: Backlink
2. Source table: Shootings
3. Relation column: Client
4. The “Anna Bauer” row now automatically shows: Autumn Collection, Spring Catalogue

Tip

The Backlink is always in sync: as soon as you add a link in the Shootings table, it automatically appears in the Backlink column of the Clients table.

---

Lookup

A Lookup shows the value of a specific column from the linked rows, read-only. It passes through a piece of information from the linked table.

What you use lookups for

• See the linked person’s email directly in the Shootings table
• Display the client’s address in the Invoices table
• See a project’s status in the Tasks table

Setting up (Relation mode, default)

1. New column → type Lookup
2. Mode: From Relation (default)
3. Choose the Relation or Backlink column (must already exist)
4. Choose the column to display from the target table
5. Save

Setting up (VLOOKUP mode, classic)

If you don’t have a Relation column but share a common key (like a customer number):

1. New column → type Lookup → mode: VLOOKUP (classic)
2. Choose the source table
3. Choose the key column in this table (e.g. customer number)
4. Choose the key column in the source table (for matching)
5. Choose the column to display

Complete example

Scenario: In the Shootings table, the client’s email address should appear automatically.

Prerequisite: A Relation column “Client” exists in the Shootings table.

1. New column → type: Lookup → mode: From Relation
2. Relation column: Client
3. Column to display: Email
4. Result: The linked client’s email appears automatically in every row.

---

Rollup

A Rollup aggregates values from multiple linked rows, for example the sum of all invoice line items, number of open tasks, or average rating.

What you use rollups for

• Total amount of all invoice line items in the Invoice table
• Average rating of all linked entries
• Percentage of completed tasks per project
• Earliest start date of all linked appointments

Setting up

1. New column → type Rollup
2. Choose the Relation or Backlink column
3. Choose the aggregation (Count doesn’t need a property!)
4. If the aggregation requires a column (Sum, Average, etc.): choose the property from the target table
5. Save

Available aggregations

Aggregation	Description	Property needed?
Count	Number of linked rows	No
Empty	Number of empty values	No
Not empty	Number of non-empty values	No
% empty	Percentage of empty values	No
% not empty	Percentage of non-empty values	No
% checked	Percentage of checked checkboxes	No
Show values	Displays values from all linked rows	Yes
Unique	Number of distinct values	Yes
Sum	Total of all numbers	Yes
Average	Mean value	Yes
Minimum	Smallest value	Yes
Maximum	Largest value	Yes

Complete example

Scenario: In the Clients table, show how many shootings each client has, and what the total revenue is.

Prerequisite: Backlink column “Shootings” in the Clients table.

Column 1 · Number of shootings: New Rollup → Backlink column: Shootings → Aggregation: Count → no property field needed

Column 2 · Total revenue: New Rollup → Backlink column: Shootings → Aggregation: Sum → Property: Price (number column in Shootings)

---

Count

The Count column is a simplified Rollup: it only counts the number of linked rows, without needing a property. Ideal for quick counting.

Setting up

1. New column → type Count
2. Choose the Relation or Backlink column
3. Save, done

When to use Count vs. Rollup?

• Only need the number of linked entries → Count (simpler, one click)
• Need Sum / Average / other aggregation → Rollup

---

Formula

A Formula field computes a value based on other columns in the same row.

Syntax

Column names are referenced in curly braces:

{Price} * {Quantity}
{Last Name} & ", " & {First Name}
IF({Status} = "Done", "✓", "–")
ROUND({Price} * 1.081, 2)

Supported operators

Category	Operators
Arithmetic	+, -, *, /
Text	& (concatenation)
Comparison	=, !=, >, <, >=, <=
Logical	AND(...), OR(...), NOT(...)

Supported functions

Function	Description
IF(cond, true, false)	Conditional evaluation
ROUND(number, digits)	Round
ABS(number)	Absolute value
LEN(text)	Text length
UPPER(text)	Uppercase
LOWER(text)	Lowercase
TRIM(text)	Remove whitespace
CONCAT(a, b, …)	Concatenate strings
TEXT(number, "0.00")	Format number as text

Entering a formula

Click on the column name → Configure column → formula field. A live preview shows the computed value in real time.

Complete example

Scenario: Invoice table with columns “Price” (number) and “Quantity” (number): automatically calculate the total including VAT.

Formula: ROUND({Price} * {Quantity} * 1.081, 2)

Result: Total including 8.1% VAT, rounded to 2 decimal places.

---

Practical example: Complete database model

This example shows how all types work together.

Tables:

• Clients: Name, Email, Phone
• Shootings: Title, Date, Price, Status

Columns:

Column	Table	Type	Configuration
Client	Shootings	Relation	Target table: Clients
Shootings	Clients	Backlink	Source table: Shootings, Relation: Client
Client Email	Shootings	Lookup	Relation: Client, Property: Email
Shooting Count	Clients	Count	Column: Shootings (Backlink)
Total Revenue	Clients	Rollup	Column: Shootings, Aggregation: Sum, Property: Price

Result: In the Clients table, you see for each client: all linked shootings, the total count, and total revenue, without manually maintaining any data.

---

Common issues

“Configure” text in a cell: The column is not fully set up yet. Clicking the text opens the configuration panel directly.

Backlink shows no values: Check that the Relation column in the source table is correctly configured and that links have been set.

Rollup shows empty cells: For Sum / Average / Min / Max, a numeric property from the target table must be selected. For “Count”, no property is needed.

Lookup shows ”-”: The row has no linked entry in the Relation column, or the linked row has no value in the Lookup property.