One to One project relations in same table.

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
I have a "Projects" table. We often have projects that are cancelled and given new project numbers. I have set up a relationship from the Projects table to itself. I am able to create a new project and relate it to the old project. I add notes in the new project in a field called Cancelled Project Details. When I relate it to the original project I can see that project number and can link to that project. 

When I try to link the old project all I have managed to get is either the record ID or text I add under "Link Text". 

I want to be able to see the actual New Job ID on the old Job and sync the Project details field. I feel like I am missing something simple. Help.
Photo of Brian Adamson

Brian Adamson

  • 140 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
Can tou ex,ain this

"When I try to link the old project all I have managed to get is either the record ID or text I add under "Link Text". 

Are you just trying to embed a report of the cancelled projects on the New project record?  Not sure what your roadblock is.
Photo of Brian Adamson

Brian Adamson

  • 140 Points 100 badge 2x thumb
What I am trying to see is the opposing Job ID on each Job. On Job Canceled I want to see Job New and on Job New I want to see Job Canceled. 

Using a relationship I can choose the Job ID in the lookup field of either the Canceled or the New. I prefer to create the New Job and link to the Canceled Job. 

When I start with the New Job I select the Canceled Job from my lookup field and I can use the Link Report from the relationship to see the New Job on the Canceled Job but I either get the "Record ID" or the text that is entered in the "Link text" box under "Display" in the field properties. I would like to see the actual Job ID. 

I also want to be able to enter notes on the New Job and have them mirrored to the Cancelled Job. With the current relationship I have to enter the notes on the Cancelled Job and they show on the New Job in a field I have set. I would rather it be one field that is mirrored but haven't been able to accomplish that as of yet.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
Brian,
I will go another round with you on this now that I'm back from visiting with Quick Base in Boston and I'm all recharged.

re: " I prefer to create the New Job and link to the Canceled Job. "

Ok, so in this setup the Cancelled Job will be the Parent and the Child will be the New job, as you will be sitting on the New Job to select which Job it is related to.  I would be very careful about naming the fields since it always gets confusing when you have a tale related to itself.

So I suggest naming the field in the relationship to be [Record ID# of Related Original Job].

On the original cancelled job the way to see info from the child is just like any parent child relationship, you have a report link field and put the children on a report on the parent record. 

The configuration of the Report link field would be that on the left you would have [Record ID#] and the right side would be [Record ID# of Related Original Job].

So that should take care of the Original Job.  

On the "child side of the relationship which is the new job, you should be able to lookup any fields you need and put them directly on the form.  Or alternatively if you prefer to have an embedded report on the form of the cancelled job, you can make a report link field, but the two fields would be opposite configured.  The Left side would be [Record ID# of Related Original Job] and the right side would be [Record ID#].

I'm doing this all in my head, so hopefully that will all work.