Table with relationship to another fields in itself?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

We have a table with part numbers which can be superceeded with new parts. 

We need to keep the link to the old part with a link to the new part.

So in the table we have Part Number (original) and NewPartNumber which  will link to the new Part Number.

Part number 1234
NewPartNumber 4567 (which is also a Part Number 4567)

Any ideas thanks?

Photo of Michael Graham | Insight Global UK

Posted 3 years ago

  • 0
  • 1
While it may sound odd, a table can be related to itself.  Try making that relationship and then when a new part is introduced, you can relate it to the old part and thus there will be a hyperlink method to jump back and forth.  One side will be a hyperlink and then other side will be an embedded list of related parts.
I created a relationship for the table with itself but couldn't figure out how to get the link against the NewPartNumber.   I already have 85,000 parts and about 20,000 of these have NewPartNumbers ???
:)  So let me guess that your idea of a good time is not to be linking those 20,000 Parts manually?

To make this work automatically, the Key field of your table will first need to be changed to the Part # field.  Obviously with that much existing data, this app is seriously "In production".  So, you need to Copy your app and do these changes as a test first in the Copy Of app.

To make the Part number field be the Key field it will need to be Unique and non blank.  I suggest that a first step is to scrub your data by making a summary report on Part number ands then click the  count column twice and save to save the report sorted by duplicate part numbers at the tip.  Then they all need to be cleaned up.

Contact me me off line via the information in my Profile if you would like some consulting assistance with this change.
Well I thought they were already linked?
If the old part and new part are in the same record then there must be an easier way of making a link to the new part?    
I already have the parts linked by relationships to drawings which have multiple parts so breaking that relationship is something i just don't want to do.    Is there another approach?
You would not be breaking any relationships.  QuickBase preserves them when you change a Key field.

However, if you do not want to just want to make any new relationships, you can just create a new field of type Report Link.   On the left side select the Part # (Original) on the right side navigate to the same app and table and select NewPartNumber#.

Then put that field on the form and have the records for the Original Parts appear directly on the report.  That last part is set in Form properties.
Perfect!!!!!!!!!!  Thanks