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 Mission Notes



Crew Mission Notes

Crew Members





Serial Numbers

Squadron Assignments





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, 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.