Connecting 4 tables, best solution?
There are 4 tables all interacting with one and other. I will describe them from the bottom up:
1) Documents - this will house all documents that are referenced in the various next 3 tables. The hope is that a document can be related to multiple tables at the same time, without creating multiple records of that document.
2) 3rd party components table - this table tracks pieces of a whole material that belong to a whole material (say the handle bars on a bike).
3) Materials Table - This table tracks records of a whole materials (say a bike) that are utilized for their own record keeping purposes and in the next agreements table. This record needs to be able to show multiple related 3rd party components from table 2.
4) Agreements table - this table tracks agreements (contracts) under which the organization running the database shares the materials in table 2. The hope is that you can add multiple materials to an agreement record, and pull related data from 3rd party components>>materials>>agreements.
I need a material record (in table 3) to show all 3rd party components that "apply" to the material, which would be indicated by the user in some manner. That meaning the third party component is part of the whole material, but needs to be tracked separately because the components could apply to multiple different material records. I need to be able to add multiple 3rd party components (and some lookup fields from the 3rd party components records, if possible) to a material record.
I need an agreements record to be able to reference multiple material records, because an agreement can contemplate several materials. I'd like the agreements record to be able to pull up any third party components info from those associated material records as well.
Overall, I need the app to be able to connect each record that has a mention of the other by some indication. When I explained this to QB, it was suggested I start a many to many relationship table. Unfortunately, from my understanding, you can still only relate one record to multiple, but not multiple to multiple. (Please excuse me if this is wrong).
Is it easier to do this by using embedded reports based on a textual field in the respective records as opposed to the "creating a relationship" method? Can this be done effectively with the creating a relationship method? Should I be looking at custom formulas and scripts to produce these types of results? Would appreciate any input!
1) Documents - this will house all documents that are referenced in the various next 3 tables. The hope is that a document can be related to multiple tables at the same time, without creating multiple records of that document.
2) 3rd party components table - this table tracks pieces of a whole material that belong to a whole material (say the handle bars on a bike).
3) Materials Table - This table tracks records of a whole materials (say a bike) that are utilized for their own record keeping purposes and in the next agreements table. This record needs to be able to show multiple related 3rd party components from table 2.
4) Agreements table - this table tracks agreements (contracts) under which the organization running the database shares the materials in table 2. The hope is that you can add multiple materials to an agreement record, and pull related data from 3rd party components>>materials>>agreements.
I need a material record (in table 3) to show all 3rd party components that "apply" to the material, which would be indicated by the user in some manner. That meaning the third party component is part of the whole material, but needs to be tracked separately because the components could apply to multiple different material records. I need to be able to add multiple 3rd party components (and some lookup fields from the 3rd party components records, if possible) to a material record.
I need an agreements record to be able to reference multiple material records, because an agreement can contemplate several materials. I'd like the agreements record to be able to pull up any third party components info from those associated material records as well.
Overall, I need the app to be able to connect each record that has a mention of the other by some indication. When I explained this to QB, it was suggested I start a many to many relationship table. Unfortunately, from my understanding, you can still only relate one record to multiple, but not multiple to multiple. (Please excuse me if this is wrong).
Is it easier to do this by using embedded reports based on a textual field in the respective records as opposed to the "creating a relationship" method? Can this be done effectively with the creating a relationship method? Should I be looking at custom formulas and scripts to produce these types of results? Would appreciate any input!
