An aggregate formula on table B based on a column on table A

[Aparajitha_Srir]

Hi team! I have a table A with a column called “Offer Amount”. I want to create a table B which will have a cell containing the max(tableA’s OfferAmount), that gets updated each time a new record is created in table A. Is this possible on airtable?

The background is that I want to send the current highest Offer Amount (out of all the records on table A) to a webpage on builder.io via zapier (hopefully) each time a new record is submitted to table A. The records are inserted into Airtable table A from a form on builder.io via zapier.

[Vivid-Squid]

Hi @Aparajitha_Sriram
You should be able to do this with Linking, Look Up, and Roll Up

Table A
Field: Offer Amount
Field: Link to Table B Product

Table B
Field: Product
Field: Look Up Offer Amount from Table A
Field Roll Up of Offer Amount in Table B with Formula MAX(values)

[Aparajitha_Srir]

Hey @Vivid-Squid, thanks a lot! However, I feel this gives me the max offer per product.
I’m looking for something like an equivalent of =MAX(Offers!A:A) in a cell in Products table. Just an aggregate with a cross table reference. Hopefully, this should get updated as new records get added in Offers table.
Is something like this possible? Sorry if I wasn’t clear earlier.

[Vivid-Squid]

Yes, same logic applies

Just add a few more look ups, roll ups and a formula

And here is the Product Table, with Highest Offer and Highest Offer Person

[Aparajitha_Srir]

Thanks a lot! I’ll try it out & see if it works for my case.