M-to-M Records Appearing More than Once in Field

[tatihulot]

Could I please request some assistance with my relationships and fields?  I’m not entirely sure if I’m designing them correctly.

I’m creating a movie database.   I’ve created separate tables for actors and actresses. 

I’ve created many-to-many relationships for them, using two separate junction tables.  (I call them Junction tables.  I know some people call them join tables or jump tables.  Please let me know which term is more acceptable in Filemaker Pro.)

The problem is this:  If I enter an actor’s name more than once, it appears more than once in the Actors field. 

For example, in my database, the name Jack Nicholson appears numerous times, each with its own ID.  I thought that’s not supposed to happen.  Am I correct about that?

So…could someone please tell me what I designed wrong, and how to tweak it so an actor/actress name appears only once in the proper field?  Aren’t the junction tables supposed to populate the actors/actresses via the ID’s?

Thank you!  T. Hulot

Actor and Separate Actress Test.fmp12

[comment]

The (main) problem with your file is that the portals on the Movie layout are pointing to the Actors and Actress tables, instead of to their respective join tables. And since you have also enabled the creation of records on the Actor and Actress sides (which you should not have), you are creating a new related record in both the join table and the Actor table whenever you make a new entry in the portal.

Besides that:

26 minutes ago, tatihulot said:

I’ve created separate tables for actors and actresses. 

I don't think that's a good idea. A single table with a field for Gender should be quite sufficient and simplify your structure.

 

26 minutes ago, tatihulot said:

I call them Junction tables.  I know some people call them join tables or jump tables.  Please let me know which term is more acceptable in Filemaker Pro.

IMHO it is best to call a table by the real thing a record represents. In your case it could be Roles or Cast. Being a join table is a type, not a name.

As for the exact terminology, you can use any of these terms: association table, bridge table, cross-reference table, crosswalk, intermediary table, intersection table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, pivot table or transition table (see here ) but not jump table; that term means something else.

 

Edited February 25 by comment

[tatihulot]

Quote

The (main) problem with your file is that the portals on the Movie layout are pointing to the Actors and Actress tables, instead of to their respective join tables. And since you have also enabled the creation of records on the Actor and Actress sides (which you should not have), you are creating a new related record in both the join table and the Actor table whenever you make a new entry in the portal.

I'll do my best to revise this properly.  

18 minutes ago, comment said:

I don't think that's a good idea. A single table with a field for Gender should be quite sufficient and simplify your structure.

I thought about this, but I wasn't quite sure how to design it.  I wanted a set of radio buttons for Male/Female, or Actor/Actress, but I wasn't 100% sure where the field should go.  I thought Male/Female should be an attribute of Actor, so it would have to go in the same table.  So should it go in the join table?

Thank you very much!

 

 

Edited February 25 by tatihulot

[comment]

34 minutes ago, tatihulot said:

I thought Male/Female should be an attribute of Actor, so it would have to go in the same table. 

That is correct. If it were in the join table it would be an attribute of the role (e.g. Mrs. Doubtfire).

 

[tatihulot]

Forgive me, I'm a bit confused.

I'm having trouble grasping two concepts.  Please bear with me.

The first has to do with where I'm putting the GENDER field.  If I put it in the ACTOR table, then I can't put it in the junction table, because it's an attribute of the actor.

But...you said my portals have to point to the junction table, and not the Actor table.  So...how do I get the Gender radio buttons in the portal, if it's not in the junction table?

(By the way, just to clarify something:  I don't want a ROLE field anywhere.  I don't care about the character an actor plays.  I just care about the actor.)

Secondly, I'm not 100% sure I understand about what to enable and what to leave unchecked in the Relationships section.  

Can we focus on these two issues?  I just need a bit more clarification.  

Did I link the fields correctly?  Movie/MoviePK to Junction/MovieFK, and Actor/ActorPK to Junction/ActorFK?  This is why I'm confused about the Gender field.  

[comment]

You can place fields from the Actors table inside the portal (it's best to make them non-enterable so that you can't change them accidentally).

I recommend you study the demo I posted here:
https://fmforums.com/topic/50942-portal-grouping-problem/#findComment-239210

It's old (you will need to convert it to the current format) but it shows the basic principles.

 

[tatihulot]

One quick question before I resume working on this:

Does the Actor field have to be populated with records BEFORE I create a portal?  

 

[tatihulot]

Okay..... I studied the demo file.  I tried my best to duplicate it, translating Contacts/Affiliations/Organization to Movie/Junction/Actor.  

I tried to set up the relationships exactly as you did, only substituting my fields for yours.  I created a Value List based on the Actors table.

I populated the Actor field with a few names, just to see what would happen.

I tried to open a portal, using the Actor table and ActorID/Actor.

Why isn't the portal working?  Which step did I miss or do wrong?

Actor with Drop Down List for ActorID.fmp12

[comment]

At first glance I already notice two mistakes:

• The first field in the "Actor" value list definition should be Actor::ActorPK, not Junction::ActorFK.
• The first field in the portal should be Junction::ActorFK, not Actor::ActorPK.

The logic here is that you select the PK from the Actor table to populate a FK field in the Junction table.

I think that once you fix these it should start working.

 

 

 

[tatihulot]

Quote

I think that once you fix these it should start working.

Yes!  I think I got it.  Would you be willing to take another quick look at it to confirm this?

May I ask just one more question?  The Actor field will sooner or later be populated with a LOT of actors and actresses.  Hundreds, probably.  What does an experienced database designer do when a drop down menu becomes that large?  Do they just deal with it?  

One last thing:  In your opinion, should I have broken the Actor field up into two fields, Actor Last Name and Actor First Name?  Or should I just leave things well enough alone for now?

 

Thank you very much for your assistance and patience.

Actor with Drop Down List for ActorID.fmp12

[comment]

24 minutes ago, tatihulot said:

What does an experienced database designer do when a drop down menu becomes that large?

You have a couple of options. First, even with with a drop-down list, you can type the first letter or two of the name to jump to that area (note also that you can sort the list by the second field, provided the names are unique).

The next level is to select from a card window or a portal, with the option to search.

 

24 minutes ago, tatihulot said:

should I have broken the Actor field up into two fields, Actor Last Name and Actor First Name? 

That depends entirely on what you intend to do with your file. If you don't need to do things like sort the actors by their last name, or find Rex Harrison while excluding Harrison Ford, then you may be fine. But keep in mind that combining the names is much easier than separating them out.

 

Edited February 26 by comment

[tatihulot]

Great!  Thank you so much.  You were a tremendous help.  I appreciate your patience and information.

 

I do have a follow up question, but I think I should start a new thread for it.

Edited February 26 by tatihulot