Table instances - I have no clue what I'm doing
I'm pretty new to Filemaker. I'm using Filemaker Pro 11 on MacOSX 10.5.
The problem I'm having probably has a simple answer but I can't for the life of me work it out.
I'm creating a database to track investigations. Some of the tables I'm using are:
Each of these has a many-to-many relationship with each other (eg. one person may be relevant to multiple allegations, and each allegation may involve multiple persons; one document may have been seen by multiple persons and each person may have seen multiple documents; one document may be evidence for multiple allegations and each allegation may have multiple related documents). I have therefore created the following linking tables:
ALLEGATION DOC RELATIONSHIP
PERSON DOC RELATIONSHIP
The relationships are defined as follows:
PERSON::__kp_PersonID = ALLEGATION PERSON::_kf_PersonID
ALLEGATION PERSON::_kf_AllegationID = ALLEGATION::__kp_AllegationID
ALLEGATION::__kp_AllegationID = ALLEGATION DOC RELATIONSHIP::_kf_AllegationID
ALLEGATION DOC RELATIONSHIP::_kf_DocumentID = DOCUMENT::__kp_DocumentID
DOCUMENT::__kp_DocumentID = PERSON DOC RELATIONSHIP::_kf_DocumentID
In the DOCUMENT record, I have a portal to display records from PERSON DOC RELATIONSHIP, which is supposed to display a list of all the persons connected to that document. This contains the following fields:
PERSON DOC RELATIONSHIP::_kf_PersonID
and some other fields specifying the nature of the relationship.
The problem is that no matter what is in _kf_PersonID, the Name displayed is the name of the person in the first PERSON record in the database, not the name of the person whose ID is in the PERSON DOC RELATIONSHIP record. I'm guessing this is because Filemaker is looking up all the persons associated with all the allegations through the chain of relationships, not just the persons associated with the document. I'm also guessing I'm supposed to solve the problem with table instances, but can't work out what I'm supposed to do because I don't really understand table instances.
Can anyone help?
(NB: I'm aware that I could use a calculation to fill in a field with the person's name in the Person Doc Relationship record, but then this would not update if changes / corrections were made to the Person record.)