Interchangeably relating records from 2 tables

[SimmKa]

Hey Everyone!

I am a little stumped. Let me first explain what I am working with to give you some insight:

I have 2 tables that we will call Table1 and Table2. Table1 is the parent table and Table2 is the child. Now I have 2 fields that I need to display related data in each of these tables.

In Table 2 I need to display and link data from Table1's field that we will call Names and in Table1 I need to display related field data from Table2's field that we will call Numbers.

I need these to be able to be changed at any time so that Names field that is displaying on a Table2 Layout can be linked to a different record in Table1 and vice versa.

So if we're looking at the Table2 layout, it will show the Names field...I want to be able to relate the record I am viewing in Table2 with a different Name from table 1 and be able to use the GTRR script step and have it take me to the record in Table1 based on the relationship that was defined by me selecting a different Name and relating it to the record I was editing.

It seems that using a portal is only a one way street that works only when new records are created through the parent table (Table1) so I can't change existing relationships. It also doesn't seem to allow me to link up the parent record (Names) from the child record (Numbers) if I need to.

I've tried using a script that gets the variable from Table2 and sets the ID and data into Table1 and that works but when I try to go to the related record in the Table2 layout it doesn't work even though the data that I selected shows up.

Anyways, let me know if all that makes sense or if anyone needs further clarification. I just want to relate records between 2 tables using 2 fields and 2 layouts on the fly without having to create new records. I just want to have it work between existing records. Maybe it's just not something FileMaker is capable of but I think i'm just stumped more than anything.

Thank you in advance!

Edited December 22, 2015 by SimmKa

[bruceR]

Not a good start. More detail please, including real table names and meaningful relation descriptions. A copy or clone of the file would be best.

[SimmKa]

I think I may have figured it out, anyways. For anyone reading that might be having a similar issue:

I needed a third table to join Table1 and Table2 together. Similar to how Line Items work as a buffer between Estimates and Parts. I added my buffer table's fields to Table1 via a portal to display Table3 (buffer) fields that are actually displaying Table2 information. I added a drop down field to the portal that pulls the ID from Table2 (parent) and Table3's (child) relationship and displays Table2 data for the Number field. Then it populates the fields based on that relationship in the portal and allows Table1 and Table2 to be related in a way that doesn't totally screw everything else up. 

Hope this helps someone else out!

 

EDIT: You can change out Table1 for 'Driver' and Table2 for 'Car' and consider the buffer table as 'License Plate Numbers'. I needed to interchangeably relate existing Drivers with existing Cars through the car's License Plate Number but also be able to relate the Car with the Driver through the Driver's Name. Two layouts are involved, Driver Information and Car Information and I needed to be able to go to the related record between them after relating an existing car record to a different existing driver without creating new records.

Edited December 22, 2015 by SimmKa

[comment]

3 hours ago, SimmKa said:

You can change out Table1 for 'Driver' and Table2 for 'Car' and consider the buffer table as 'License Plate Numbers'

Can one car have many License Plate Numbers? I suspect not - and if I am right, it makes no sense to "consider the buffer table as 'License Plate Numbers'". Perhaps you meant Trips, or something like that. We really need a better description of the real-life entities that your solution is designed to track.

[SimmKa]

The Cars don't necessarily have more than 1 at any given time but they can have previous ones, which will be recorded. The Drivers can have more than 1 Car and the Cars have the plate numbers, the plate number is what is displayed on the driver's record information. The driver is only driving one car at any given time, though. The cars are being tracked by their plate numbers and need to be assigned to a different driver on a whim. The Driver's name needs to show up on the car layouts record information. More importantly the Driver record needs to be able to assign a new car on it's layout. It all boils down to relating records from separate tables that were previously unrelated to each other. 

The join table (Plate Numbers) between the car and driver tables works great for what I am trying to do it seems.

Edited December 22, 2015 by SimmKa