Forum Discussion

AnnettaColeman's avatar
AnnettaColeman
Qrew Cadet
4 years ago

Grandparent and Parent relationships

I am fairly new to QuickBase and trying to wrap my head around relating both Grandparent and Parent Tables to numerous common children tables.  

Example:   
  • Project Table is a Parent to Change Request Table
  • Both Project Table and Change Request Table are Parents to numerous common tables for
    • Attachments
    • Actions Needed
    • Financial Summaries
    • Tollgate & Quality Reviews
  • In this relationship Project Table is both a Grand Parent and also a Parent to the common tables

Problem:
I am struggling with how to set up table relationships so that from the Project Table Main Form I can easily see both Children Records and also Grandchildren Records.  What is the most straightforward way to accomplish this?   I have tried allowing all of the relationships to be established via Related Project / Related Change Request and also via common Reference Proxy to Project ID but embedded report link continues to show only Children and not Grand Children. 

Thanks in advance.



------------------------------
Annetta Coleman
------------------------------
Relationships
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    Do I understand correctly that, for example, a Project Table may have many attachments, but also, a project table may have many change requests, and those change requests ALSO have many attachments.

    You would like the report link on the Projects table to show ALL attachments. The attachments for the Project, and the attachments for ALL the change requests?

    ------------------------------
    Mike Tamoush
    ------------------------------
    • AnnettaColeman's avatar
      AnnettaColeman
      Qrew Cadet
      4 years ago

      Mike -
      Yes, this application is to provide Portfolio Management over hundreds of projects.   

      The Project Table is a parent to the Change Request Table.   Some projects have zero change requests, other multi-year projects may have upwards of ~50 change requests.   Both Projects and Change Requests are parents to numerous children tables (Attachments, Project Teams, Weekly Project Health Status, Monthly Financial Summaries, Tollgate and Quality Reviews, etc.)   Literally a fairly complex set of relationships and need to aggregate data that spans both child and grandchild views.   

      As an update: I was able to confer with a colleague who provided me some additional coaching on using formula fields in the children tables to aggregate Project IDs and then using report links in both the Project Table and the Quality Control Tables to be able to aggregate both child and grandchild data.

      I remain curious about native QuickBase methods to manage complex relationships such as these and hopefully provide a more straightforward route to aggregate grandchild data back to the grandparent.

      Thanks in advance for your guidance.



      ------------------------------
      Annetta Coleman
      ------------------------------
      • MichaelTamoush's avatar
        MichaelTamoush
        Qrew Captain
        4 years ago
        If you want one report link on the grandparent that shows all the children and grandchildren, you could do it in the following manner.

        In the child table there should be a numeric field called 'Related Project'. Lookup this field down to the grandchild table. Now in the grandchild table you will have two related fields. This one you just made ([Child Table - Related Project]), and another one called [Related Project] (the latter is from the direct relationship between the Project table and grandchild).

        Make a formula numeric field in the grandchild called [Related Project Formula].
        if([Related Project]>0, [Related Project], [Child-Table - Related Project]). Essentially, you are just spitting out whichever related project is populated.

        Back on the grandparent, create a report link, where Record ID# = Related Project Formula

        ------------------------------
        Mike Tamoush
        ------------------------------