Display the corresponding event for an item by picking the most recent event (e.g. max(created_at))


#1

I have a table [Table 1] which has a list of all the items of clothing stock that we have.

I also have a table which is an events log [Table 2] of all the events that happen to those items of clothing (e.g. purchased, packed, posted, returned, etc) and a date when that event happened (created_at)

I would like to show the latest event (with the latest created_at date) for each item of clothing on [Table 1]. Essentially this is finding the max(created_at) date filtered by clothing item for Table 2 and then finding the clothing item in Table 1 and putting the event that aligns to the max(created_at) date and clothing item.

In Google Sheets I would use a MAXIFS function to find the latest created_at date for a specific item, and then use a VLOOKUP to find the matching event.

Reading other posts, I suspect this requires making a calculation sheet, but I’ve tried for a while and can’t work it out. Anyone have any thoughts?


#2
  • Get the date of the latest event: Add a Rollup field to Table1, pointing to Table 2 and Created At field, and formula MAX(values). Call it ‘Date Last Event’.
  • We bring it to all events: Add a Lookup field to Table 2, pointing to ‘Date Last Event’ in Table1
  • We compare it with Created At: add Formula field to Table 2, with something like this IF({Date Last Event} = {Created At}, {Event Description}). Now, just the Latest Event has a field with the Description. Call it ‘Last Event Description’. Repeat this for all fields you want to show in Table 1.
  • Bring fields from Table2: add Lookup fields for all the fields you want, you will only get 1 value each.

Of course, change field names by your existing ones, and call the new fields as you want.

My Events table:

My Interactions table:


#4

That’s perfect - thanks so much for the help - exactly what I needed it to do!