Advanced Features (Formulas, Relations, Rollups)

Overview

Databases support powerful advanced features that let you build dynamic, relational, and calculated data structures. These features are optional but can dramatically increase the usefulness of your tables.

1. Formulas

Formulas let you compute values based on other property values in the same row.

Creating a Formula Property

1. Click "Add property" in the table header
2. Choose "Formula" as the type
3. Give it a name (e.g., Total Price)
4. In the formula editor, type an expression using property names.

Formula Syntax

Element	Example	Description
Property reference	price	Use the exact property name (case‑insensitive)
Arithmetic	price * quantity	Basic math (+ - * /)
Functions	round(price * quantity, 2)	Built‑in functions (see list below)
Conditional	if(status == "Done", "✅", "❌")	Ternary if(condition, trueVal, falseVal)
Text concat	"Task " + task_number	Use + to concatenate strings

Common Functions

• round(number, digits) – Round to digits decimal places
• dateAdd(date, amount, unit) – Add time to a date (unit: days, weeks, months)
• formatDate(date, "YYYY‑MM‑DD") – Format a date string
• length(text) – Length of a string
• contains(text, "sub") – Returns true if sub is in text

Best Practices

• Keep formulas simple for performance
• Use descriptive names for properties referenced in formulas
• Avoid circular dependencies (a formula referencing itself indirectly)

2. Relations

Relations link rows in one database to rows in another (or the same) database, creating a relational data model.

Creating a Relation Property

1. Click "Add property"
2. Choose "Relation"
3. Select the target database you want to link to
4. Optionally enable "Allow multiple" to link many rows

Using Relations

• Click the cell to open a selector of rows from the target database
• Search or scroll to find the row you want to link
• Click to add the link
• Linked rows appear as clickable chips; clicking opens the related row

Bidirectional Relations

When you create a relation, Ascend automatically creates a back‑link property in the target database (you can rename it). This lets you see the relationship from both sides.

Use Cases

• Projects ↔ Tasks – Link tasks to their parent project
• Clients ↔ Invoices – Connect invoices to the client record
• Employees ↔ Departments – Assign employees to a department

3. Rollups

Rollups aggregate data from related rows using a relation property.

Creating a Rollup Property

1. Add a new property
2. Choose "Rollup"
3. Select the relation you want to roll up from
4. Choose the property in the related rows to aggregate
5. Pick an aggregation function (Sum, Average, Count, Min, Max, etc.)

Example: Project Total Budget

• Relation: Tasks (each task has a budget number)
• Rollup: Property = budget, Function = Sum
• Result: Shows the total budget of all tasks linked to the project

Aggregation Functions

Function	What it does
Count	Number of linked rows
Count values	Number of non‑empty values
Sum	Adds numeric values
Average	Mean of numeric values
Min / Max	Smallest / largest value
Earliest date / Latest date	Date extremes
Show original	Shows the first related value (useful for single‑select relations)

4. Combining Advanced Features

You can nest these features for powerful workflows:

• Formula referencing a Rollup – e.g., budget_remaining = total_budget - rollup_sum_spent
• Relation + Formula – calculate a value based on linked rows
• Rollup of a Formula – aggregate a calculated field across related rows

5. Performance Tips

• Limit the number of relations per table (very large relation graphs can slow UI)
• Avoid heavy formulas on large tables (use rollups where possible)
• Cache frequently used calculations in a separate property if performance degrades

6. Permissions

• Read‑only users can view formulas, relations, and rollups but cannot edit them
• Editors can create and modify these properties
• Admins can change which properties are visible in views (hide complex formulas if needed)

7. Troubleshooting

Formula shows "Error"

• Check for typos in property names
• Ensure you’re not creating a circular reference
• Verify the data type matches the operation (e.g., cannot add a text to a number)

Relation not linking

• Make sure the target database is accessible (you have permission)
• If the target row is archived or hidden by a filter, it won’t appear in the selector

Rollup shows "0" or empty

• No related rows exist – add links first
• The property you’re rolling up may be empty on the related rows

8. Related Guides

• Property Types – Overview of all property types
• Working with Rows – Adding, editing, and selecting rows
• Views & Filters – How to display data
• Collaboration – Comments and sharing for databases

---

Last Updated: December 2024