Airtable is great, but the reporting facilities are limited. Say we have a table of class attendances in Airtable. Each class has a date, a subject and a list of linked attendees (shown here using letters instead of names):
| date | subject | attendees |
|---|---|---|
| 01/07/2021 | English | A,B |
| 01/07/2021 | Cookery | C,D |
| 01/07/2021 | French | A,E,F,G |
| 02/07/2021 | English | A,B,H |
etc
We want to find out:
- how many unique attendees there were for English classes in July 2021; and
- how many times each of these people attended English
There is no practical way to do this in Airtable that I am aware of. But we can easily do it in Easy Data Transform.
- First we need to create a filter in an Airtable grid view of the table:
- Then we need to download the filtered data as a CSV:
- Then we can transform the data into the form we need it, like this:
- input the CSV.
- use Split Col with Into set to Rows to move each person’s attendance onto a unique row.
- use New Col to add a column of 1s that we can sum.
- use Unique to keep unique names and sum the number of times they occur.
- use Sort to order by number of attendances.
- use Remove Cols get rid of cols we don’t need.
- output the results to a CSV file.
Example CSV input file:
class-attendance.csv (308 Bytes)
Example .transform file:
airtable.transform (3.5 KB)