For each group, show row with most recent date


#1

Hi there,

I’m new to Coda but enjoy what I see so far. I have a table with entries for various items. Each entry contains the item, a date and some other data, somewhat like a log. I’ve grouped the items in that table, so it now shows an item, then a bunch of dates and data, the next item, etc… Now, I’d like to build a view that, for each item, only shows the most recent entry in that table. Unfortunately, I cannot figure out how to do the filtering for each group. Any tips?

Best,
Koen


#2

Hi Koen,

There are few ways you could get desired result - I will post them here as i get time to work on them.

In this approach I have used separate table, the data in there is updated based on automation (Row Changed) event. Example is here - Raw logs are sample data and as the data changes there, dashboard table is updated to show latest and earlier entry in that item group.

Hope this helps.

Thanks.


#3

Here’s a second approach where you are not creating a separate table but filtering the existing one to show you only the latest entry for each item group

The formula for filtering would be: [Raw Logs].Filter(Item=thisRow.Item).Sort(false, [Event Date]).First() =thisRow


#4

Here’s third option where you can put results directly in canvas.


#5

Thanks a lot! I’ll try to translate these solutions to my problem and will get back to you if I fail to do so. Solutions 2 and 3 seem to be the most relevant ones for my case. I guess #1 is more efficient if you’ve got a lot of rows - which I don’t.

Thanks again!

Koen


#6

I implemented the second solution, and it works great - thanks! I did play around with Filter and Sort, but I guess I didn’t realize that I had to match it again to thisRow.