Multiple Relationships between tables

Question

So currently I have a Projects table and a Company table. A company has many projects. I also have an offices table and each Company has many offices. I need to also store billing and shipping information. I am torn on making these free form fields or relationships. I think a relationship is the right way to go....

So do I create another relationship between Companies and Projects since it is independent of the initial link or should I create a many to many relationship? I think I create multiple relationships... One for the Project to Company primary relationship, another one to many for the bill to and another one to many for the ship to....

Thoughts on that?

------------------------------
Ivan Weiss
------------------------------

markshnier__you · Answer

You have not actually stated what you goal is, but I assume that you need Office information down on the Project.

I would just make a relationship where 1 Office has Many Projects and make that a Conditional drop down after based on the [Related Company] of the Project.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------

ivanweiss · Answer

Mark,As mentioned in the first post I need to record the Bill To and Ship to information in the project.&nbsp; This might be different from the Company contact we are dealing with for the project itself.For example, the project might be Boeing in Renton WA but we might be billing Boeing in Seattle WA.&nbsp; and shipping to Boeing in Renton WA.&nbsp; I need to have those three independent sets of addresses storedIt also could be a project for Boeing in Renton WA, billing JLL in Chicago IL and shipping to Boeing in Renton WA.&nbsp; So multiple combinations are possible------------------------------Ivan Weiss------------------------------

markshnier__you · Answer

I'm not sure of all the tables and relationships in your app, but basically you will need to have a table of those Bill To Ships to's somewhere. Not sure if that is your "Offices Table". The have a Relationship to look those up down to your Project. Then figure out what you want to use for a Conditional Drop down to only offer up relevant Offices. Any field that you can coax down to the project can be used to filter the conditional drop down.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------

ivanweiss · Answer

Mark, that I get.The question was should I create "multiple" one to many relationships to the Company table:One for the Company for which we are performing the project.One for the Company we are billing for the projectOne for the Company we are shipping to for the project (in case it is different from the first)Or do I need to do a many to many relationship with a joining table in there?&nbsp; I think three one to many relationships are ok but I wanted to confirm before creating that structure------------------------------Ivan Weiss------------------------------

markshnier__you · Answer

I would say yes, you will need multiple relationships.&nbsp; But one technique is how to deal with the vanilla situation where they are all the same Company but still how to allow for overrides.&nbsp; ie lets not force the user to choose three companies manually.The expedient solution is to use form rules to default the 2nd two drop downs for company to equal the first drop down where the company is not blank.&nbsp; BUT then that does not really force the user to think much about the overrides.So another option is to ave those two extra drop downs for company override and then have calculated fields to resolve which company to use for the actual relationships where you pull in the data.&nbsp; ie you would have 5 relationships.One to get the man company,One to allow an override for the Billing company (but with no lookups)&nbsp;One to allow an override on the Shipping company (but with no lookups)&nbsp;Then a calculated field for [Related Billing Company (final)] where you have a relationship to pull in lookup fields.Then a calculated field for [Related Shipping Company (final)] where you have a relationship to pull in lookup fields.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

Forum Discussion

Multiple Relationships between tables

Related Content

Using Grid Edit to Edit Multiple Records at Once

Cross App Relationships

Edit Multiple Records at Once with Grid Edit

How to Link Tables in a Complex Many-to-Many Relationship?

How to consolidate multiple rows into one