AnsweredAssumed Answered

Table instances - I have no clue what I'm doing

Question asked by caz3000 on Apr 23, 2010
Latest reply on Apr 24, 2010 by caz3000

Title

Table instances - I have no clue what I'm doing

Post

Hi,

 

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:

 

PERSON

ALLEGATION

DOCUMENT

 

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 PERSON

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

PERSON::NameCombined

 

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

Outcomes