Discussions

Expand all | Collapse all

Creating a embedded view of projects on the company table

Ivan Weiss06-23-2019 12:47

  • 1.  Creating a embedded view of projects on the company table

    Posted 06-22-2019 19:46
    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?


  • 2.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 08:32
    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





  • 3.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:13
    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? 


  • 4.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:26
    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.


  • 5.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:38
    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.


  • 6.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:42
    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?


  • 7.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:48
    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.


  • 8.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 12:47
    Sorry 


  • 9.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 13:01
    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. 


  • 10.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 13:12
    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)




  • 11.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 13:13
    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)


  • 12.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 13:28
    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.


  • 13.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 13:54
    So that is where the issue is.  I have a related contact not a related company available in that list of options


  • 14.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 14:02
    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].


  • 15.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 14:09
    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.


  • 16.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 14:51
    Right, I anticipated that problem. So that is why I suggested that you make a summary report, not a table report, a summary report of projects. That way each project will only appear once.

    Make an unfiltered report of all unique projects and then use that report on the form. That way the report link will do the filtering for only projects for that company and each project will only appear once.


  • 17.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 15:11
    Okay so I looked at that but the summary report lists each project with a number next to it showing how many were in the report.  I think this is going to be confusing to my end users.  I was hoping to display something like the attached which is what is in my contacts table. This provides a bit more information to them so there is useful data on that page and they can directly enter a project from this screen if they need to.
       




  • 18.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 20:11
    I had to do 63 km bike ride. Now I�m settled in with beer and pizza on order.

    You can add as many columns as you like by adding more levels to group by, but it will still show the count of the project members


  • 19.  RE: Creating a embedded view of projects on the company table

    Posted 06-23-2019 22:36
    However, if it does bother you then post back and I will explain how to filter the project members so that the report will only return one project member per project. Then you can use the simple table report and it won�t have that extra charge at the end


  • 20.  RE: Creating a embedded view of projects on the company table

    Posted 06-24-2019 14:00
    Good healthy bike ride followed by beer and pizza huh?  sounds like my style lol

    Ideally, I would love to do the latter and filter the project members and use the table report.  That gives me a few advantages:

    1. I can eliminate that column I dont want to see.
    2. I can use one report in multiple places so my color coding logic etc. is all set and I dont have to repeat and maintain it across multiple forms.


  • 21.  RE: Creating a embedded view of projects on the company table

    Posted 06-24-2019 14:54
    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.


  • 22.  RE: Creating a embedded view of projects on the company table

    Posted 06-25-2019 10:29
    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. 


  • 23.  RE: Creating a embedded view of projects on the company table

    Posted 06-25-2019 10:58
    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.


  • 24.  RE: Creating a embedded view of projects on the company table

    Posted 06-25-2019 13:57
    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?


  • 25.  RE: Creating a embedded view of projects on the company table

    Posted 06-25-2019 15:08
    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">http://www.QuickBaseCoach.com">www.QuickBaseCoach.com to carry on with this?