How can I create and relate a Parent Company and a Subsidiary Company

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • In Progress
I have several companies in my "Companies" table that are Parent Companies.  I have several Subsidiary's of the Parent Companies listed as Companies as well.  How can I relate the Subsidiaries and the Parent Companies to each other?  I also have contacts that need to belong to both the Parent Company and the Subsidiary Company.  On the "Companies" table I want a place for if you select the Parent Company to select and relate its Subsidiary Company and visa versa.  Then I need a way for the contacts to be able to be related to either the Parent Company, the Subsidiary or both if needed.

Hope this makes sense.  I am very new to QuickBase and appreciate any advice.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,744 Points 5k badge 2x thumb
Hi Mark,

For scalability in the long term I would recommend planning to build that out so that you have three tables. One for Parent Companies, one for Subsidiaries, and one for Contacts. This way you can have Parent Companies that have Subsidiaries and then both Parent Companies and Subsidiary Company can be related to Contacts. This way you can have a nice flow from Parent Companies to Subsidiary and then from both those tables to Contacts. This keeps all of your data organized and flowing in a way that breaks down the two types of companies you have. Unless you have a use case where a company would be both a Parent and a Subsidiary?

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Can you describe the relationship where that field is used?  Does that have a lookup field which can be used as a Proxy?

But the other choice is less elegant but workable.  Just set the [Related Parent] field on the form to only be used for Edit and Add, and also have an appropriate lookup field on the form set to show in View only.  Those are set in the form properties.  That will work, it really just tat now you have a but of extra clutter on the form when in the form editing mode,  but to the users it is the same effect as a proxy field.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
It came about from needing this for another table...

You can do this with a reverse relationship.

On the current relationship make a summary field of the Minimum of the field [Record ID#] subject to the filter that the title equals Chief Compliance Officer.   Call that field [Record ID# of Compliance Officer Contact]

Then do a new reverse relationship where 1 Contact has many Companies, but during the setup do not let QuickBase create a new field for you for the reference field on the right side of the relationship.  Instead use the field [Record ID# of Compliance Officer Contact].

Then just look up any info such as the name field from the Contact record to the Company record.



Hopefully that will give you the needed info?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
OK, I think I get it. This is a problem in edit mode.

When you created  the reverse relationship Quick Base helpfully always makes the very first lookup field to be the Proxy field. 

But in this you don't want that.  So on that field for [Record ID# of Compliance Officer Contact] it is probably set to have a proxy field.  Get rid if that setting on that field. In edit mode you want the field to just behave as a regular lookup field, and not show that field [Record ID#] field to the user in edit mode.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
Man you are good!  All working now!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Thx. I think I’ve done the “10,000 Hours” a few times over.

https://en.m.wikipedia.org/wiki/Outli...
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
Wow I really appreciate all the info.  I think I will make a copy of my app and try to make all the changes and if I mess up I can use the copy too make sure I am back to where I started.  I will give it a shot and let you know how it goes.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
One thing I didn't think about is "Opportunities" Table.  Sometimes an opportunity will be related the the Parent Company and sometimes the Subsidiary Company.  I guess if I copy the "Companies" Table and rename the copy to "Subsidiaries" table it would retain all the relationships created?  Then I can just edit the data based on which Table it is in?
Photo of Anna Kelley

Anna Kelley

  • 862 Points 500 badge 2x thumb
I would probably relate Opportunities to both Parent Company and Subsidiary company tables - meaning Opportunities would become a child table to both Parent and Subsidiary tables. Then, if I want to have a master record of all Parent company AND Subsidiary company opportunities, I'd pull through theOpportunities from the Subsidiary to the Parent company table with summary fields or embedded reports.

I'm better at drawing out the relationship than typing it out but I'd describe it exactly as Evan depicted in his initial response to you, except replace Contacts with Opportunities. Hope that makes sense. 
(Edited)
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,744 Points 5k badge 2x thumb
Hi Mark,

I would agree with Anna that you could replicate this build to share Opportunities as well. This way a Parent company can have Opportunities and a Subsidiary could have their own opportunities. 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,744 Points 5k badge 2x thumb
Not a problem at all Mark, I have built some similar use cases in my own apps before. Your end result will be a little rough but you can always stream line it and clean it up over time to better suit your needs. 
Photo of melizzza

melizzza

  • 436 Points 250 badge 2x thumb
I had the same problem with jobs (parent/child jobs) as well as customers. 
It's a little confusing at first, but I kept all customers in one table and created a relationship customer-customer. This allows for a company to be a parent or subsidiary. I hadn't looked into any of the implications of doing it this way, but maybe someone can come along and tell my why it's not a recommended option? :) 

On jobs for example - our form has a 'related jobs tab' you can either assign the parent or view child jobs from the tab. 
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,464 Points 5k badge 2x thumb
That is another way of doing it. From a technical standpoint creating a self referencing table causes some options to no longer be available such as copy master detail buttons. Self referencing tables disable this function and require an entire re-architecture in the future if that is something that becomes needed or requires a custom scripted button.

From an experience standpoint, being in a Care role before this I've just seen a number of customers who caused themselves a great deal of heart ache trying to navigate a self referencing table when it comes to reporting and keeping the flow of their information clean and organized. Self-referencing tables are functional but I usually prefer to break things up into distinct tables to leverage that separation as a tool. Also long term it helps to keep the tables from individually growing too large as the data is naturally broken up. 
Photo of melizzza

melizzza

  • 436 Points 250 badge 2x thumb
Thanks for the reply!