4 Replies Latest reply on Apr 11, 2011 2:24 PM by AdamReed

    parent/child company relationship



      parent/child company relationship



      In my database project I have a company table which contains fields like company id, company name, type, address, etc.  I would like to be able to identify some companies as parents or subsidiaries of others.  Currently I've just created another instance of the company table, and am linking "company id" to "parent company id" -- but some strange things are happening (like the id count will be one number higher, or I create duplicate records, etc.).  Could someone walk me through the simplest way to do this?

      Thanks very much,


        • 1. Re: parent/child company relationship

          Please describe exactly what you have set up and then exactly how it is failing. What you describe sounds feasible, but we don't know the details that are keeping it from working for you. "id count will be one number higher", for example doesn't really mean anything to me is this the serial number? a record count? the number of the parent company or the child?

          If CompanyID is a serial number field, you should have this relationship to link (possibly) many child companies to one parent company:

          Companies::ParentID = ParentCompany::CompanyID

          where ParentCompany is an additional table occurrence of Companies.

          • 2. Re: parent/child company relationship

            Sorry to be unclear -- I've tried a few different solutions and run into different problems, but what what you've described is what I believe I'm now doing (although it isn't working yet).

            I have a table "Company" with three fields:

            - a serial number field "Company ID"

            - another serial number field "Child Company ID."

            - Company Name (text field)

            I have an additional table occurrence of that table called "Child Company."

            I am linking "Company ID" in the company table to "Child Company ID" in the other occurrence of the "Company" table, and allowing the creation of new records in the "Child Company" table.

            On my layout I've created a portal to "Child Company" through which I'm trying to define these relationships.

            In this example the "id count" isn't off -- please disregard, but instead duplicate records are being created (of the children) when I establish the relationship between child and parent.  If I don't allow the creation of new records I'm unable to select the names in my portal.

            I hope that's clearer, and thanks for your help.

            • 3. Re: parent/child company relationship

              Not what I am describing here.

              There's no need for the second serial number field. There should be just one in use here. CompanyID is an auto-entered serial number. ParentID is just a number field.

              The attempt to add/edit records in your portal is what is creating duplicate child records. You can use a portal to Companies on the ParentCompany layout to display the child records, but you can't use it to add child records as those records are not listed in this portal. (Nor are they in your portal.)

              To link a record to a given parent, you enter/select the company ID for that parent in the ParentID field. You can define a two column value list that lists all the company ID values from Companies in column 1 and all the company names from Companies in column 2. The user then selects a company's name, but the value list enters the number.

              • 4. Re: parent/child company relationship

                Thank you!  That works perfectly.