'there cannot be more than one relational path between any two tables in the graph'

[Rothko]

Hello,

I have a many-to-many relationship with two tables ('AAA' and 'BBB') linked by a join table. Both sets of data can be assigned a contact in a fourth table. To do this I had to set up a 'ContactsAAA' and a 'ContactsBBB' table occurrence.

So far this works fine.

Now I would like to be able to have a contacts layout with two portals visible, showing related AAA and BBB records.

Obviously I need to find a workaround to get another table occurrence, on which my contacts layout can be based. If I try to link 'ContactsAAA' and 'ContactsBBB' in the relationships graph, FM tells me 'there cannot be more than one relational path between any two tables in the graph'.

Any ideas? Thanks.

[Fenton]

There are larger questions here. Like are AAA and BBB really supposed to be separate tables? We'll have to assume yes, as we don't know what they are. One wonders why there is a join table between them; but I don't think it has much to do with Contacts.

You would benefit from learning something about the "anchor-buoy" method, creating a separate "table occurrence group" (TOG) for each of the main "entities" (Contacts, AAA, BBB). Here is a rather long PDF explaining what I mean:

http://www.digfm.org/ref/FM7_key_concepts.pdf

Sorry, don't seem to have a link to a shorter one, but there likely is one. Since you posted a PNG not a FileMaker file, I can't rearrange yours to show you. There will be other examples here on fmforums, but likely spread around, as it's a common structure.

What you should have is a TOG, separate from the one you have (of AAA and BBB), with Contacts as the anchor. It would have relationships to new table occurrences (TOs) of AAA and BBB, via a new join table* (that would be 2 TOs of the join table, one between Contacts and AAA, one between Contacts and BBB).

You have specified whether a single contact could be linked to both AAA and BBB. But since AAA or BBB would likely be linked to multiple Contacts, then the join table is required.

*I don't know what or why (or even) the join table you've got is, but I don't think it is the one we want for Contacts.

[Rothko]

I had heard about that before, thanks for reminding me. That was a hard one to chew (well, for me).

There are good reasons to for the many-to-many relationship between AAA and BBB materials: either one can derive from the other, and both will have a supplier assigned. But each material will only have one supplier.

I think I got it working - see attached file.

But I guess it's probably not a good idea to mix up standard RG and anchor-buoy methods in one file (the 4 tables from my question are just part of a larger database), and I'm not starting all over again, so will have to keep this in mind for the next time.

anchor_buoy.fp7.zip

[comment]

are AAA and BBB really supposed to be separate tables?

I would ask the same question. Everything seems to suggest they should be in the same table, set apart by a Type field.

[Fenton]

So what you have now is a single table occurrence group (TOG), Contacts. This is OK, as far as it goes. But it does not give AAA or BBB their own TOG. Their main layouts would be tied to these child table occurrences (TOs) of Contacts. Hence any further relationships to AAA or BBB would also have to be part of this same TOG. That is also OK, for a simple database, or if they are always children (which they do not seem to be). But for anything complex it would get messy in a hurry.

Yes, it is somewhat difficult, tedious to say the least, to retool a large existing database to use the anchor-buoy method. But it can be done. It's mostly a matter of reassigning fields on layouts, and/or* reassigning relationships in scripts. The interface remains much the same, from a user's point of view. It is often possible to at least use a separate TOG from new tables.

* I say 'any/or' because you can break the relational ties to the existing AAA and BBB to Contacts, move each down the chart, as its own anchor of its own TOG. The base fields on existing layouts then remain much the same. Add new child TOs for them to Contacts (you can duplicate multiple TOs at once). Then reassign portals and their fields, and scripts. FileMaker is pretty good about remembering the field when you reassign its relationship. I remember the bad old days, pre-FileMaker 7, when it did not remember. Now that was a PITA.

[Fenton]

Oh yeah, also you'd need to reassign any relationally filtered value lists, if affected. And portal sorts. Which, by the way, are broken in your file, so I couldn't see what to reassign them to.

Oh yeah again. If you do lots of fancy processing in the join table, using relationships to other tables, then you main need more than 1 layout for the join table. This is not a common need however. If it's just simple processing, then you can just 'jump' to the layout in any TOG, do the processing, and return to the original layout.

One of the basic features of Go To Related Record is that it can land on a layout belonging to a TO which is NOT in the current TOG, transferring the found set of the relationship. This allows you to intelligently move between TOGs when necessary, and makes the anchor-buoy method workable.

AAA_BBB_fej.fp7.zip

Edited January 29, 2008 by Guest

[Rothko]

I would ask the same question. Everything seems to suggest they should be in the same table, set apart by a Type field.

Hello,

turns out you were right from the start. It really made sense to merge the two tables, which solved a lot of problems. Thankfully I think I don't need to turn the whole solution into anchor-buoy.

But now I have filtering and navigating problems. I'm posting about that in the 'presentation layer'. Thanks guys!