More than one relational path between two tables...

[Berlitzer]

Hello. This is my first post, and my first question.

I am building a database for a small company. I have contact information that I need to record for employees, patients, and patients_other (what i call family members, guardians, etc) all of which i specifically want to keep phone and address information for.

Ideally, I was trying to assign a contact_ID to each of these persons. Then the contact_ID was related to a table that contained a Phone_ID and Address_ID. Then, the address_ID related to a table that contained the address for the person. The phone_ID related to the table that contained the phone information.

Needless to say, since the Patient table is related to the Patient_Other table (so i can tell what patients are associated with the Patient_others), Filemaker didn't like it because both of these tables both point to the contact table and I got the message:

"There cannot be more than one relational path between any two tables in the graph. Another occurence of one of the tables must be added to the graph"

Can someone explain why this is happening and what is the best way to get around it?

Thanks.

[Fenton]

You need a separate table occurrence (TO) of the PhoneAddress table to attach via a relationship to Patients_Other.

You cannot create a "circle" of connecting relationship lines; because it makes the path ambiguous. From a given point it could travel either side of the circle (which likely would be different) to the destination TO. Different paths would likely produce different results. Not good, so FileMaker stops you.

Tables which are connected to multiple other tables will often need multiple table occurrences. It's not a problem; they just hang off the end.

[Berlitzer]

Thanks Fenton...

I'm pretty sure I'm following you, I just wasn't familiar with occurences. So if data changes in the occurence, will it change in the table it represents?

Could you direct me to a more elaborate (if necessary) definition of an occurence? Thank you very much.

[Fenton]

A table occurrence (TO) is tied to a specific "base table" (the tables you define in Define Database). The data is IN the base table, so of course it will change, if you have access to it from where you are at the moment. Whether and what you see is dependent on where you're looking from, and what the path is to it.

Layouts are attached to a specific table occurrence. Usually your layouts are attached to the "plain" first table occurrence of your base table; which is created by FileMaker when you create a new table; so you don't really have to think about it (which is probably for the best :-).

You can think of a TO as either the "beginning" or the "end" of a particular relationship path, depending on whether your current operation is beginning there, or reading from or ending there (via Go To Related Record []). Relationships are bi-directional, if the key fields on each side are indexable.

Relationships are still there, as a line from a TO to an adjacent TO. But, because FileMaker 7 lets you go "through" intervening table occurrences, you must specify the table occurrence you are targetting, not the relationship line itself.

Like any game, you must stay on the lines :-] Though are ways to "jump" between unconnected TOs of the same base table; by going to a layout of another unconnected TO of that same base table, within the same window. Windows have their own found sets, so that's another wrinkle. Much flexibility, but more learning curve.

There is a rather large and clunky, but otherwise very good Powerpoint presentation, and example files by Kevin Frank. It is mostly about Table Occurrence Groups (TOGs) and the "anchor-buoy" approach, but it helps clarify how layouts and TOs are connected:

http://www.kevinfrank.com/demo-files-user-group.html

(P.S. If Kevin is reading, I'm sure it was less clunky when you were also talking.)

 

[Berlitzer]

Again, Thank you very much. That information helps a ton. Thanks.

[Mike D.]

Another good source to read is available on the FileMaker website - Migration Foundations and Methodologies. You can download it from this Link

Mike

[ricky_s]

Thanks for the link! Even without the narration, this helped answer a question I've had.

(I'm working on a very complex database, and needed to know if it was better to create one humungous TOG, including all possible relationships; or to create several smaller TOGs. Kevin's presentation definitely sent me in the direction of the latter.)