Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Creating a embedded view of projects on the company table

I have attached the relationship diagram to help illustrate what might not be a proper setup causing my issue.

On both my contacts and companies table forms I want to expose an embedded list of related projects.  So we can see activity for each Company and Contact.

On the contacts table, no issue.  I can very quickly expose my projects.

For the companies table I cannot seem to find it.  For some reason (and I think it has to do with my join table, project members) I cannot expose the report of related projects.

Something off with my relationships perhaps?
Forms
Relationships
reports and charts
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      So I think I am following it but will that allow numerous Project ID's?  So I create a project ID reference proxy in the Companies table?  But each Company might have multiple projects associated with it? 
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Ivan,
      You are overthinking this. It�s easy.

      Report link fields just love to run reports. They just need two values to match on.

      So just coax the Record ID# of the Company down to any child table in your app, or grand child, or great grand child.

      Then on the Company table create a Report Link field and on the left field in field Properties Select the Record ID# of the project and then on the right side of the configuration in field properties navigate to your app and the target table and locate the Record ID# of the project to match on.

      Then just put that report link field on your form and set the form properties to display the children directly on the form and probably build a report to use for that purpose. Sent the form to use that report.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      So just to create a bit more clarity as I have been playing with this....  I created the report link field.  What I am unsure is what field to map it to.  I have Record ID for Companies on the left side, on the right side though how do I establish where the match projects are?  I tried Project Number (which is a ref ID field) but that just returns the project with the matching ID.  I need to return all of the ID's for projects containing this company.  I thought maybe that somehow comes from the Project Members table I have.

      Project members which is a join table in essence between contacts and projects and each contact belongs to a company.  

      But in short, I cannot figure out how to get the matching projects to this specific company.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I suggest that you fact make a Report Link of of Project Members on your companies table by looking up the field probably called [Related Project] in Contacts down to the Project members table and called it what it is which is [Record ID# of Project]

    Then I would use a summary report of unique Projects as the report to use on Companies for the Report Link field.  That is because the Projects will be repeated on the Project Members table and presumably you just want to see each Project once. 
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Okay so did the first one no problem.  I have a report link and I can see Project Members:  Related Project.  

    But what am I using for the left side of that report link?  I am not sure how to generate that summary report you are referencing?  I of course have a report on the reports table that shows all of my projects.  But I am not sure from the companies table what to do (attached is a screenshot that might help)


    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      And just as a reminder by companies table is not at all related to my projects table.  That is all done indirectly via the project members table (or I have my logic wrong on how I am doing this)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I miss spoke aboveThis is what I meant to say.

    I suggest that you fact make a Report Link of of Project Members on your companies table by looking up the field probably called [Related Compny] in Contacts down to the Project members table and called it what it is which is [Record ID# of Company]

    So on the configuration for the report link field on your company�s table the left side will be record ID and the right side will be a record idea of company located on the project members table.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      So that is where the issue is.  I have a related contact not a related company available in that list of options
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      No problem. Just look up [related company] from your contacts table down to the Project Members table but re-label it what it is, which is [Record iD of company].
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      Okay, getting there!  Thanks as always for your help.  I actually did this but forgot it would be under Project Members - Contacts - Related Company.  So I did have that right as you said :)

      I changed the report as appropriate and now we are getting the list of projects....

      One last thing though, on a few of them I have multiple project members from the same company on the same project.  So how do I only show the project once?  For example on my Anthem project I have (3) people from Compass Group (company) s it is showing the Anthem project (3) times.