Skip to main content

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?



  • 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:





  • 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:




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

Reply


Terms & Conditions