Multiple Relationships between tables
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 info...
Forum Discussion
Mark,
As mentioned in the first post I need to record the Bill To and Ship to information in the project. 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. and shipping to Boeing in Renton WA. I need to have those three independent sets of addresses stored
It also could be a project for Boeing in Renton WA, billing JLL in Chicago IL and shipping to Boeing in Renton WA. So multiple combinations are possible
------------------------------
Ivan Weiss
------------------------------
As mentioned in the first post I need to record the Bill To and Ship to information in the project. 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. and shipping to Boeing in Renton WA. I need to have those three independent sets of addresses stored
It also could be a project for Boeing in Renton WA, billing JLL in Chicago IL and shipping to Boeing in Renton WA. So multiple combinations are possible
------------------------------
Ivan Weiss
------------------------------
MarkShnier__You
Qrew Legend
Qrew LegendI'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
------------------------------
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
- 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 project
One 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? I think three one to many relationships are ok but I wanted to confirm before creating that structure
------------------------------
Ivan Weiss
------------------------------- MarkShnier__YouI would say yes, you will need multiple relationships. 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. 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. 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. 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)
One to allow an override on the Shipping company (but with no lookups)
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 Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------- Thanks Mark. All squared away. For right now I am going to keep it simple nad just have the dropdowns but can always add the functionality down the road.
------------------------------
Ivan Weiss
------------------------------