Expand all | Collapse all

Connecting 4 tables, best solution?

  • 1.  Connecting 4 tables, best solution?

    Posted 03-28-2018 13:04
    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!

  • 2.  RE: Connecting 4 tables, best solution?

    Posted 03-28-2018 16:45
    When you want true tracking on a multiple levels you have to create a 'joined table'  (for whatever reason, QB people always say 'many-to-many' which is not correct).

    So you would need to create the master tables and the joined tables.  

    ...just brainstorming...
    You would need the following tables..

    Material /Item Components (Joined table)

    Client --> (has many) Agreements
    Client --> Materials
    Clients --> Documents

    Agreements --> Materials
    Agreements --> Documents

    Materials --> Material Components
    Materials --> Documents

    Components --> Material Components
    Components --> Documents

    Material Components --> Documents

    This is just a rough guess based on what you described.  Honestly I'd guess you have a lot more moving parts that you are trying to track, so I'm sure we are missing things.

  • 3.  RE: Connecting 4 tables, best solution?

    Posted 03-28-2018 19:47
    Thanks, Matthew. You are correct I was guided to utilize a Many to Many, but I find that this is restricted to 2 tables referencing one and other. I'll have to look up more information about a joined table, and how to implement in QB. Any suggestions for literature? Also, in your initial analysis, do you feel my goal is possible without creating a large number of joined tables that feed into larger joined tables?

  • 4.  RE: Connecting 4 tables, best solution?

    Posted 04-02-2018 16:56
    The quickbase university is a good resource on these items.

    As far as you being able to do it without a bunch of joined tables.  I think it should be relatively straight forward and not too many. 

    But, i don't know your whole story.  Nobody's app is usually as simple as above.  That is only part of the story for one problem/question.

    You might need to work with your QB account manager to truly evaluate/design the app.
    Trial and error - build it and it it doesn't do what you need, try again.
    Talk with a pro ;)

    Matthew Neil - Product Specialistmneil@mcftech.com Phone: 440-201-6050 ext. 476" width="300px">