Calculating data from another table into this one


#1

I use AirTable to manage membership in my local organization. I have a table with the member data including their dues owed. In another table I upload the payroll reports every other week when they come. I can group those records by employee ID to see the total they have paid and compare it manually to the amount they owe. I would love to have AirTable do that part for me. I can’t figure out how to get the total amounts from the grouped records to automatically populate to the original table.

So what I want is the sum of the records attached to the user ID to be displayed in the other table in the record for the user. Does that make sense?

Thanks!

Jesse


#2

Assuming your membership table has a linked records field tying it to the payroll table, I would think you could do this through a roll-up. Create a new field in your membership table and define it as a roll-up field using the SUM() function on the weekly dues field. That should bring the total paid per member over to your membership table, where it could be used to calculate amount owed.

However, from your description it sounds as if your tables may be ‘soft linked’ – that is, each table has a field based on common data (employee ID), but aren’t explicitly linked. If so, you’ll need to hard link the tables through a linked records field. This is easily done; there’s a short video tutorial in the Airtable support entry on linked records fields that tells how.