I think that you have these relationships:
That correct? (can't see any uploaded image of your relationships graph. Did you upload a file of the correct type? (Gif, JPEG, PNG only) )
Hmmm, I'm thinking as I type here.....
If you had ONE data source table for all your join table occurrences, you might be able to pull this off.
Complainants::__pkPeopleID = Complainant_Case::_fkPeopleID
Cases::__pkCaseID = Complainant_Case::_fkCompCaseID
ComplainingWitnesses::__pkPeopleID = Comp_Wit_Case::_fkPeopleID
Cases::__pkCaseID = Comp_Wit_Case::_fkCompWitCaseID
and the same for Respondants. The key being that you have one join table with different match fields to the Case ID.
This would allow you to set up this relationship
People------<People_Case>--------Cases (but I'd probably use a different occurrence of Cases)
People::__pkPeopleID = People_Case::_fkPeopleID
Cases::_pkCaseID = People_Case::_fkcCombinedCaseID
_fkcCombinedCaseID would be a calculation field with this expression:
List ( _fkCompCaseID ; _fkCompWitCaseID ; _fkResponCaseID ) //for any given record, only one of these fields would have a value.
Then you could place a portal to People_Case on your People Layout and list each case to which a given people record was linked.
PS. If you have FileMaker 12, I'd look at Execute SQL to set up a Union query as a much simpler method for listing all cases to which a given person has been linked.
I do have Filemaker 12
I'll have to find information on the Execute SQL and Union Query
I will see what I can come up with.
First step would be to get ExecuteSQL to query one Join table with a relational "join" to cases so that you see all cases with that _number_id_person value in _kf_number... of the join table.
Once you can get that query to work, you can either set up the UNION query or you can write 4 calls to executeSQL and place them inside a List function like this:
List ( ExecuteSQL (//query for interviewees goes here) ; ExecuteSQL ( //query for Complainant goes here) ....//and so forth )
To see what syntax can be used in ExecuteSQL, open the ODBC JDBC Guide from the FileMaker Help menu and look up the SELECT statement. Seed Code's free SQL Explorer is also very useful in constructing your first queries with correct syntax.
So I'm a little lost do my relationships in the snap shot look correct?
The only snap shot I see is the one before you make any changes and I don't know which method you wish to implement.
Not quite. What I described uses same data source table for all three join table occurrences.
In this latest screen shot, records from both the Complainents and Respondants are matching to the same field in the join table and I can tell that you have three different tables being used as join tables instead of just one.
I hadn't considered that. It might or might not work. I think it would cause issues for your existing design--especially and calculation fields in People that refer to fields in the join table as well as on some of your existing layouts. But I can't categorically say that it wouldn't work.
Here's what I had in mind:
In Manage | Database | relationships, make a new table occurrence of the Join table by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Do this a second time and you have three occurrences of the same data source table.
You may find this tutorial on table occurrences helpful: Tutorial: What are Table Occurrences?