Skip to main content
Question

How do you create the relationship back to a Parent table record from ""Orphan"" child records

  • February 24, 2018
  • 0 replies
  • 50 views
I have a table with Parking Lots and a new child table with drainage catch basins within those Lots. Each lot has a unique number that is not the record ID# (key field) and I cannot, for a number of reasons, make that Lot Number the key field.  Anyway, all of the catch basin records are a csv file import and the Lot Number is part of those records. Using a report link works to show the catch basins under each parking lot, but I need to create a Related Lot numeric reference (the Lot's record ID#) for each of the catch basin records. There are 189 lots and 305 catch basins so I need a way to get this done by formula or something.
    This topic has been closed for replies.

    One approach in these situations is to make a Sync table called parking Lot Sync in the same app to mirror the Parking lots table and then change the key field of that table to the Lot# as I assume it is Unique.

    Then you can relate the catch basins to that Parking Lot Sync table.  That may be enough to solve your issue, but if you really do need to have them attached by a Relationship, then you can make a relationship between Parking Lot Sync to many Parking lots and then do a summary maximum of the [Record ID#] field can call it [Record ID# of parking lot].

    Then look that up down to Catch basis and use that for your relation to Parking Lots.
    • ScottGrund
    • Author
    • Registered
    • February 24, 2018
    Hmmm....that sounds like a huge undertaking but I do need to have that relationship.  Since both tables have the Parking Lot Number isn't there a way I can maybe place in the Parking Lot table a formula field that says if the record has Parking Lot Number 1234 then [Record ID#]? Then someway in the Catch Basin table have the same Parking Lot Number 123 translate to that [Record ID#] and use it to relate the records?  Hope that makes sense.  I'm having a lot of trouble with relationships in this huge application and have placed a number of postings with no good results yet.  This one is only one of the recent issues but also one the simpler.  

    The other problems involve multi level relationships where I need to relate records back up about 3 to 4 generations. But that is for another day.
    I don't see another way.  I can help you set this up if you want one on one assistance, but like you said you have hundred of records to link up and they will be changing in the future so you need something automatic.
    • ScottGrund
    • Author
    • Registered
    • February 24, 2018
    Yep. Thanks anyway.
    Terms & ConditionsAccessibility statement