Comparing associated records in a field

[mirkanu]

Hi,

Not sure if title accurately matches my question. I have 2 tables linked by "Part Code":

Table 1: Parts. Columns: Part Code

Table 2: Offers. Columns: Supplier Name, Part Code, Price

In Table 1 I would like to add columns that automatically select the lowest Offer per Part Code. So Table 1 would have e.g. columns Part Code, Cheapest Supplier, Cheapest Price. Latter 2 automatically filled.

Is this possible, if so, how? Any help appreciated!

[mirkanu]

OK I'm able to create the Column "Cheapest Price" in Table 1 using a Rollup field for Price with formula MIN(values).

But "Cheapest Supplier" I'm stuck with the fact that a Lookup field doesn't allow dynamic conditions. I would need it to have a dynamic condition whereby "Price" [Table 2] = "Cheapest Price" [Table 1]. This doesn't seem possible?

[j-hugg-ins]

Hi there. As you've discovered you'll find that Airtable's formulas won't cut it when you need to do more complex logical operations. For something like this I would use Airtable's Scripting feature in Automations. You could set up an automation that would trigger whenever a new Offer is created or updated (depending on how you're currently adding offers to Airtable). You do need to be on a paid plan to use the Script feature but in my opinion it's worth it's weight in gold as it unlocks loads more flexibility and functionality, subject to your technical appetite of course 🙂

I ran a quick test and got it working. If you're on a paid plan and would like to add the scripted automation let me know and I'll happily provide the script and walk you through it.