Many-To-Many Relationship Database Design Question
I'm fairly new to FM but am familiar with most basic types of database relationships. Many-To-Many is not one I use very often so I probably need a reality check. I have three tables called "clubs", "teams", and "players". The first relationship between clubs and teams is your garden variety one-to-many (one club can have many teams). The second relationship is my quandry. Each team could, of course have many players but its also true (at least in youth soccer) that each player could belong to one or more teams which by definition I believe is a many-to-many relationship.
I understand I need a join table that contains the foreign keys of both "teams" and "players" and I believe I have that set-up correctly. At the moment I'm trying to create a "teams" layout that will allow me to enter player information into a portal. The portal contains the PK of the players table as a drop down (displaying name) and a few other fields such as age, DOB etc. the drop down works just dandy but I guess I was expecting the related information to autofill into the portal from the players table given the current relationships. All I can get it to do is put the PK value in t hat first field. I've spent a great deal of time analyzing my logic and design and I must be missing something obvious. As I said I'm new to FM so perhaps there is a subtle setting I'm forgetting. At any rate I was hoping to for someone to review my logic and design to see if I'm crazy or not. I've attached an image of my relationship settings to assist. Any advice would be appreciated
thanks in advance