Rollup / Formula on 3 tables (groupby)

[tomerbar]

Hi everyone,

I’m encountering some challenges with defining a rollup field that combines data from three tables in Airtable. Here’s the setup:

1. Buildings (fields: name, etc.)
2. Employees (fields: name, hourly_rate, etc.)
3. Timesheet (fields: employee_name, building_name, day, hours, etc.)

I want to create a view that summarizes the total salary for each employee in each building. The view should include the following fields:

• employee_name
• building_name
• total_salary

The total_salary should be calculated as follows:

• Employee.hourly_rate * (sum of all hours in the Timesheet where building_name matches the building in the view).

Can anyone provide guidance on how to set up these rollup fields and calculations to achieve this view?

Thanks in advance for your help!

[TheTimeSavingCo]

You're going to need a fourth table where each record represents a single link between each employee and each building I'm afraid

To automatically populate it, I'd suggest:
1. Create a formula field that displays `[Building Name] - [Employee Name]`, e.g. "Building A - Jerry"
2. Create an automation that runs every time a Timesheet record has a linked Building and Employee,
3. Give it an "Update Record" action and make it update itself and take the value from the field from step 1 and paste it into the linked field to the "Employees <> Building" table

Link to base