Jun 14, 2024 04:34 AM - edited Jun 14, 2024 04:34 AM
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!
Jun 14, 2024 05:30 AM
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?
Jun 16, 2024 09:05 AM
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.