Showing Related Projects w/o Relating Table to Itself

  • 0
  • 1
  • Question
  • Updated 12 months ago
  • Answered
I'd like users to be able to relate projects to other projects such that on the project's form they can:
  • see a list of their related projects appears as an embedded table
  • relate the project they're viewing to another project
I deleted the Projects-to-Projects relationship I had because it prevented from using the button that copies a project and it's child tasks.

So created a new table: Project Relationship < Projects and I've figured out how to have the form show an embedded report of it's related projects, but I can't figure out how users are going to easily relate a project to another. It seems they'll need to create a Project Relationship record, give in a meaningful name to they can recognize it, then go into several projects and pick that name from the list. I think this tends to work counter to my users expect. Is there a trick or idea anyone has on how I could employ this that would prevent having to create two records, create a name and go into other projects to pick that name from the list?

How can I tweak the the embedded report to list related projects but not itself (the project being viewed)?

Thank you?
Photo of Amber


  • 590 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Do your projects have any unique or identifying information that connects them already?  Maybe a "Client" or a "Location" or a "Work Order", etc?

Basically, what determines that they are connected?
Photo of Amber


  • 590 Points 500 badge 2x thumb

My Projects table is related to some parent tables (Clients and Project Categories) but I want to give users a way to related to other projects in a more focused way.

I played a bit with this and you might be able to get it to work with a report link field if you are willing to have the users key a record ID# of the project they are trying to link to.  Or any field on the record that they want to link to if you have a better field than record ID.

I made this field

Right("000000" & ToText([Record ID#]),7)

and then this other field

List(",",If([Other project 1]>0,Right("000000" & ToText([Other project 1]),7)),
If([Other project 2]>0,Right("000000" & ToText([Other project 2]),7)),
If([Other project 3]>0,Right("000000" & ToText([Other project 3]),7)))

ThenI used these fields to make a report link field and set it to not match exactly.
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
After thinking about this some more, I'm curious as to why you had to delete the original projects - to - projects table.  You mentioned that it caused a copy function to not work.  I think we might want to visit why that didn't work.

 It might just be the details of the relationship and what QuickBase does when you make that circular relationship.

if you set it up right, and modify that script, this is going to be your best bet.
Photo of Leanne


  • 354 Points 250 badge 2x thumb
Hi Amber, I know this was a year ago, but I searched for this issue too and did not find a good answer on the boards, so I thought I would post my solution. It is not 100% perfect but 80%.

Create the 2nd Table as the Join. So you will have Projects Table and Related Projects Table.
Create a basic relationship - Projects to Many Related Projects and pull over the lookup fields. I would rename those Master Project - Name, Master Project - Manager, etc.
On the Related Projects Table, create a multiple choice field that pulls it's choices from another table. Use the Project Name field from the Projects Table.
On Projects table create a report link, Project: Name to Related Projects: Master Project Name
On the Related Projects Table create a report link, Related Projects: Project Name to Project: Name

Related Projects Form:
Multiple Choice Project Name (can rename on form to Related Project) - for Data Entry
Report Link (link to record, not the report option) - for viewing
Master Project Name - this will be read only and for reference.

Projects Form:
Add Related Project Button
Projects Table Report Link

The user will click Add Related Project Button on the Project form. On the Related Project Form they will select from the drop down. They will save and on return to the Projects form the report link will show all the related projects. They can keep adding more as needed.

Hope this helps someone!
Photo of Amber


  • 590 Points 500 badge 2x thumb
This is awesome, thank you! I hope I get a chance to try it out soon!