Forum Discussion

ShalomEguale's avatar
ShalomEguale
Qrew Member
2 days ago

Help connecting Table to Table Relationships

Hi,

I have 4 tables

  1. Site table - houses all site info including site ID# which is the unique identifier to be used to relate/connect all tables.
  2. Risk table - houses all the risks. 1 site can have many risks
  3. Milestone table - Each risk can have many milestones
  4. Site Risk Register - Joining table that has all the risks and milestones relating to each site on one table

I'm struggling to figure out how to connect these tables. I figured all tables need to have that site id# field in order to connect. So I created a one to many relationship with the Site table and the other 3  tables so all 3 tables have a site id# lookup field.

Then I created more relationships. One Site Risk Register has many Risks, One Site Risk register has many Milestones and then I also made One risk table has many milestones. I added dummy data in the tables but it is not reflecting in the Risk Register join table. 
Can I get help on what I did wrong and how to properly set up these table relationships

Relationships

2 Replies

Sort By
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    19 hours ago

    Ok, I think you might be overcomplicating your design.  Your [related site] field in your Risks table can be passed down into your milestones table via the [related risk] relationship in milestones; so your "siteid# and any other lookup-information can easily be displayed and reported on from milestones.

     

    You state that the Site Risk Register is a join table housing all the Risks and Milestones relating to a site in a single table.  But, by definition; every Risk having multiple milestones means that each Milestone is a "join" record of a Risk and Milestone for a Site.  Therefore; if you display ALL the milestones for a single Site; you get all the Risks automatically as a groupable-value.

    You can then embed a Report Link field-type on your Sites table that lists all the Milestones and group that report by the Risk (parent).

    If your Site Risk Register is supposed to be a list of "all risks" and "all milestones"; then you can build that fourth table and have a Pipeline create the join-table records there for all of them; but I would question the purpose of this "Site Risk Register" as it relates to Sites and what you would do with that extra table?

    • ShalomEguale's avatar
      ShalomEguale
      Qrew Member
      18 hours ago

      Hi Laura,

      Thanks for your response. I need the Site Risk Register table because this is actually the main table that will be used. The Site Risk Register table has a form with questions users need to answer about the risks and milestones related to that site. So I plan to add the embedded link for both the risk and milestone reports in that form

      The Site table is a connected table from a different app and is only there so I can pull the Site ID#s to use as a lookup field. Ideally, that table will be hidden once everything is set up.

      Are you saying all the tables just need to have the same related field in order to be connected?