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.

2 Likes
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

The formula breakdown is:

  1. [Raw Logs].Filter(Item=thisRow.Item) <- gives us all records from same table where item matches
  2. [Raw Logs].Filter(Item=thisRow.Item).Sort(false, [Event Date]) <- sorts records found in first step in descending order
  3. [Raw Logs].Filter(Item=thisRow.Item).Sort(false, [Event Date]).First() <- picks the latest record
  4. [Raw Logs].Filter(Item=thisRow.Item).Sort(false, [Event Date]).First() =thisRow - show thisRecord if its the very first record in sorted set . hide everything else
2 Likes
4

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

1 Like
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

1 Like
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.

1 Like