Relationally Confused...

[nycpost]

I have a database that tracks cast and crew members on a TV show. It contains the following tables:

Table 1 - cast and crew

Table 2 - episode numbers

Table 3 - every scene in every episode

I've made a value list of all the cast members from Table 1 and turned that into a checkbox field on a Table 3 layout. I then use this to check off which actors appear in each scene. This works great.

My production would now like a way to list all the cast and crew members that participated in each episode. For cast, this is based on their appearance in a scene (hence why I created the checkbox value list above). For crew, I need the ability to select which episodes they've worked on (most crew work on every episode, but some crew work only on certain episodes).

I've made a relationship linking Cast Members in Table 1 to Cast Members Used in Table 3. I then have another relationship linking Episode # in Table 2 to Episode # in Table 3. So, Table 3 is acting like a join table. This allows me to put a portal on a Table 1 layout and display the episodes the actors appear in by pulling from fields in Table 2.

This works pretty well for the cast members, except that the episode numbers are in separate records in the portal. I'd really like to get them all into one record. Also, what about crew members? I still need the ability to select which episodes they worked on and a portal just won't do it. I could create a second field for crew, but that feels kind of sloppy.

Is there any way I can take the episodes that show up in the portal and build them into a carriage return delimited list which I can then use to activate an auto-enter checkbox field? Is there any way to do that without a script? I'm pretty sure I could figure out the scripting, but I'm wondering if there is a more elegant solution. I just feel like I'm over looking an easy solution to this whole thing.

Ideally, what I'd like to end up with is a checkbox field on a Table 1 layout, and if the person is a cast member, have it show me which episodes they appear in (based on the checkbox entries I've made on Table 3), and if the person is a crew member, have it select every episode by default so I can uncheck those that don't apply.

Does anyone have any ideas as to the best way to achieve this?

Thanks,

Mike

[comment]

Table 3 is acting like a join table.

Not really. You would have much more reporting flexibility with a real join table between Cast/Crew and Episodes (one record for each participation).

In any case, I am not sure what exactly you are trying to accomplish now. These two sentences seem to contradict each other:

My production would now like a way to list all the cast and crew members that participated in each episode.

...

This works pretty well for the cast members, except that the episode numbers are in separate records in the portal.

One would think that the portal should be on a layout of Episodes, showing the cast that participated in the currently viewed episode?

[nycpost]

Thanks, Comment. Sorry, didn't mean to be contradictory. What I meant was I feel like I've only found a partial solution in the way it's currently implemented, using a portal to display all related episode numbers for cast, like so:

TABLE 1--------------TABLE 3----------------TABLE 2

cast&crew------------scenes----------------episodes

I'm using Table 2 to filter and show only those related episodes. This works for the cast, but it doesn't work for the crew. I'd like to see if there's a more universal solution, where the participation for cast and crew is tracked in one field. Currently, I'd have the portal tracking the participation of cast and then another field, perhaps a checkbox field, tracking the participation of crew. I'd love to find a way to get their participation tracked all in one field.

Your idea to place the portal on a layout of Episodes (Table 2) is interesting. I hadn't thought of that. When I think about the report I need to make, it seems like it should be a layout from the Table 1. I think what's tripping me up is the context of all of this.

[comment]

I am still hazy about this point: what exactly IS the report you need to make?

[nycpost]

Ah, sorry. It's a report of all the cast and crew who worked on a given episode. 

My thinking was that "episode" would be a field on a Table 1 layout. You'd do a find for the episode you wanted to view and bam, there's your list of cast and crew. The problem I'm having is finding a way to get the episode values into that field. With cast, I'm currently only able to get a list of related records in a portal. I'd like to set up a checkbox field for episodes to check off for the crew, but I can't figure out a way to get the cast episode values into the same field.

[comment]

It's a report of all the cast and crew who worked on a given episode.

Well, if it's only one selected episode at a time, then it would make more sense to show this in a portal to Crew&Cast on a layout of Episodes, I think.

---

P.S. Please use table names rather than numbers - it's hard to follow that way.

[nycpost]

Thanks, Comment. Yes, currently they're only asking for one episode at a time, but I foresee them asking for multiples in the future. What if it was for more than one episode?

[comment]

Actually, if you print the layout with the portal you CAN show as many episodes as you want. Though I should add that this is still a hack - you'd be much better off with a join table.

[nycpost]

OK. That's kind of what I was thinking. I'll get going on a real join table. Thanks again, Comment!