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?