Discussions

Expand all | Collapse all

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

  • 1.  How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 20:22
    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.


  • 2.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Top
    Contributor
    Posted 01-25-2018 20:31
    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?



  • 3.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 20:47
    So I would use my current "Companies" table for my Parent Companies.  I would create a "Subsidiary Companies" table and move the Subsidiary Companies to that table.  Then I would need to create a relationship between those two tables with "Companies" can have many "Subsidiary Companies."  I would also need to create a relationship where "Subsidiary Companies." can have many "Contacts" like I have for "Companies" can have many "Contacts" already.  Is that correct?  Is there anything else I left out?  On the "Companies" Table I can have a field to pull related "Subsidiary Companies" (Subsidiary) and on the "Subsidiary Companies" table I can have a field to pull related "Companies" (Parent) ?  For the existing contacts how can I have them be connected to a Parent Company and a Subsidiary?  Do I need a new field?


  • 4.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Top
    Contributor
    Posted 01-25-2018 21:07
    Yes that is right, it requires you to go through and relate your Parent Companies and Subsidiary Companies but ideally long term it will be neater and help keep your data clear. Then once you relate Subsidiary Company and Contacts you have 2 choices. You can either create and relate Contacts to the specific Subsidiary Company via the Related Subsidiary Company. Also for any contacts that are related to the Parent you might also want to view you can create a field that is the Report link field in Subsidiary Company.

    A Report link displays records on a Form based on matching a field in one table to another, and once your data is filled in your Subsidiary Company and your Contacts table will have one very solid field in common, they will both have a Related Parent Company field with the same value. So you can set the report link to match Related Parent Company in Subsidiary Company to the Related Parent Company field from Contacts. This way you will have the report of the contacts specifically related to this Subsidiary Company and a list of the contacts that were related to the the Parent Company. It will allow you to more easily differentiate the two. It just takes a bit more work in the beginning to get all lined up. Then in the future you can either chose to add a contact who is just for one of your Parent Company's or a contact who is related to the Subsidiary Company. I have linked below a little video that shows what a set up like that might look like in an App

    https://www.screencast.com/t/pcc3oUACPit">https://www.screencast.com/t/pcc3oUACPit">https://www.screencast.com/t/pcc3oUACPit


  • 5.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 22:44
    When I view the "Parent Company" on the Subsidiary table it is showing an ID# instead of the Name.  When I edit it shows the name, how do I make it show the name when viewing?  Sorry for asking things that should be simple._


  • 6.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 22:53
    I will pick up the slack in case Even signed off for the day :)

    No doubt you have a lookup field for Name.  Go to the relationship and click into the field [Related Parent Company].  That holds the [Record ID#] of the Parent company record.  Useful for databases but less so for your users.

    Edit the field properties for the field [Related Parent Company, to assign a Proxy field of name.

    Then put name on your form and remove [Related parent company] from the form.

    Then magically the form will act as [Related Parent Company] in edit mode and will act as [Name] in view mode. ie in View mode it shows the Proxy field.


  • 7.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 22:56
    Thank you so much!


  • 8.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 23:09
    Fixed it right up.  I have another case where it shows as the record ID when editing but not when viewing.  This is a field that uses a summery field of "Record ID#.  I can't seem to get that one fixed, any suggestions or more info I can provide?


  • 9.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 23:14
    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.


  • 10.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 23:37
    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?


  • 11.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 23:45
    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.


  • 12.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-26-2018 00:08
    Man you are good!  All working now!


  • 13.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-26-2018 00:14
    Thx. I think I�ve done the �10,000 Hours� a few times over.

    https://en.m.wikipedia.org/wiki/Outli...


  • 14.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 21:15
    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.


  • 15.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 21:21
    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?


  • 16.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-25-2018 23:42
    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. 


  • 17.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Top
    Contributor
    Posted 01-26-2018 15:09
    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. 


  • 18.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Top
    Contributor
    Posted 01-25-2018 21:17
    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. 


  • 19.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-26-2018 14:56
    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. 


  • 20.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Top
    Contributor
    Posted 01-26-2018 15:08
    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. 


  • 21.  RE: How can I create and relate a Parent Company and a Subsidiary Company

    Posted 01-26-2018 22:56
    Thanks for the reply!