Relationship Problem - TV Pitch Database

[videotom]

I'm using FMP V7 on a Macintosh. I have this problem I can't seem to find a good solution for.

I've developed this database that tracks the television pitches my company has made to TV networks. The database contains two main tables: Ideas and Networks. In the ideas table, we track the specifics of each idea including the networks we have pitched each particular idea to. In the Network table, we track the specifics of each of the target networks we pitch to like demographics, etc.

All works fine except when I try to get the two tables to relate together in the following way:

In one of the layouts for the Network table, I want to see a listing of all the projects we pitched to that particular network, and their status. I used a portal with repeating fields for this – which I think was a mistake. It’s not functioning properly. It seems to show the correct data, but other features are problematic. I’ve tried to think of a way to handle this relationally, but I can’t seem to figure out how to display a list of all the related records from the idea table (which includes the network name), since the main key field relationship is with the network name field itself. Visually, here is a simplified representation of what I’m trying to do:

NETWORK TABLE

Network: The Discovery Channel

Ideas pitched to this network and Status [a portal?]

Show Idea 1 – Passed

Show Idea 2 – Liked a lot – need to follow-up

Show idea 3 - Sold

Likewise, in the idea table, I want to display a listing of all the networks we pitched that particular idea to…

IDEA TABLE

Name of idea: Show idea 1

Networks Pitched to and Status [a portal?]

Discovery - Passed

Fine Living – Interested – need to follow-up with them

Court TV – Considering

--

I’m sure that there’s a simple solution out there. Any help would be appreciated.

Tom

[comment]

You need 3 tables: Ideas, Networks, Pitches. The Pitches table is a "join table" - it comes between the the other two tables, and it records each individual pitch. The relationships should look like this:

Ideas::IdeaID = Pitches::IdeaID

Pitches::NetworkID = Networks::NetworkID

(IdeaID is a serial auto-entered number in Ideas; NetworkID is a serial auto-entered number in Networks).

[videotom]

Thanks for the tip. I understand where you're coming from with it. However, I have one follow-up question...

One of my UI requirements is to enter the networks that we pitched a particular idea to in the idea layout. I'm assuming I can do a portal to cover this (porting to the Pitches table you recommended). But we often pitch a particular idea to more then one network. If I want to avoid using erwpeating fields, how do I allow for the entry of more then one network in this portal?

Thanks,

Tom

[comment]

A portal from Ideas to Pitches will show ALL pitches made for the currently viewed idea, to any network. Add fields from Networks into the portal to show information about the network (the Pitches table only holds the NetworkID).

[videotom]

Sorry if I'm being dense with this. I'm an on-again, off-again FMP user.

Understanding that the portal will show all the pitches to a particular network, I actually want to ENTER specific networks that I pitched an idea to on the idea page. Is this possible?

Thanks again,

Tom

[comment]

the portal will show all the pitches to a particular network

Not so. From Ideas, the portal will show ALL pitches of an idea, to ALL networks. From Networks, the portal will show ALL pitches to a network, of ALL ideas. You really need to try this to get the hang of it.

[videotom]

Okay,

I implemented the three tables with the relationships you suggested. As I suspected, it doesn’t function as I wanted it to. The portal will indeed DISPLAY information, but what I really need is a way to ENTER the information in the Idea layout view in first place without having to go to a different layout. Perhaps a portal is not what I need (at least not in the Ideas layout).

The Ideas layout is the most logical place for us to enter-in specifics of pitches (versus the Network layout). My ideal scenario is to be in the Ideas layout, and enter the pitch specifics there (who we pitched it to, what the response was, etc.). Since a portal only shows info already entered (versus allowing you to enter in new info), I don’t think it will work for this.

Would you happen to have any other thoughts on how this might be able to work?

Thanks,

Tom

[comment]

In defining the relationship between Ideas and Pitches, enable the creation of records in Pitches. This will allow you to create a new Pitch by typing into the first empty portal row. Typically, you would start by selecting the network you've pitched to from a value list of networks.

This is the basic method. There are others, but now we are branching from relational structure into user-interface. For example, you could enter the data for a new pitch into global fields, then click a button that runs a script to create a new record in Pitches.

[videotom]

SWEET - It now works as I need it. Thank you!

Tom