Return the latest price for another table using rollups and lookups

[Craig_Toohey]

Hello,

I’ve seen a few forums on here that address something similar but none that exactly addresses my issue or gives a clear answer.

I have a series of price entries in a table called RFQ/PO Items.
I have a Lookup on a table called Supplier SKUs that looks up each of the prices on the RFQ/PO Items table.
I have a Lookup on the Supplier SKUs table that looks up each of the Dates on the RFQ/PO Items table.
I have a Roll-up on the Supplier SKUs table that looks at the MAX(values) to return the latest Date from RFQ/PO Items.

What I’d like to have is something that let’s me see just the latest Price. So, if there are three prices, $5, $8, and $6 on 1/1/2022, 2/1/2022, and 3/1/2022, respectively, I would like the rollup to return the value “$6”.

Thanks in advance for your help!

[ScottWorld]

That’s relatively tricky to do in Airtable. I discuss how to do it in this episode of the BuiltOnAir video podcast:

[Craig_Toohey]

Got it thanks @ScottWorld! In case anyone else is looking, I’ll summarize (I didn’t watch the whole segment, but I got the first clue and was able to figure out the rest).

1. Find out max date by using a rollup on the Supplier SKU table to look up the max date from the RFQ/PO Items table.
2. Go back to the RFQ/PO Items table and use a Lookup to find the max date from the Supplier SKU table.
3. Use a formula field on the RFQ/PO Items table to find out if the max date = the date for that record. If Max(date) = date, Price, 0)
4. Go back to Supplier SKU table and rollup from RFQ/PO Items using Max(values) - all the non-most recent date prices will be 0s, so you’ll just be grabbing the most recent price.

Just skimming the way you did it, I think you just had an extra field which clarified with a “yes” or “no” whether a record was the latest or not, which isn’t necessarily a bad idea!

Woohoo!