peterduck

Multiple relationship join table

Discussion created by peterduck on Sep 1, 2017
Latest reply on Sep 3, 2017 by peterduck

All right....I'm throwing in the towel and asking for help.

 

I have these tables that need to relate to a table named 'Source':

 

Aircraft

Aircraft Mission Notes

Buildings

Crew

Crew Mission Notes

Crew Members

Formation

Mission

Personnel

Revisions

Serial Numbers

Squadron Assignments

Station

Story

Units

 

I have set up a join table 'Join_Sources' as pictured:

 

Screen Shot 2017-09-01 at 10.17.48 AM.png

The first use was on the 'Personnel' table/layout.  Worked as expected.  See following image:

 

Screen Shot 2017-09-01 at 10.18.31 AM.png

 

I attempted to do same on 'Serial Numbers' table/layout and the result is incorrect.  See image below:

 

Screen Shot 2017-09-01 at 10.18.53 AM.png

 

It shows the same reference record.  Here are the records from the 'Join_Source' table/layout.  See images below: (note that the first image is from the 'Personnel" relationship and the second is from the 'Serial Numbers' relationship.  It's just a "coincident" that the the two fields have the same data.)

Screen Shot 2017-09-01 at 10.21.00 AM.png

 

Screen Shot 2017-09-01 at 10.21.10 AM.png

 

The relationship for 'Personnel' uses a field 'cSourceRelationship' that relates to 'ID_Personnel' in the 'Join_Source' table.  The relationship for 'Serial Numbers' uses a field 'cRelationship_Source' that relates to 'ID_SerialNumbers' in the 'Join_Source' table.    The 'Source' table uses 'cRelationship' that relates to 'ID_Source'  in the 'Join_Source' table.

 

Why in the world is the record on the 'Serial Numbers' table showing a relationship from the 'Personnel' relationship?  Here are the portals being used (in both cases 'Sources').  See images below:

Screen Shot 2017-09-01 at 11.24.19 AM.png

Screen Shot 2017-09-01 at 11.23.56 AM.png

 

In another related database I have this type of relationship working brilliantly (at least so far).  See image below:Screen Shot 2017-09-01 at 10.20.25 AM.png

 

It should be noted that calculation fields used in the relationship are indexed.

 

I believe I'm using the correct philosophy for what I need to accomplish.  My dilemma is I have many "entities" (aircraft, missions, personnel,.....so forth) that need sources referenced for many fields on each layout.  At the same time, each source may have many "entities" to apply to.

 

I would very much appreciate anyones suggestions.   It should be noted that while I'm not necessarily a "newbie" to Filemaker, this is not my profession so a modicum of understanding would be appreciated.  Let me know if more information is required.

 

Thank you in advance.

Outcomes