Rollup

​

Relationship Between Rollup Fields and Link Fields

Rollup fields depend on previously created link fields. Through link fields, rollup fields can “see” related records in another table and perform various calculations on them.

​

Formatting Rollup Data

After performing rollups, you can not only get calculation results but also format these results and configure interactive displays to make them more readable and understandable. Formatting allows you to present rollup data in specific styles and formats, while interactive displays let you configure different UI and interaction options for calculation results. This not only makes data more readable but also helps ensure data consistency across different contexts and application scenarios.

​

How to Create a Rollup Field?

1. In a table with a link field, select “Add Field,” then choose “Rollup Field.”
2. Select an existing link field to determine the data source for the rollup.
3. Choose the field you want to roll up, then specify the aggregation operation to perform (complete list of operations below).

​

Rollup Field Use Cases and Examples

Let’s say you’re managing a bookstore database with two tables:

Table A: Orders

Table B: Books

The Orders table records customer purchase information, while the Books table contains pricing for each book.

If you want to sum up the total amount for each order, you can:

1. Create a link field in the “Orders” table linking to the “Books” table.
2. Then, create a rollup field in the “Orders” table, selecting the link field created in the previous step.
3. Specify “SUM” as the rollup operation and select the price field from the “Books” table as the target for rollup.

Let’s look at two tables: a “Book Details” table and an “Orders” table.

​

Book Details Table

Book Title	Price
”Code Complete”	$60
”Design Patterns”	$40
”Refactoring”	$50

In this table, we list the price of each book.

​

Orders Table

Order No.	Books (Link Field)	Total Amount (Rollup Field)
001	”Code Complete”, “Design Patterns”	$100
002	”Refactoring”, “Design Patterns”	$90

In the Orders table, when we select certain books, the rollup field automatically calculates the total price of the selected books:

• For order 001, we selected “Code Complete” and “Design Patterns”, priced at $60 and$40 respectively, so the total amount rollup field shows $100.
• For order 002, we selected “Refactoring” and “Design Patterns”, priced at $50 and$40 respectively, so the total amount rollup field shows $90.

Through this combination of link fields and rollup fields, we can easily automatically calculate the total amount for each order in the Orders table without manual input or calculation.

​

Supported Rollup Formulas

Formula Name	Explanation
COUNTALL	Counts all values, including empty and non-empty values
COUNTA	Counts non-empty values
COUNT	Counts the number of records
SUM	Calculates the sum of all numeric values
MAX	Returns the maximum value among all numbers
MIN	Returns the minimum value among all numbers
AND	Returns true if all values are true
OR	Returns true if any value is true
XOR	Returns true if an odd number of values are true
ARRAY_JOIN	Joins all values in an array into a string
ARRAY_UNIQUE	Removes duplicates from an array and returns a new array with unique values
ARRAY_COMPACT	Removes all empty values from an array and returns a new array
CONCATENATE	Joins multiple values into a string

​

Important Notes

• Performance Considerations: Large numbers of rollup operations may affect performance, especially when dealing with many data records. The system will provide the fastest possible calculation speed, but periodically optimizing and reviewing your rollup settings can better ensure application write performance.
• Rollup Formulas: Rollup fields will automatically provide allowed rollup formulas based on the data type of your selected rollup field, so you won’t always see all rollup formulas.

​

Conclusion

Rollup fields are one of the powerful data analysis tools, reducing the burden of manual summarization and data updates through automated calculations. They provide a quick and flexible way to view and analyze aggregate information from related data.