2 Replies Latest reply on Feb 20, 2013 11:56 PM by MarkusWick

    showing all items compatible to a product with portal tool....

    MarkusWick

      Title

      showing all items compatible to a product with portal tool....

      Post

           Hi there!

           I am stuck with a problem that I seem not to be able to solve alone.... Lets explain with an example:

           I have a database with a table "laptops" and a table "spareparts".

           "laptops" holds 47 different laptops, each is identified by a unique number from 01 to 47

           "spareparts" holds hundreds of spareparts. Each of that has a unique number, too.

           Some spareparts are compatible with only one laptop model. Some are compatible with three, some with 10, some with all.

           I created a field, which holds the modelnumbers with which the sparepart is compatible to. Its called "compatible with model".

           So.

           Sparepart "harddrive connector" is compatible with model 08 and 09.

           Sparepart "Mainboard" is compatible with model 08

           Sparpart "dvd drive" is compatible with model 05, 06, 07, 10, 15, 16.

           When I open the layout which shows the models, when I go to model 08 for example, i want to show a list of spareparts compatible with model 08 (harddrive connector and mainboard).

           I tried that with the portal tool but simply cant figure out, which relationship i need to use. It would be easy if one possiblility of relationship would be conmtent of field "modelnumber" of table "laptops" is contained in field "compatible to model" of table "spareparts".

           Any hints how I can solve that?

        • 1. Re: showing all items compatible to a product with portal tool....
          philmodjunk

               What you are describing is called a "many to many" relationship. A given laptop record needs to link to many parts and a given part record needs to link to many laptop records.

               The typical approach for setting this up is to add a third table, a "join" table that manages the many to many links:

               Laptops-----<Laptop_Part>------Parts

               LapTops::__pkLaptopID = Laptop_Part::_fkLaptopID
               Parts::__pkPartID = Laptop_Part::_fkPartID

               A portal to Laptop_Part located on a Laptops Layout can list all parts for a given laptop and fields from Parts can be included in the portal row. Put the same portal on a parts layout and you get a list of all laptops that require that part and fields from laptops can then be included in this portal's row to show more detail.

               Here's a demo file for many to many relationships that you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          • 2. Re: showing all items compatible to a product with portal tool....
            MarkusWick

                 Many thanks for that!

                 I will try that solution. At the moment I have set up a script that collects the compatibility within a simple textfield. But the better way would be the relationship way . So many thanks for that hint an the link!