Rollups & Connected Calculations

Transcript Summary

Introduction

What's up, Databasers? Sam here. In this video, I'm going to teach you all about Roll-Ups. I'll explain what Roll-Ups are, the different types of Roll-Ups, and how you can use them in your app. First, I'll show you what a finished app looks like using Roll-Up fields.

Finished App Example

Here, I have a table of my customers with fields like name and street address. The roll-up fields I’ve created include:

• Open Jobs: Shows the number of open jobs for each customer.
• Minimum Job Value: Displays the lowest job cost.
• Maximum Job Value: Shows the highest job cost.
• Average Job Value: Provides the average job cost.
• Total Electrical Jobs: Displays total electrical jobs for each customer.
• Job Categories Used: A concatenated list of job categories (e.g., electrical and plumbing).

Setting Up Roll-Up Fields

Let me walk you through how to build these fields.

Step 1: Creating the Data Structure

In this example, I have a jobs table and a customers table, where each customer has ordered jobs from us. The jobs table includes fields like job price, category (electrical or plumbing), and status (open or complete). We’ll add Roll-Ups to the customers table.

Step 2: Adding a Roll-Up Field

To create a Roll-Up field, go to the fields option and select equation. Then, choose the Roll-Up type you want to create. Roll-Ups only work if there’s an existing connection between the tables. In this case, I’ve already created a connection between the jobs and customers tables.

Example: Count Roll-Up

1. Select the jobs table as the source.
2. Choose Count to count the number of jobs for each customer.
3. You can add data conditions to specify which jobs to count. For example, count only jobs with a status of "open."
4. After saving, the Roll-Up will display the number of jobs for each customer.

Step 3: Other Roll-Up Options

There are several types of Roll-Up functions. Here’s how to use a few more:

Minimum Value

The Minimum Value Roll-Up allows you to display the lowest value from a specified field, like job price. This helps you show the lowest job cost for each customer.

Maximum Value

The Maximum Value Roll-Up works similarly to the minimum function but shows the highest job price. This can be useful for prioritizing customers based on their highest-value jobs.

Average Value

Use the Average Value Roll-Up to calculate the average job price for each customer. This is helpful for understanding which customers bring in higher-value jobs.

Sum Value

The Sum Value Roll-Up adds up the total value of all connected records. For example, you can calculate the total amount of all jobs for each customer, giving you an idea of their overall contribution.

Step 4: Concatenate Roll-Up

The Concatenate Roll-Up combines multiple values into one field. For example, you can concatenate job categories like electrical and plumbing to see which services a customer has used.

Concatenate Unique

If you only want to display each category once (even if they have multiple jobs in the same category), use Concatenate Unique. This will prevent duplicates and only show each category one time per customer.

Conclusion

Those are the seven different types of Roll-Ups you can use in your app:

1. Count
2. Minimum Value
3. Maximum Value
4. Average Value
5. Sum Value
6. Concatenate
7. Concatenate Unique

I hope this tutorial was useful in understanding how to use Roll-Ups in your app. Thanks for watching, and have a great day!