need help getting multiple records to display...

[JD Scott]

sorry for being a scripting lightweight but here goes...

I have 2 databases. one for actors and the other for jobs.

both files have a field called 'job numbers'. I set up a relationship so both files share 'job numbers'. (I hope I did it right)

in the job db I've created a layout that has fields on top and bottom and is set up the way I want it. in the center of this layout I'd like to be able to list all of the actors (from the actor db) which have a matching job number. I cannot figure out how to do this. I created a button that when pressed triggers a script that essentially says

if actors::job code = job code

SetField ["first name", "actors::first name"]

this succesfully puts the actors first name into the first name field on my layout in the job db. unfortunately, I can only get 1 record to appear while I'd like to have all 10 records from the actor db to come up on my layout.

does this make any sense?

[dbruggmann]

What you need is a portal: You create them in Layout mode with the button, which shows a

[DanBrill]

I think what you really need is a 3rd table since what you have is a many-to-many relationship: One actor can have lots of jobs, and one job can have lots of actors.

In the new table (that is, database) called ActorJobs.fp5, I'd have a field called Actor, a Field called Job. Each record will be a unique combination of actors and jobs. You'll never actually have to work with data in this file. It just serves as a bridge between the other two. You might want to add an additional field here called Role.

In your Actors db, relate the Actor to the Actor in ActorJobs. In your Jobs db, relate Job to Job in ActorJob. Set the relationships to allow for the creation of related records.

In Actors, create a portal to ActorJobs, and add the related fields ActorJobs::Job and ActorJob::Role. Say you are on the record for Harrison Ford. In the 1st portal row you can select the job Star Wars and indicate his Role, Han Solo. (I was going for something classier here, but my Richard Burton example got too confusing when both the Job and the Role were called 'Hamlet.' So I geeked out and went for the Star Wars example.)

In the Jobs database, create a portal to ActorJobs. Add the related fields ActorJobs::Actor and ActorJobs::Role. Now when you are on the record for Star Wars the first portal row will show actor Harrison Ford in Role Han Solo. From here you can add another row with Actor Mark Hammel in Role Luke Skywalker. Be aware that this won't automatically create a record for Mark Hammel in the actors db. You'll have to set this up manually or by a script. But once that's done, Star Wars, Luke Skywalker will show up on the Mark Hammel record.

Hope this helps,

Dan

[JD Scott]

thanks for the advice guys. now I shall tackle it. still confused though. I may post again. :-)

[JD Scott]

I'm getting there...couple of questions:

do I need 2 fields in the actors db that represent the actors name? a regular name field and an ActorJobs::name field?

i'd love to actually show someone my databases. because I'm so close but not quite there.

by the way - this forum is great. so glad I found it.

Thanks again,

Jaime

[John Caballero]

Dan's suggestion is right on the money. A movie will typically have many actors, and an actor will typically have many movies. You need the 3rd table to show combinations of actors and movies.

Rather than use the actor's name as the join field from Actors to ActorJobs, create an ActorID field in your Actors db and use that ID as the join field. So in the ActorJobs db, you'd have an ActorID field and a JobNbr field. You'd create a relationship in Actors that links to ActorJob based on the ActorID field and a relationship in Jobs that links to ActorJob based on the JobNbr field.

Let's say that in your Actors db, Harrison Ford's ActorID is 100.

Let's say in your Jobs db, Star Wars has JobNbr 999, The Hunt For Red October is 1051, Witness is 7234 and Indiana Jones & The Last Crusade is 44.

Your ActorJobs db will contain lots of records, but somewhere in there you're going to have a series of records that look like this:

ActorID_____JobNbr

100_________999

100_________1051

100_________7234

100_________44

(Ignore the underscore character...that's just for spacing.)

Conversely, you'll have a series of records where the ActorID is different, but the JobNbr is the same. For example, JobNbr 999 (Star Wars) will have the ActorID for Harrison Ford, along with ActorIDs for Mark Hammil, Carrie Fisher, etc...so using the above example, the ActorID would change with each record while the JobNbr would be the same.

In your Actor db you'll have a portal that will show all the movies the currently viewed actor has been in. In your Jobs db, you'll have a portal that shows all the actors who have been in that movie. Both databases will get that information from the ActorJobs db.

What you need to keep in mind is that whenever you create a new entry in your ActorJob db, you'll need to specify content for both ID fields (ActorID and & JobNbr). As Dan said, there are a few ways that can be done.

[DanBrill]

Hi John,

Thanks for elaborating. I was giving a shorthand theoretical answer, and in the process could have sent our friend down a difficult road. In the real world you'd want to make sure every record has a unique ID of some sort. (Some DB systems call this this the 'key'). The actual field value is almost never a good idea since names aren't unique. (Although we may have stumbled on the one case where this would hold: the Screen Actors Guild requires all of its members to have unique names!)

Anyway, serial numbers are a great way to do this (though next time I start a DB my key will = right("000000" & serial,6).) Seriously, this makes it much easier on the eyes to look down a list of ID numbers, esp. when what you are really looking at is the name next to it displayed using the 'also show value from' option.

As I was mulling this over during a bout of insomnia, I got to thinking that this kind of db could get complex fast. You could have an yet another db just for Roles, for example (a many to many to many relationship?), and you could see all of the actors who played the role of Hamlet in any number of Hamlet productions in the Jobs database.

This database might become the ultimate Kevin Bacon finder!

Cheers,

Dan