Careful, the birds are eating your bread crumbs and the witch is getting hungry...
(Sorry, my kids have been watching a Once Upon a Time marthon of last season's episodes and guess which fairy tale just got dramatized...)
I think you have layouts and tables in the same list. Might the names in parenthesis be your table names and the names next to them be layout names?
The basic many to many relationship here is:
Participants::__pkParticipantID = Roll::_fkParticipantID
Events::__pkEventID = Roll::_fkEventID
For an explanation of this notation: Common Forum Relationship and Field Notations Explained
For a demo file of this type of many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
When I started this adventure, it seemed like a nice little stroll in the park but now I'm not so sure I'll find my way out of the forest. : )
Your suggestion seems perfect, I will change my design to match your relationship design. Would I set up layouts for each role (student, teacher, counselor)?
I have insufficient information to answer your question. Separate layouts could be used or a single layout could also be designed that works with all roles. In fact, a layout based on events can use a portal to Roles that works with all possible roles.
Which is a better approach depends on what you need to do with each role and how your workflow will need to function when you create/edit these records.
Thank you for your suggestions. The portal/roles/layout idea is something I will have to consider. Our goal is to import the data from an online application via our website. (Does that make any sense?) Thanks to your input, we are much closer to that reality! Yippie Skip!
You may find it much simpler to publish your database to the web. There are hosting companies that will host your Filemaker database to the web for you for a monthly fee. This would enable users to enter data directly into your database from their web browser--eliminating the need for importing data.
Publishing the database to the web seems like the best plan. Can you suggest a few companies? The only thing that might stand in the way of this plan is time. (And by time, I mean money!!)
Before I received this response from you, I posted "Importing data from excel into 2 FM tables" on the forum. If you have absolutely nothing else to do this weekend AND you have a burning desire to think about FM imports. I would really appreciate your thoughts.
I just wish I had the brain power to match FM power! : )
Best to you...
I have had insufficient personal experience with these companies to make a meaningful recommendation.
Thank you for all your help. We may not be able to import data but it is a noble endeavor! : )
Best to you.