3 Replies Latest reply on May 28, 2011 2:55 AM by happyez

    Relationship between two fields, switching lists from one to another

    happyez

      Title

      Relationship between two fields, switching lists from one to another

      Post

      I'd love to find a better description - words fail me today...:)

      I have two fields in an Organisation table (::ORG)

      i. <<Brand>>

      ii. <<Parent>>

      <<Parent>> is the parent company of <<Brand>>. There is a SN to each record, and the relationship between <<Brand>> and <<Parent>> is flat, within the ::ORG table.

      I want to be able to list all the <<Brand>>'s that fall under a <<Parent>> name. eg.

      Diageo makes Guinness, Smirnoff, Interslate and Johnnie Walker.

      My thought was to create a self-join, and in a new layout under the new sj_ORG, do a portal linking back to the original ORG table. 

      <<Parent>> is within sj table

      <<Brand>> is within ::ORG table

      But, this didn't really work.

      I do want to show these as a portal in one table or another. It is so I can put it in a report.

      So my thinking is probably off a little or not onto it.

      Any thoughts?

        • 1. Re: Relationship between two fields, switching lists from one to another
          ninja

          Sounds like your structure is in your way.

          You seem to have three entities (or maybe two...not sure)

          1. Organization

          2. Parent

          3. Brand

          Or are Org and Parent the same thing?

          What you are looking to do would be very easy if your tables were set up as

          Org---< Parent ---< Brand

          Then you would simply put a portal on the parent table showing records from Brand.

          If a Parent has more than one Brand...then Brand should be in a related table...not "flat" as you describe it.

          • 2. Re: Relationship between two fields, switching lists from one to another
            philmodjunk

            It would seem you need at least two tables:

            Parent: One record for every parent organization
            Brand: One record for every brand, linked to Parent by ParentID--an auto-entered serial number field in Parent, but defined as a number field in Brand.

            With this structure, you can place a portal to Brand on your parent table and see all the Brands for that Parent Organization. You can also set up a list view layout based on Brand with Fields from Parent placed in Sub Summary layout parts to produce a report that looks like this:

            Parent Company Name 1
               Brand 1
               Brand 2
               Brand 3
                .
                .
               Etc.

            Parent Company Name 2
               Brand 1

            and so forth...

            • 3. Re: Relationship between two fields, switching lists from one to another
              happyez

              Yes, it was the structure...damn.. And the solution is really simple. I knew it was simple but wasn't hitting it in the right way....

              I now have to reorganise it all (again for the 5th time in the last 2 years...oh well :) I'll apply the solutions then.

              Thanks very much for your time!

              Eric