lookup field from 1 of many records in a linked field

[leisterbrau]

When the lookup value is a field with multiple records, is there a way to filter the returned linked records and extract one field from it?

For simplicity's sake, suppose that I have the following 4 tables

1. Products - generic information about products

2. Vendors - info about vendors

3. vendor_products - products that vendors carry (i.e. vendor pricing), linked fields to products and vendors

4. invoice line items - columns to select product, vendor, lookup field with vendor_products from vendor

On the invoice line item, I would like to be able to also filter the vendor_product by the selected product and then extract the price for it.  Is this possible?  

[TheTimeSavingCo]

Hmm, wish I could help but I can't think of a way to do that I'm afraid.  I take it there are business reasons why "Invoice Line Items" can't be linked to the "Vendor Products" directly?

[leisterbrau]

Thanks.    I suspected that would be the case.  The main reason I didn't do that is that some products have stable prices and availability from vendors and can be automatically pulled, others are bespoke and I'd like to manually choose the product, vendor and price combo.  Was hoping to hoping to avoid too many duplicate columns...vendor_product, vendor_product vendor, vendor_product_product, vendor_product_price, prodoct, vendor, price_manual

[TheTimeSavingCo]

Ahh yeah, fair enough.  Hmm...what if "Invoice Line Items" had:
1. Link to "Products"
2. Link to "Vendor Products" that's filtered based on the previous field
3. Lookup field from "Vendor Products" to grab the "Vendor"
4. Currency field for price

And you had an automation that would trigger whenever the link to Vendor Products was filled, and its action would beto grab the current price and put it into the currency field? 

That way for bespoke products you can modify the price, and for products with stable prices you'd have the amount stamped in which would be good for data accuracy?