I have a serious snag in my comprehension of solving many-to-many relationships.
I am developing a solution for myself to keep track of many aspects of performing music. I am stuck at the part where I have CONTACTS and VENUES. I want to be able to call up a venue and have a portal (say) to the contact(s) associated with that venue, and conversely “see” the associated venues on the CONTACTS layout.
I believe I have a many-to-many relationship: any given contact could have more than one venue associated with them (some own two or more performance spaces) and any given venue could have one or more contacts associated with it.
I understand that I need a “join table” that has two foreign keys (_fkContactID and _fkVenueID … does it need its own primary key as well?). I have created these tables and created the relationship inside FileMaker 16. Here is where I am stuck in my understanding of how to use this relationship. Specifically, how do I now approach connecting and displaying this information? Do I need some other field in the ContactVenue join table, as in the “line items” table that is so ubiquitous all the tutorials? If so, what specific field(s), and how do they work?
Ideally I want to be able to create a new venue and “associate” that venue with one or more of my contacts, or create a contact and associate that contact with a specific venue, even if that venue has another contact already associated with it. It would even be nice to create one single layout for input of a new venue and new contacts together, and obviously have that data saved to the respective tables.
It seems so easy, but I have a mental block on this issue, and I feel confident that I can move forward with the database if I can wrap my head around the practical solution to this problem.
I have uploaded the database as it now stands if that is of any help (based on a FileMaker Contacts template), and filled-in a few dummy records. It is a work in progress as I learn. I have just barely begun work on the VENUE portion, so the layout is not yet formatted; I have no other pertinent layouts save for the work I’ve been doing on CONTACTS. I don’t know if it will be useful but I do have a checkbox-list that already includes and entry for “Venue Contact”. On the ContactType field.
Donwload at http://cliffordjames.org/CliffsContacts.fmp12
I indeed thank you very much for your help; the answer may seem easy to you guys, and hopefully it will seem that way to me by next week. I learn fast, but am also incredibly dense at times.