Forum Discussion

AmberPollard's avatar
AmberPollard
Qrew Assistant Captain
9 years ago

Showing Related Projects w/o Relating Table to Itself

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?
Relationships
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    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?
    • AmberPollard's avatar
      AmberPollard
      Qrew Assistant Captain
      9 years ago

      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.

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    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.
  • QuickBase9's avatar
    QuickBase9
    Qrew Cadet
    7 years ago
    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%.

    Fields/Relationships
    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!
  • AmberPollard's avatar
    AmberPollard
    Qrew Assistant Captain
    7 years ago
    This is awesome, thank you! I hope I get a chance to try it out soon!