3 Replies Latest reply on Oct 7, 2013 8:20 AM by jlamprecht

    How to design a m:n-Relation?

      Hi there,

       

      I tried hard but failed. Mabye somebody can give me a hint how to design a m:n-relation-ship between "Companies" and "Brands". I want to have a portal that shows me all brands that are assigned to a company. Brands can be assigned to different ones but shoul only exists once (unique brand name).

       

      What I have (simplified):

      • table Companies
        • fields: ID Company, Name (should be unique)
      • table Brands
        • fields: ID Brand, Brandname (should be unique)
      • table Relations_Company_Brand
        • fields: ID Relation, ID Company, ID Brand

       

      I have made the relations between these three tables and implemented in my Company layout portal for the table Relations_Company_Brand. I also have a field "Brandname" from the table "Brands" in the portal to show the "nice" names of the brand and not only the IDs.

       

      Now my problem: how can I type in a brand name so that a relations is created with the ID of the company and - if the brand is existing - the ID of the brand OR a new entry in the table Brands is created and the new ID of the brand is used. Is there a trick? A light way to implement this? Any sample database?

       

      This would help me much!

      Kind regards, Luna

        • 1. Re: How to design a m:n-Relation?
          CarlSchwarz

          If you are trying to create a entry in the join table (relations_company_brand) then you will need to script it.  I'm guessing that you want a join table because you want multiple companies to also stock multiple brands and vice versa.

          I.E. you will want to gather the ID Company and the ID Brand in a variable, then go to the join table, then create a new record and populate it:

          e.g.

          set variable $IDCompany xxxx

          set variable $IDBrand xxxxx

          go to layout (relations_company_brand)

          new record

          set field 'ID Company' $IDCompany

          set field 'ID Brand' $IDBrand

          go to layout (original layout)

          • 2. Re: How to design a m:n-Relation?

            Dear Carl.

             

            okay, it then is not possible to achieve this just by the form-fields etc. in the layout... Does it mean that I should have a global field where the user types in the brand name and the a script makes a look-up and does the setting if the brand is already there or make a new brand entry, obtains the ID and make then the new brand entry?

            • 3. Re: How to design a m:n-Relation?
              jlamprecht

              Ok from your description, it does seem you need a join table to be able link M Companies to N Brands.

               

              My approach for this would be as follows:

               

              Starting from the company context, which I assume you are doing, have a portal of all brands that are linked to the company. The portal will be based on the join table between Companies and Brands.

               

              To add new brands to a company, I would institute a picker. So, just put a button next to or in the portal. This button will take in the current Company ID and will navigate to a picker layout that would have the list of all brands. Then, when the user has selected a brand and hit Submit/Done, store the Brand ID and navigate to the join table context. Create a new record and populate the appropriate fields with the Company ID and the Brand ID. Navigate back to the Company layout and you should have a new brand in your portal for the company.