Newbie Questions - Self-Joins and Many-Many Relati

[silenuz]

I'm trying to develop a database of all my dvds, and am quite new to filemaker. I have a table for movies, and a table for actors, I have the layouts set etc..., but now what I need is a way to have a record in the actor table show related records from the movie table, as well as the movie table records having a portal with GTRR buttons to the actor table. From reading the forums I understand I need a join table, but how I go about this I have no real idea, other then records in each table must have unique ids , and the join table would be used for browsing etc... Any help, or suggestions would be greatly appreciated, I downloaded Ralphi's seperation model but don't really posess enough knowledge to decipher it. Also I ussed self-joins for year and genre and then a GTRR script to allow clicking on a year or genre and have related records brought up, everything works but what I was wondering when I went to vreate the second self-join I was prompted to create another table in addition to the one created for the first self-join. So what I was wondering is do I need both tables, or can I have both self joins to the same occurance of the first self-join table ?

Thank you in advance.

[Brian C]

You essentially have a many to many thing going here.

Create your join table, call it something that make sense to you.

This table will basically only need to hold 4 fields. ActorID, ActorName (lookup) and MovieID, MovieName (lookup). Using relationships from the join table to the other tables will allow you to auto-enter and store the names once an id is entered in the Join table.

Then you will need to either use a script to create records in the join table with the specific actorid/movieid or you will need to make sure your relationships will allow creation of related records so that you can use a portal to do your data entry directly.

Use a relationship to link to the join table from the Actor table to see what movies an actor has been in.

Use a relationship to link to the join table from the Movie table to see what Actors star in a particular movie.

[silenuz]

Thank you that makes things a bit clearer, I almost have it thanks to your suggestions. I'm still having trouble with entering data in the portal, but I have a clearer idea of what I'm doing, thanks.

[silenuz]

I almost have it, but not quite and was wondering if anyone maybe had more suggestions, cause I ended it set up with auto entered serials for the ids, and the records get created okay but I was wondering, if I'm using the ID as a match field how will the database know not to create another record for the same actor when I input his/her name in the portal as the same name would be input in multiple records ? Thanks in advance for any tips/suggestions anyone might make.

[Brian C]

Your Movie table will have 1 unique record for each movie.

Your Actor table will have 1 unique record for each actor.

Your Join table will have multiple records for each actor and each movie.

Each join record will contain a movie id and an actor id in order to match an actor to a movie.

It is not necessary to store the name of the movie or actor in your join table, but it helps for display purposes in portals. You can use an unstored calculation field instead of a field with the lookup option set.

Assuming the relationship to the actors table is called "Actor" which links ActorID to ActorID

calculation field example:

calculation field name: cActorName

calculation field contents: Actor::ActorName

calculation field storage options = Unstored

Using an unstored calculation will allow you to lookup and display the related data from another table using the ID numbers only and without taking up any additional space in the join table.

Keeping the above in mind, you just need to make sure that when records are created in the join table, they will have id #s that match back to the actor and movie tables. The join table at no time should be creating new unique ID numbers unless it is for the purpose of just identifying a specific record in the join table for reasons beyond the topic of this discussion.

Here is an example of how the data might look when stored in your tables:

Movie Table

----------

MovieID: "45" MovieName: "Minority Report"

MovieID: "107" MovieName: "Last Samuri, The"

Actor Table

----------

ActorID: "247" ActorName: "Cruise, Tom"

Join Table

--------

ActorID: "247" cActorName: "Cruise, Tom" MovieID: "45" cMovieName: "Minority Report"

ActorID: "247" cActorName: "Cruise, Tom" MovieID: "107" cMovieName: "Last Samuri, The"

With your relationships in place you should be able to use portals in your:

Actors table to display movies a selected actor starred in.

Movies table to display actors staring in the selected movie.

Hope Im helping more than confusing here.

[silenuz]

Thank you very much, you have indeed one again helped clear things up, I almost have it working how I want, thank you, your help is much appreciated.

[silenuz]

Just when I think I understand things and that I'm getting somewhere it turns out not to be the case. I'm having trouble with the record creation in the releated tables. What happens is when I eneter the same actor in for different movies I do get multiple entries for that actor in both the join table and the actor table. The portals in the actor table display the proper information in each entry, but as I said I get multiple entries for the same actor if they're in different movies.

I guess I have my database defined wrong? I have The following tables:

Movie related to join by Movie_ID

Actor related to join by Actor_ID

Join has 4 fields Movie_Title Movie_ID Actor_ID and Actor_Name

Movie_Title is looked up from the MOvie Table

Actor_Name is looked up from the Actor table

Anymore help would be greatly appreciated. THanks you in advance.