Please help a noobie with a simple relations problem.

[Res]

I'm sure there is a simple solution to this problem, but I have not been able to figure it out.

I am using FileMaker Pro 8 and I have a database with two tables to keep track of actors and the skits they are performing:

ActorsTable

ActorID#

PhoneNumber

Email

SkitsTable

SkitID#

Venue

Date

Showtime

What I want to do is have a drop down menu on the SkitsTable that will allow me to easily add and change the various Actors for each skit. I also want the ActorsTable to show all the skits the Actor is performing in.

Thanks in advance for any help.

[RalphL]

You haave a many to many relationship and you need 3 tables: Actor, Performance & Skit.

In the Actor table you will have fields : ActorID, First Name, Last Name, Address, City, State, Zip, Phone, email, Gender, etc.

In the Performance Table you will have fields: ActorID, SkitID, Venue, Date, Showtime, etc.

In the Skit table you will have fields: SkitID, Skit Name, etc.

Your relationship between Actor and Performace is ActorID. Your relationship between Skit and Performaace is SkitID.

See topic Complex Relationship Help Needed, Post#180289. I posted a sample file there.

[Res]

Thanks, I thought I would have to use some sort of join table.

I looked over your sample file and it helped, but I am still having a few problems with bringing it all together.

As suggested I made 3 tables:

ActorsTable

ActorID#

FirstName

LastName

PhoneNumber

Email

Age

Gender

SkitsTable

SkitID#

SkitName

Description

Notes

PerformanceTable

ActorID#

SkitID#

Venue

Date

Showtime

Each Skit can have only one Performance (making it a one to one relationship). Each Performance can have multiple Actors, and each Actor can have multiple performances (making it a many to many relationship). I tried pulling event information and actors into the performance table via portals, but things did not work right.

The end result I am trying for is to have one layout that lists SkitName, Venue, Date, Showtime, Description, Notes, and lets people add up to 8 Actors to it via pop up value lists. What am I doing wrong?

[RalphL]

OK that is different from what I thought. Change Performance table by removiing ActorID and add PerformanceID. Make a join table between Actor & Performace using the key fields ActorID & PerformanceID.

A portal in Performace looking at the join file will allow you view the actors.