Hi All

Maybe I'm high or something here, but I'm stupidly missing something important about many to many relationships, and I need someone to explain it to me. :

I get the concept of a many to many relationship - I think I'm just having problems implementing it in Filemaker.

I've got two tables. There's a primary key in each table, both set as an auto-calculated serial number as a record ID. There is also a join key in each table which is supposed to link them together. The primary key field can't be edited, but the join field can (I of course, have to enter a number in there to join the records.) If the numbers match, then the records are related. I can get a one to many relationship out of this setup, but not a many to many. I've read about using a join table but I just don't get it.

Help?

Edited January 27, 200916 yr by Guest

Hi All

Maybe I'm high or something here, but I'm stupidly missing something important about many to many relationships, and I need someone to explain it to me. :

I get the concept of a many to many relationship - I think I'm just having problems implementing it in Filemaker.

I've got two tables. There's a primary key in each table, both set as an auto-calculated serial number as a record ID. There is also a join key in each table which is supposed to link them together. The primary key field can't be edited, but the join field can (I of course, have to enter a number in there to join the records.) If the numbers match, then the records are related. I can get a one to many relationship out of this setup, but not a many to many. I've read about using a join table but I just don't get it.

Help?

No, you don't get the concept if you're doing it the way you're doing it.

You need a join table; and you need to remove the join fields you have already created.

Imagine cars/owners. One owner may have many cars; one car may have many owners.

Owners:

OwnerID

OwnerName

Cars:

CarID

Year, Make, Model

CarOwners:

CarID

OwnerID

You can also have a many-to-many relationship without a join table: just define your "join key" as a text field, and enter the primary keys of records from the other table as a return-separated list. But the usefulness of such arrangement is quite limited.

Thanks everyone - I figured it out. I ended up figuring out to do it comment's way - it turned out that his method is what was required for my data model. I knew to use a multi-key field to accomplish this, but I had to figure out first to make it a text field in order to accept a carriage return! Sheez - the simplest things sometimes...

Sorry for the lame-brained post, but I had my head into it for far too long yesterday and just couldn't see the forest for the trees. Once I got some sleep, it went much smoother. :

You don't really give us much explanation of what you're trying to do, but I think you would be well advised to note Comment's last sentence: "But the usefulness of such arrangement would be quite limited."

A true join table (as described by Bruce) will give you a whole lot more flexibility down the road, and ultimately will be easier to manage.

For example, with a portal that displays rows from the join table, you can create a drop down to select related records by some sensible part of the related record. Like when you're entering a car in Bruce's example, you could select owners from a list.

You get a lot more reporting power with this structure as well.

You could also add fields to the join table to track information pertinent to the relationship itself (like the date the owner signed on to owning this car; I'm making things up here).

David