"Therefore when I try to link the People and Deals table to the notes table, I get an error saying that there are more then one relationship path. "
You can have more than one relationship between two tables. I've had more than 100 relationships linking the same two tables until I figured out a more efficient design that didn't need so many. :smileysurprised:
Each "table" in your relationship graph is really a "table occurrence" or "table instance" (Same thing different terms). You can only have one relationship between any two table occurrences. To create a 2nd relationship, make a new table occurrence.
I'm using FMP 10, in that version, open Manage | Database | Relationships and click an existing table occurrence. Click the button that has two green overlapping plus signs to make a copy of this TO. Click and drag to make a new relationship that links your new TO to an existing TO.
Seems like an odd way of doing it but its easy enough to do. Thanks!!!! It seems this way that your relationship view can grow very quickly....
It's a pretty standard method for documenting database design. Microsoft Access has a similar tool and they're both based on something called an "Entity Relationship Diagram" that you can find in most database design text books.
Keep in mind that you need a name for each relationship as well as the relationship itself. Each TO thus gives you the ability to distinguish between relationships in scripts, calculations, layouts, value lists, etc.