Creating a embedded view of projects on the company table

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress
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?
Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb

Posted 4 weeks ago

  • 0
  • 1
Hi Ivan,

In order to achieve your desired result, you have to pass the Project ID to your Companies table which will allow you to create a report link as described in this post.

https://community.quickbase.com/quickbase/topics/lookup-from-a-lookup



Photo of Ivan Weiss

Ivan Weiss

  • 814 Points 500 badge 2x thumb
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? 
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.
Photo of Ivan Weiss

Ivan Weiss

  • 814 Points 500 badge 2x thumb
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.
I do not see any relationship diagram posted here. Can you tell me the relationship between companies and contacts and projects? Is Project simply a grand child of companies?
Photo of Ivan Weiss

Ivan Weiss

  • 814 Points 500 badge 2x thumb
Just posted the relationship diagram.  I think the challenge I am having is how to get past the project members table.  That is where I lose the ability to connect the data.

A contact can be associated with multiple projects.
A project can be associated with many contacts.

So I have a join table called Project Members.

I believe that is where I am losing the ability to pass the data through.
Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb
Sorry 
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. 
Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb
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)


Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb
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)
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.
OK, so on the relationship where 1 Project has Many Project Members make a summary of the Minimum Record ID# of the Project members.

Call the field [Record ID# of 1st Project member]

Then look that up down to Project Members.

Then make a table report of project members where the Record ID # is equal to the value in the field  [Record ID# of 1st Project member]

Then use that report for the Report Link on the form.
Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb
Okay so I did all of that but it doesnt appear to be working right.  And I was questioning the logic but tried it anyway.  So if lets say I have a project with three different companies as project members:
Gensler, Compass Group, and CBRE.  If Gensler is the first project member it only catches the project for Gensler.  It does not catch it for Compass Group and CBRE.  I want this project to appear on each of their (3) company records. 
Ok, I agree that the solution did not solve the problem,

Let try another one. This one is easy to set up.

On the relationship between Contacts and Project Members, make a Summary Combined text field of the Project Names. It will appear in a bubble-like format when viewed as a field on the contacts record. And it will show the unique Projects which the Contacts are Assigned to.

Then on the relationship between Companies and Contacts, make a combined text Summary field of THAT field. It will give the unique projects which those Contacts are Assigned to.

If that works and you do not like the bubble-like appearance, post back and I will provide a formula to use to create a formula text field to to change the appearance of the Project list.
Photo of Ivan Weiss

Ivan Weiss

  • 824 Points 500 badge 2x thumb
Alright so now we are clearly in the right direction.  That worked.  The bubble look works for my needs as it displays the projects.  Would be a home run if I can get that into a project list like what I posted before, but that goes to a table report.

In the event I cannot how do i get the action when I click on one of those bubbles it takes me to that project record?  Right now it is taking me to a report of contacts associated with the project for the company.  I looked at the drill down behavior and not seeing an option there that would work for that.  Unless I need to build a report in the project members table?  if I am in the right place?
(Edited)
This is possible but its a lot of typing and syntax for me to explain the steps.

What needs to be done is to create Rich Text hyperlink field on Project Members to be a hyperlink to display the project.

Then create a Combined text summary field of the unique hyperlinks.

Then get those up to the Company record.

Then probably we will need to make a formula to make that back into a Rich Text field.  Can you contact me via the contact into on my website www.QuickBaseCoach.com to carry on with this?