I have a database that has Ingredients and Suppliers, each ingredient can have more than one supplier and have a preferred supplier.
My problem is this: I have a fields "Preferred Supplier","Product Type" in the ingredients table and have "Supplier Name" and a related table with "Product Type" for suppliers
Eg: Supplier A sells Seafood and Meat, so the layout has the suppliers name with a portal with product type.
In the ingredients layout I would like a conditional value list for "Preferred Supplier" based on Seafood being picked from "Product Type" list with Seafood in their related table to show up.
I can make a conditional value list, but not sure how to link the Supplier with related table to value list in Ingredients layout.
any help would be appreciated