Forum Discussion
EvanMartinez
7 years agoQuickbase Staff
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?
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?
- MarkComish7 years agoQrew Assistant CaptainSo 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?
- EvanMartinez7 years agoQuickbase StaffYes 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 - MarkComish7 years agoQrew Assistant CaptainWhen 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._
- QuickBaseCoachD7 years agoQrew CaptainI 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. - MarkComish7 years agoQrew Assistant CaptainThank you so much!
- MarkComish7 years agoQrew Assistant CaptainFixed 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?
- QuickBaseCoachD7 years agoQrew CaptainCan 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. - MarkComish7 years agoQrew Assistant CaptainIt 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? - QuickBaseCoachD7 years agoQrew CaptainOK, 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. - MarkComish7 years agoQrew Assistant CaptainMan you are good! All working now!