Show only most recent row per group in grid view
Hi all.
I’m evaluating Airtable for use as a task and activity management solution. Part of the solution is weekly reporting on the status of ongoing projects or tasks, across a team of ~50 people.
We have a Projects table which contains records for each project or task:
Team members post incremental status updates against items in Projects using the Activities table:
For weekly reporting purposes, I would like a Grid view grouped by Project, which shows only the most recent Activity (sample below):
I have been trying to construct a Grid view of the Activities table, grouped by Project, with a formula field to identify the most recent record, against which I could filter. I have been unable to devise a method that works.
In order to display as desired, it will be necessary to identify the most recent Date within each Group of Activity records. There are some problems with this - MAX() returns only numeric values as far as I can tell, and MAX(values) is only useful in a rollup field.
The MAX(VALUES) rollup formula correctly identifies the most recent date, but I can’t work out how to create/manipulate a second table to leverage this technique. I have also considered a formula field which combines Project and Date to solve the group issue above, but again, don’t know how to leverage this as a filter which allows the display of only one record per group.
Airtable is an excellent resource so far, and if I can clear this last hurdle in our evaluation I believe we can use the product successfully. I’m grateful for any suggestions on this from more experienced users than I.
Thanks.
