Forum Discussion

MICHAELSARGENT's avatar
MICHAELSARGENT
Qrew Cadet
6 years ago

Relationship setup recommendation needed

Here's my setup:

  • I have a new request table (parent)
  • I have a group assignment table (child)
  • I have another group assignment table (child)

Here's my need:

  • I want to create a resources table whereby resources supporting the parent and two children records can be added (each element can have multiple resources)
  • I want to be able to query the resource table to show me all resources across the parent and 2 child tables for one project

Issue:

  • If I create joins from each table to the resources table, then I have 3 lookup values in the resources table (parent, child, child)
    • How do I keep the records clean?
    • How do I query to say I want all resources for one project?


------------------------------
Michael Sargent
------------------------------
App builders
Relationships
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    6 years ago
    Hi Michael,

    It is possible to have a shared resources table that all three tables would pour into and then report from there. You would just want to figure out which level you would like to set your reporting up against, most likely you want to organize them all by the New Request so you can see each resource for a New Request and each of its children tables. For that set up I would recommend setting up the relationship as you mentioned but also make sure when you are setting that up you don't just pass down the Related Group Assignment or Related Other Group Assignments from the two children tables you would also want to pass down the Related Request. This way every record you create on that table will have a Related Request and then also have a Related Group Assignment or Related Other Group Assignment. Then from there you can create a formula field that helps to pull in the correct fields for reporting into two central fields one for Related Parent and one for Related child a or b. Then you can set your report to sort and group by that new Related Parent and it will show any records that relate to the same parent. 

    For the formula for reporting you can use something like:

    List("", [Request Name], [Request Name from Child Table A],[Request Name from Child Table B])

    That way whichever one is filled in the formula field will grab it and pull it into your reporting. Then for the other field you would just need to set up something like:

    If([Related Child A Record Name]="", [Related Child B Record Name], [Related Child A Record Name])

    That will pull in the child record name for the child record that passed down. This will allow you to create a report that you can then query to find the pieces related to a specific New Request all in one place. You can even get fancy and have it all report in a stylized format if you like. Linking back to the parent records can be a little more complicated but just creating a clean report from the table can definitely be done with a little formula work to help get everything consistently related. The same can be done by passing down the related parent record through each relationship and creating a formula field to capture the related parent record field in one place and setting the Report Links on your New Request table to find all records that have the same record id as the New Request record in that table. I hope this suggestion is helpful Michael.

    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------