2 Replies Latest reply on Sep 19, 2013 10:11 AM by LyndsayHowarth

    Conditional value list


      Hi All,


      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



        • 1. Re: Conditional value list

          You should take those two fields out of Ingredients and instead create a dedicated join table between Suppliers and Categories; this is also where you denote a supplier as preferred.


          Besides the normalization issue in your existing setup, you also have no way of associating a supplier (preferred or otherwise) with a category until you have added one of their ingredients from that category.


          Create a join table between suppliers and categories, with the two foreign keys, plus a number field like "preferred" (that you display on a layout formatted as checkbox with a value list of "1") to mark a supplier as preferred for that category.


          Create a field with a constant value in Ingredients (e.g. a globally stored calculation) with the result "1", then create a relationship between Ingredients and a TO of CategoriesSuppliers with the predicates categoryID = categoryID and constant = preferred. Add a TO of Suppliers (like “Suppliers__preferred") “behind” the CategoriesSuppliers TO and define this relationship by supplierID = supplierID.


          Define your conditional value list as using supplierID and supplierName from “Suppliers__preferred", and "starting from" Ingredients.

          • 2. Re: Conditional value list

            Where you list the supplier in your products table,,, it can see through to the related info about the supplier.

            So from the Order table you can have the conditional valuelist... based on a selection of the ingredient ID... once committed can show you the valuesof the suppliers (ID's?) on the LHS... and on the RHS the actual names of the suppliers showing via the Ingredients supplier join table via the relationship to the suppliers table... and at the bottom you would show only related values via the Ingredients supplier join table.


            It is late... I probalby said that badly. I will try and look again in the morning...

            '- Lyndsay