Comparing two input values in a table and giving the matching result - product pricing

[Kristian_Lauron]

Hi community!
I’ve been now struggling couple days with finding a solution to a problem that to my mind seems pretty easy to solve.

So basically I have a billing list table to where I want to bring more info to from another table. In a separate table I have the information of our subcontractors and their prices. Also our clients have different prices so we need to bill them based on their contract price and then also check which subcontractor are we using so that we can get the values “Client’s price” and “Subcontractor price”.

The ideal situation:
I search first for the product (the names of the products are of course the same to all clients) and then when I select the client and the subcontractor it would automatically fill the info of the client’s price in one cell and the subcontractor’s price in another.

I’ve searched for other similar answers in the Q&A but can’t seem to find exactly similar problems from others. I also thought about doing this with the automation tool, but I haven’t been able to find a solution to get the input after finding the data based on my conditions. Also basic lookup doesn’t help me or the find-formula / filter. I can’t solve this by just filtering everything as the billing table and billing rows to each product should be fully visible.

Hope I was clear enough with my explanation. Thanks in advance!

[TheTimeSavingCo]

Hmm, what issues did you face while using basic lookup?

From reading your post, it feels like your problem would be solved with linked fields to the Client and Subcontractor table, with lookup fields to the prices, but you said it didn’t help so I figure I’m missing something

[Mohamed_Swella1]

Hi @Kristian_Lauronen

Welcome to Airtable Community !

The best setup for you would be the following tables :

1. Clients : Has all the Clients info

2. Subcontractors : Has all the Subcontractors info

3. Products : Has the Product info (without the price)

4. Subcontractors and Products : In this table, (what we call a Junction table or a Line Items table) you will link the Subcontractor and the Product tables to this table, and create a record for each Product from each Subcontractor

5. Projects (or something similar) where you the have the Client linked and the Subcontractors and Products table also linked, you can then add the Client price in this table and use a lookup to get the Subcontractor price from table 4.

Hope this helps :slightly_smiling_face:

[Kristian_Lauron]

So yeah I maybe wasn’t specific enough with my question. The way that you mentioned to solve the issue is correct but we already have made it like that. :slightly_smiling_face: The next step would be to get automatically sorted list of products based on the chosen client and subcontractor and then after choosing the right product, it would get the right purchase price and the price for the client.

So it should follow this workflow:
select a client > select a subcontractor > get a list of products based on the previous selection > choose a product > automatic fill for the purchase price and the client’s price. Hope his clarifies it! :grinning_face_with_smiling_eyes:

[Mohamed_Swella1]

And how will we know what is the list of products needed? Maybe screenshots would help :slightly_smiling_face:

I think what you are looking for is an automation