More than one relational path error.

[fmow]

My database consists of the tables: Projects (_pk_project_id), Employees (_pk_Employee_id), Clients (_pk_client_id) Clients_Individual (_pk_person_serial) and Protocol (_pk_serial) (where each document that enters or exits the office is tabulated with a serial no.) I have joint table for Projects and Employees (many to many), and also clients has a relationship, is the father of, protocol. Now when I try to relate projects, _pk_project_id to _fk_project_id in the Protocol table, I get the "there cannot be more than one relational path between any two…" error. I suppose this is because Protocol is already related to Projects via Clients. 

 

And then it prompts me for a new instance of Protocol instead. Should I just leave it as it is and then just populate _fk_project_id in Protocol without having them related directly via a one to many relationship? (The same error prompt appears when I also try to relate _fk_Employee_id and _fk_person_serial from the  Employes and Clients_People Clients_People) 

[eos]

If you want to connect the same tables in different ways (as is the case here), then you have to create separate TOs, which is absolutely the point of the Relationship Graph. Realize that those little boxes in the RG are not the tables themselves, but merely instances of the tables, and you can (for all practical purposes) create an unlimited number of instances, depending on your requirements, and connect them using different predicates. Which data you see via a given relationship depends on where you're starting to look (the so-called context) and where you're looking into. Both starting-point and end-point define a path, and this path must be unique in the RG (as must be the TO names); otherwise FM wouldn't know which path to follow.

 

Here's a quick & dirty example. Note that you can add as many TOs as you wish and connect them in new, (hopefully) meaningful ways.

 

Also note that in your case, to see the protocols related to a project, you wouldn't even need a new TO or connection – merely look from Projects 'through' Clients into Protocols, and there they are (assuming that a project has only 1 client). You can even use the PROJECT interface to create new protocols, as you will see, and they will automatically 'belong' to the correct client.

Instances_eos.fmp12.zip

[fmow]

Hi Eos, thanks for what was a very lucid explanation. Thanks also for the suggestions for the technical paper (I've just signed up to the fm dev. program for that) and for the example. Many thanks.

 

As far as I can tell, if I leave the database as is, that is relating Protocol to Projects via Clients and Protocol to Clients_Individuals via Clients again I should have no problems with the integrity of the schema. Or should I just create 1 (or 2) more table occurrences and link Projects and  Clients_Individual with the Protocol table. I can't figure out what the difference in each schema is, the latter is more elaborate, but that's as much as I can gather.

 

In my novice thinking I supposed that if a certain table has a number of fields that occur in other tables, they should all be directly related with primary and foreign keys to the original tables. Could you please validate for me that this is not the case? That it suffices if there is an indirect relationship between the tables?

[eos]

You don't have to connect those tables again to state the fact that they're related – that's already implicit by the other connections. Here another version of the example database - not incorporating Clients__Individuals, but supposing that a Project can have multiple clients (which for this purpose comes to the same thing). You see that there TOs simply used as interface devices, and how a conditional value lists looks up data from several hops away.

 

Make sure to read the Technical Brief; it's a bit whimsical at times (and I have to know – I translated it into German   ), but very insightful. If you can get your hands on a copy of The FileMaker Pro 10 Bible by the same author), read that, too; it's an excellent book, and the essential principles for database design he describes and advocates are very much independent of the current version of FileMaker Pro (barring truly earth-shattering changes  ) .

Instances_eos_2.fmp12.zip