Reporting on / rolling up records which have linked records which in turn have further linked records

[Ben_Crouch]

I have the following hierarchy within my agency:

Customers: Who take multiple services each month
Services: Which are made up of multiple tasks
Tasks: Which have multiple people work on them, all taking a different amount of time
People: Who have a different hourly cost

I have built the following tables in Airtable

• Customers
• Services
• Tasks
• People

I have a ‘Services Delivered’ Table and each month we add to it the customer and the service they have taken (a new record/row for each).

What I’m struggling with is how I can create a report (or a table first that needs to facilitate this) that shows me the amount of time spent on services that month by person. I’m guessing this would need to roll up the volume of services added for every customer across the month and also roll up the tasks which make up those services and finally rolls up the time by person spent on each.

I just can’t figure out how to report on that or if and how I build a table to report on tha

[Kamille_Parks]

You would likely need one more table for Reports.

1. In your [Services Rendered] table, create a field that gets the year and month of the service performed. That could be a Formula field using DATETIME_FORMAT({Date of Service}, "YYYY-MM")
2. Use an Automation to automatically copy the value of your Formula field into a Link to Record field pointing at your [Reports] table. Suggested trigger: “when record updated”, watching only the Formula field.

Now you have a single record for each month that will be linked to all the services performed that month. For existing [Services Performed] records you’ll have to copy the Formula to the Link fields yourself as the Automation will only handle new records.

3. Create a Count field for each [Person[ record you want to track. Use the “Only include linked records that meet certain conditions” toggle to only count records for the appropriate person.

Depending on how many people you have, you may have quite a few count fields. If that becomes unwieldly, or if you have an ever-growing list of people, you may need to modify the above instructions slightly:

• Adjust the formula field in [Services Rendered] to concatenate the year-month and the Person name. Adjust the Automation to also watch the Person field for updates. Now you will have one report record for each person-month pair.
• Add a formula field in the Reports table to extract the year-month out of the record name. That way you can group all records by month but still see summary data for each Person.

This base on the Airtable Universe uses an approach similar to the one outlined above to get reports at month, year, etc. intervals. You can take a look at the formulas there, and look at the included Script App that will help you build formulas if need be:

Airtable

Simple Time-interval Reporting - Airtable Universe

Ever need to get the count of how many records are from a given year? Or quarter? Or month? Take a look at this sample base that uses a few formulas and a...