Problem linking two tables to the same field

[chizuoka]

Hello I have a simple issue that i can't get into grasps about, which is linking to tables to the same field

With reference to the db relationships below

I am trying to link "Contacts:Contact ID Matching" to "Class Taught:Contact ID Matching" but Filemaker wont allow me as it says i cant have 2 rships to the same table. Thats all fine... but, if i have a relationship as above, I create a Classes Taught Portal and a Classes Joined Portal in a Classes layout to display who is teaching the class and who is learning in the class it will display fine.

However if i try to have a Classes Taught Portal in a Contacts Layout it wont be able to display the relevant data, which is understandable because contact id matching field in contacts is not connected to contact id matching field in classes taught.

How then may i do the above may i ask? how can i also link the contacts table to the classes taught table? I also need to display classes joined data in a portal in the contacts layout.

Thanks so much for your help!

[Ron Cates]

Seems like you may need to learn about Table Occurances. You can have multiple occurances of your tables in the graph in order to establish different relationships. In the graph you would make a copy of your Classes Taught table ( which it's self is a Table Occurance, just the first one ). You can call it Classes Taught 2. Then create a relationship from Contacts to Classes Taught 2 matched on Class Taught 2:Contact ID Matching .

Then on your layout you would use a portal pointing to Class Taught 2

Note: you can name the Table Occurances anything you like. The underlying Table will still be the same.

Every instance you see of a Table in the Graph is merely an occurance of the underlying table. You can show any number of Occurances in the graph for a single table. There will still only be one actual table of data that these Occurances represent.

[chizuoka]

That worked wonderfully, however, i have tried to understand why would FM want to do that? Why not like mysql where i can have a foreign key link anywhere at all instead of creating a ghost of a table?

[Ron Cates]

Guess there's no way I could answer why. It's how it's done in FileMaker since the introduction of the relationship graph back in FM 7 I think. As for why not like SQL, although I am not that familiar with SQL myself I have learned from these boards that FilMaker is not like SQL and it is advised that you not think of it as such. Otherwise you will find yourself struggling to come to terms with how things are done in FM. In other words, don't expect FileMaker to behave like SQL because it is not SQL, it's FileMaker :)

[mr_vodka]

The relationship graph is not an ERD. Furthermore, with FileMaker simply creating a relationship does not create an natural inner join filter. Up until FM12 you could not filter relationships on the fly (WHERE clause ) unless using specifically on predetermined key fields with utility fields. You could filter on the presentation layout since FMP11 using filtered portals but again not in the relationship itself.

I would check out the ExecuteSQL calc as you may find it more comfortable.

That being said, to better understand FileMaker, I would highly suggest reading the whitepaper that I referenced here. It should help you understand some of the key concepts of FileMaker and its relationship graph.

http://fmforums.com/forum/topic/83586-understanding-relationships/page__p__387314__hl__whitepaper#entry387314

[Ron Cates]

I guess with my limited knowlege of SQL I would surmise that the why comes from the defferent approaches to relationships. With FileMaker you define relationships and they exist permanently. Whereas, and I may be completely wrong here, but doesn't SQL decaliar the relationship as it is being used? So once it's been used or called for the purpose at the moment the relationship doesn't actually continue to exist? If so it would seem that it is just two different methods for managing relationships that behave differently. It may be that logically the same thing can't be done with SQL but since the relationships dissapate from use to use they are not there to interfear with each other the rest of the time. Again, this is based on very limited experience with SQL so I would love to know if I am close :)

[chizuoka]

Thanks so much for your explanations! Will check out your references... Ron... cant say if you are close about sql.. as im would not dare to call myself an sql expert about its very basic concepts... haha. Thanks so much though