Many-to-Many: Basic questions.

[Snow-i]

First time poster here - I've been building a database for my father's small business - it isn't easy trying to figure everything out on your own. I've finally given up and need some help.

For simplicity sake, I have two tables that need a many to many relationship. Customers and Interest Codes. I've created a join table between them which I think is setup correctly but I'm not quite sure how to properly setup the a portal in my customers layout to show the interest codes attached to that record. I'd like the portal fields to use a popup menu to have the Interest codes as selectable, and I've setup a value list but I'm not sure if I have the right fields selected within it.

So far I have for relationship:

Customers-CustomerID = Jointable-CustomerID

Jointable-InterestID = Interest-InterestID

Any help with specifics for this would be greatly appreciated. Its filemaker 11 pro for OS X. I can provide a screen shot if that helps.

Thanks!

[bcooney]

Welcome to the forum.

Good job on the join table! Now, the portal on the Customers form will be based on a TO of the join table. Format the InterestID field to be a popup menu that uses a value list of all Interest codes (ID and Name), showing just the second value.

[Snow-i]

Thank you!!!

It seems to be working with my test interestcodes, and I haven't been able to put any effort into formatting, so we'll see how it all shakes out. The best feeling is getting it to work after several hours and several methods of trying :-). I'm still trying to completely understand whats happening under the hood - i think I get the idea of the join table (hold info for each of the one - many relationships). I appreciate the warm welcome, and may need further help in this thread. :-)

Cheers!

[bcooney]

Good for you. Join tables simply capture the combinations and resolve the many-to-many. Often, they also contain fields that are pertinent to that intersection (a booking record joining a Room and CustID may have a DateStart and DateEnd, for example).

Don't waste time and stress out trying to solve something that probably has been done. We're here to help.