Search formula in 2 differents tables

this is the link of the supplier table : Airtable - Grid view

Welcome to the community, Guillaume!

I’d first suggest rethinking your base design because I can’t think of a reason why you’d have a separate table for product prices. So, you need 3 tables at most, as well as linked fields linking suppliers to products (I’m assuming not every supplier will stock something).

From there, use a lookup + formula or a single rollup field to figure out the lowest price of any given product (assuming that’s your criteria for the order).

This is a common sentiment expressed by newcomers to any low- or no-code platform, especially one as powerful as Airtable. So, you’re definitely not alone in thinking this but us, humans, have a tendency to overestimate our anecdotal experiences when it comes to making generalizations. In fact, we are pretty hopeless at inductive reasoning. :slightly_smiling_face: Especially with already super abstract stuff like software.

My advice - fail, then report back.

Detailed questions explaining where you got stuck and how and why your base works the way it does tend to get resolved at a much higher rate because experienced users will immediately see fundamental mistakes with base design, formulae, and/or JS code. This, in turn, improves your odds of not just solving your current problem but getting a real eye-opener of a response. The kind resulting in an “aha, if I can do this, then I can also do that” kind of moment.

So, the nature of your questions should always be focused on the what (you’re trying to do), but the actual contents should be all about the how. Something along the lines of:

I wanted this...

So I did this...

Expecting this to happen...

But this happened instead...

Of course, this is all assuming that you want to get something done.

There’s nothing wrong with just wanting something to get done and not caring how that happens. There’s only so many hours in a day, after all, and learning about data structures, relational database design, and the like isn’t how most people would describe their ideal pastime. :grinning_face_with_sweat:

But those kinds of questions tend to fall in the work-for-hire category - there’s even a separate forum for those right here on community.airtable.com.

Thank you Dominik for your very long and detailed post. :pray:

You maybe right, and my problem is maybe more in my structure than in formula or function…
I try to rethink, but I still think I need all my tables : 1 for product, 1 for supplier, and 1 for price list… For the purchase order, I could use the price list, and add some column, but since there will be a lot of different purchase order, I think it make think clearer this way. (by the way, it’s not always the cheapest who has to be selected, it’s a human choice)

I tried look up, formula rollup, but I dont see a solution in this case.

I’m trying another structure…it’s more complicated and less handy, but may work…

it’s not always the cheapest who has to be selected, it’s a human choice)

Does that mean you’re using the single-select field for that choice?

This field was an option to do the purchase order. But it would work if we order just once. Because we want to make order every month, I create a new table, and I dont’ use this field.

Not sure if that’s what you meant exactly, but having one table for listings, another for suppliers, and a third one for orders should allow you to use a single base indefinitely instead of re-creating new tables when things get messy (that’s what Views/filters are for).

Either way, glad you worked it out!

:thumbs_up:

“Maybe”? No, is. :winking_face: This falls neatly into a Pareto curve; in almost all of the cases where developers hit the wall, the data model is typically the constraining factor. Perhaps you’ve temporarily overcome this challenge. If you believe this is “solved” well enough, for now, that’s good. But I don’t think it really is a sustainable approach and we’ll see you back here in a few months.

Bonjour Bill,

By the way, I solved the problem by changing my table design!
It works for the moment, even if I’m not sure of my design.
Let’s see in a couple of months :slightly_smiling_face: