Relationship model

[KurtW]

Hi all:

I am trying to move into more serious relationship design in FM7 and am, once again, having a bit of confusion adapting my Access/SQL Server knowledge.

Using the example of a film production database: I have tables for Talent, Scenes, Locations, Film Info and Crew. Normally I would join all this data in a simple junction table with field names like:

junctionID, MovieID, TalentID, CrewID - like that. This table would obviously hold the relationships between what crew and talent worked in what scene of which movie.

All fine and well, but how does FM handle that? I can actually create this exact table I mention, but then how do I do things like enter three different actors into one scene? How do I pull a portal to view all scenes that this one actor is in?

I can do this with relative ease in Access/SQL Server, but am getting very confused here. Any starter advice is greatly appreciated.

Thanks

Kurt

[Fenton]

I have no experience with Access. But I think you'll find the FileMaker 7 is somewhat similar. You create join tables between the primary files, and data is accessible. I don't think it would be only one join table. You could build the join tables as needed. FileMaker is pretty forgiving about this.

Would you not have Movies and Talent, with a join table between, so you knew who was generally "in" the movie, before worrying about which exact scene they were in? How about crew?

In the example you mention, 3 actors in a scene. This would go in a join table, between Talent and Scenes, "SceneItems" (for lack of a better name). You could enter this data from either direction; either choose an actor for a particular scene, in a Portal to the join file while in Scenes; or choose a scene for the current actor, while in a portal in Talent, looking at the join file from the other side.

Each of these choices could be first "filtered" with a related value list, after first choosing some other field first (such as Movie), to narrow down the list. Or not.

The 2nd portal mentioned above would automatically show all scenes for the current actor. You could also Go To Related Record to see them in list view in the join file (if there are really a lot).

This SceneItems could hold a lot of other entities as well. But it would only be for things which were assigned on a scene-by-scene level.

[Computer Geek]

Kurt -- As someone who has had struggled through the pain of access before, you will love FileMaker and the relationship model. The FileMaker website has a white paper on it which really helped me out an awful lot. I would recommed it, in addition to Fenton's post.

[Fenton]

You can also create the tables and "key" fields you need (and name type fields, just the basics). Then try and create the relationships. Put a little sample data in the fields. If you run into trouble you could zip the file and post it here, attached with the "Choose File" button, in 2nd screen (if you have Preview Reply checked). With relationships it's often easier to see than to explain in words.

[KurtW]

Thanks gentlemen:

Yeah, the example I gave was a bit basic, but I am just tryimng to wrap my head around the FM relationship model. I am going to take your advive and read up on portals (amd how they will facilitate the relationship). I have DL'd the FM white paper and will read it ASAP.

Thanks

Kurt

[KurtW]

Fenton:

I read up a bit (not enough yet, but a start) on FM relationships and realize I have been way overthinking it. I see now that you can create join tables and allow FM to intuitively follow the paths after you declare the 1st relationship.

But (and there

[Fenton]

I see you're jumping in with both feet :-) It appears that you've got the paths pretty much correct. All people are in Talent; CastCrew is like a join file (but joined to what? Movies? That's what makes a group of people a "cast," right, that they're working on the same movie?).

I think the problem is that you're really wanting to create a new record in Scenes, at the end of the line, while reaching back up the line to see relevant data to choose from. You are not wanting to create a new record in Talent from Scenes; which is what it sounds like you're trying to do. You don't generally create new records "up" the path; Talent being at the top level. You do however choose "from" data up the path. Read on.

Further, Scenes should not be the end of the relational line. A Scene is an entity (thing) of its own. A particular person in a scene is a "line item" of the scene, a distinct entity of its own, so it requires another table(s). I say table(s) because there are also props, stagehands, etc.. You may want a table for each; each coming directly off Scenes. Or you may keep all the ScenePeople together, tagging which kind of person they are, and put SceneObjects all together in one other table. Up to you. All of it would be visible from Scenes.

So, at the end of the line, you want to create a new record. If the new record is created while you're IN the line items file, then you'd be looking "up" the relationship path for data; assuming that the people or objects have already been entered in a higher table.*

You can use either drop-down lists or portals to choose this "higher" data. Which you use depends on several factors: the number of records to choose from, whether they are alphabetical (generally yes), how much layout space you're willing to use for this (portals use a lot).

In either case, you should use "related" values lists, or "filtered" portals, which are similar, both using a relationship along the path to narrow down the choices to only relevant ones. Your choices have a hierarchal relationship. If you've already assigned a person to a particular movie, in a join file such as CaseCrew, since a Scene is always within a particular movie, then the drop down list of people in Scene line items should be filtered to only those for that movie; possibly only those of a certain type for that movie, after entering the type (actor, makeup, whatever, I don't know :-)

You can reach all kinds of data, as long as the relationship path is valid, which also means that the lines can reach, in your Relationship Graph.

*You could also create the new line items record up in one of the higher tables, in a portal pointing TO the Scene line items table(s), by choosing data from the other tables. In that case you'd want to either have button to create the record, or turn on "allow creation of related records" for the line items table in the Relationship box (little black-outlined box in the graph).

The latter works well, until you get too many records to show comfortably in a portal. The button has the advantage that you do not have to see empty space in the portal, you sort the portal by date/time created descending, and the new record appears at the top.

[KurtW]

Thanks for your detailed reply, Fenton:

>>I see you're jumping in with both feet :-)

Yeah, I

FMzip.zip

[Fenton]

It's good you attached the file. It was not your design so much. It was the old "assigned the fields to the wrong relationship."

In the Join portal in Scenes, you'd put the field "Talent::ScreenName." That will show you the name OK, if the TalentID has been entered correctly in the Join file (you must have done this manually), because FileMaker 7 can read through the Join file back to the Talent file, via the TalentID.

But, because the relationship from Join to Talent does not (and should not) allow creation of related records, nor is the ScreenName even in the Join file, it was not able to create a Join file record in the portal.

The correct field in the portal should be the TalentID in the Join file. It uses a value list with "show also field," then the ScreenName field. Then it can create the portal record.

BTW, there is a bug in the value list; it does not sort by the 2nd field, even if you tell it to. This will hopefully be fixed fairly soon.

Also, while your current 1 join file for all works, it does not allow you to set up such things as a "crew" for movie (other than relating to the Join file, which would show multiple entries for a person, one for each scene. It short circuits any possibility of creating "filtered" related value lists for choices, such as "only show people for this movie."

I began to work on my version of multiple join tables, but got interupted. Perhaps tomorrow :-/

MoviesTest.zip

[KurtW]

Thanks Fenton:

I am at work and can't really look over the answer/attempt to implement probably until late tonite. I will respond asap. Just wanted to say thanks.

Kurt

[KurtW]

Fenton:

>>In the Join portal in Scenes, you'd put the field "Talent::ScreenName." That will show you the name OK, if the TalentID has been entered correctly in the Join file (you must have done this manually),

I did do it manually since it wasn't allowing me to create records through the portal. Did the fact that I manually entered this data screw up the relationship in any way? Why would that be?

>>... because FileMaker 7 can read through the Join file back to the Talent file, via the TalentID.

>> But, because the relationship from Join to Talent does not (and should not) allow creation of related records, ...

Agreed!

>>... nor is the ScreenName even in the Join file, it was not able to create a Join file record in the portal.

Think I am getting lost. I wouldn't want to include the ScreenName in the Join1 table, that would be redundant data if that table already includes the TalentID. So, are you saying that I can't 'look-through' to Talent, pull a portal of Talent that displays ScreenName and have the portal create a record in Join using the appropriate TalentID for that ScreenName?

Or maybe you answered that here:

>>The correct field in the portal should be the TalentID in the Join file. It uses a value list with "show also field," then the ScreenName field. Then it can create the portal record.

I don't have my FM at work, so I can't look over what I did. I *thought* this was what I was doing but obviously not. So, to recap - if I follow you:

I use the TalenID in the portal that exists in Scenes because that is the true Join criteria from Join1 -> Talent. Then I use a popup menu with a value list with "show also field" selected and steer to the ScreenName field. Now, when I choose a ScreenName in the portal, it will actually write the TalentID to the Join1 table, as it should.

In essence, I have created the FM equivalent of an HTML Select box that displays a text name but passes a numeric ID value. (This is exactly what I have been trying to do.) :-)

Thanks

Hopefully I will be able to try this out in the next day or so.

Appreciate it.

Kurt

[Fenton]

I believe that what I was saying was that you can reach through a Table Occurrence to another to display or edit data; but you can't create a record in the join file by typing into such a "twice removed" field. "Allow creation of related records" is useful sometimes, but it has limitations. One of them is that to you have to enter data into the actual target table in order to create a new record in it.

If "allow creation" was turned on for Talent also, you might end up creating a new record in that table; which may be useful in some line item situations, but isn't what you're trying to do.

[KurtW]

So there is no practical way to 'break up' a relationship to allow me to pull data INTO the portal in Scenes to view data from Talent, but have the portal WRITE to Join1 unless that exact data field is mirrored in Join1?

This will require some rethinking...

Thanks

Kurt

[Fenton]

You call "pull" data, if you mean look at or edit it. The Talent Name, for example, is NOT in the join table, yet displays properly in the join file portal, reaching through it to the base Talent table occurrence. But you could not type a "new" name into that particular field, in that particular place, and create a new record. It is the name field in Talent; yes, you could edit it in the join portal, but that would usually not be a good idea.

If you turned on "allow creation" to Talent, it still doesn't work. I imagine because you are asking FileMaker to create 2 keys in what is a many-to-many relationship*, which it cannot unambiguously resolve.

*You can see this in the relationship graph by the "crows feet" at the end of the path's lines.

The TalentID field though is IN the join table. So entering data in it creates a new record in the join table; and the Talent Name is displayed, via the relationship of TalentID to the Talent table.

[The following is (more) incoherent rambling :-]

You could create a record in a table down the relationship path from join, with a relationship on the JoinID (foreign key in new table). Then, after creating the Join table record, you could type into a field from that new table occurrence, in the Join table's portal, to create a record in the new table.

But, despite the fact that it's a one-to-many relationship, the portal would restrict you to one-to-one entries. So you may as well just put that data into a field in Join instead. Unless you had several different Join type tables, and portals to each of which included a field to this same "mini-join" table, which would then collect data from each of the joins. You'd still have to enter data directly into a mini-join's field to actuate the new record, which it makes it unreliable as a central "history," but may have other uses.]

[KurtW]

Fenton:

>>You call "pull" data, if you mean look at or edit it.

Exactly. I meant view the data. Sorry, I am still stuck in the parlance I use most commonly.

>>The Talent Name, for example, is NOT in the join table, yet displays properly in the join file portal, reaching through it to the base Talent table occurrence. But you could not type a "new" name into that particular field, in that particular place, and create a new record. It is the name field in Talent;

That's great - nor would I want to in this instance.

>> yes, you could edit it in the join portal, but that would usually not be a good idea.

Agreed. I wouldn't want to here.

>>The TalentID field though is IN the join table. So entering data in it creates a new record in the join table; and the Talent Name is displayed, via the relationship of TalentID to the Talent table.

Bingo! That's what I'm after.

>>[The following is (more) incoherent rambling :-]

You could create a record in a table down the relationship path from join, with a relationship on the JoinID (foreign key in new table). Then, after creating the Join table record, you could type into a field from that new table occurrence, in the Join table's portal, to create a record in the new table.

Okay, that makes sense. Theoretically you could then cascade this functionality quite deep.

But, despite the fact that it's a one-to-many relationship, the portal would restrict you to one-to-one entries. So you may as well just put that data into a field in Join instead. Unless you had several different Join type tables, and portals to each of which included a field to this same "mini-join" table, which would then collect data from each of the joins. You'd still have to enter data directly into a mini-join's field to actuate the new record, which it makes it unreliable as a central "history," but may have other uses.]

Okay, I think I was close but was missing the nuance. I hope to have time to try this out in aday or two and report on my progress.

Thanks for your time and patience.

Kurt

[Fenton]

>Bingo! That's what I'm after.

I thought that was what my example file did :-]

Since then I've messed about some more. I now have what I think of the "FileMaker way" to set up the table occurrences. It is fairly different. The reason for it is that it can "filter" the drop-down lists, to only relevant possibilities. It assumes that most of the data is hierarchal. It uses the Crew table; Talent however is independent (you enter a new person there, not "tied" to anything yet).

It's a little confusing, with more TOs than the previous minimalist one. It will be worth it when you have hundreds and thousands of entries. I'll post it when I've tested a little more. No need to confuse you, yet :-).

Here's the relationship graph. Concatenated names, "MovieCrews," "CrewTalent," are join files. Hyphenated names are extra TOs, needed to show data (what are by default named TO 2, etc., I prefer descriptive names).

MovieJoins.pdf

[KurtW]

Good lord! I completely missed that you had posted a sample table structure. Man, I am really out of it. Only excuse is that I have been working 'round the clock! THWACK - to my head.

I will DL what you have attached and look it over asap. Thanks again!

Kurt

[Fenton]

Oops! The example I posted before was "incomplete," to put it kindly. It was more or less a mish-mash of your original "join only" method and my later "related values lists" method. But some of the data still had to be entered "manually," in the line items tables; and the graph was kind of messy.

[Example file moved to later post]

This new one is the correct structure (I think :-). It is actually simpler. There is NO direct data entry in the Join table. The data is entered as move down the hierarchy. So you enter the Movie first. Or the Talent; these two are independent. Then, beginning from Movie, you can enter either the Crew(s) or the Scene(s). These are more or less independent also.

In Crew you choose Talent for that particular crew (for that particular movie).

From Scenes you finish the details. You get a filtered value list of crews for that movie, then a filtered value list of people for that crew.

Because all data entry is filtered, none would be available until the Scene ID was populated. There's no fields that you'd just type into (to create a related record). So related records (portal rows) are created with a button.

After data entry in the Join portal in Scenes, the resulting Join record has IDs from each table.

I had an epiphany while fixing that, a method for creating the relationship graph. It is the way that you would do it intuitively, so you may not notice. But when converting something from one data entry method to another you have to notice.

The basic principle is this: In hierarchal data structures the relationship graph mirrors the data entry path.

When this is so, then portals and "filtered" related value lists become natural. Data higher up the chain, however distant, is available for display.

At the bottom of the chain(s) there is a join or line items table(s). Because there can be only 1 path down the chain to the bottom, and it consists of IDs (not names), then often the name of only one of the entities is available. This is because you can't get the name if the path up is many-to-many at any point (which is likely). All that is needed are a few little "tail" table occurrences from the bottom Join to each of the non-attached entity tables, ID to ID.

[KurtW]

Fenton:

>>Oops! The example I posted before was "incomplete," to put it kindly. It was more or less a mish-mash of your original "join only" method and my later "related values lists" method. But some of the data still had to be entered "manually," in the line items tables; and the graph was kind of messy.

Oh, no problem. I understood that to be the case. I certainly didn

[KurtW]

Fenton:

Looks great. A quick question, though: What, exactly, is the purpose of the 'trailing' tables you mentioned, like Movies_Join, at the end of the chain? They look like duplicates of earlier join tables but are obviously important in your scheme based on the 'final' position.

Kurt

[Fenton]

I'm glad you asked that question :-/ The "tail" table occurrences (TO) are only there so you can see the names of things in the Join table, whose data is all IDs. And the reason you'd need the names, other than curiousity, is for reports, which would happen in the Join table; because it is the file that has data from each of the other tables, so any kind of report is possible.

But, upon looking again, I see that the Movies_Join TO is unnecessary (so I removed it). Because the relationship of Movies to Scenes is one-to-many, not many-to-many, the Movie Title is available passing through the Scenes_Movies TO, which is connected via SceneID to Joins, to the Movies TO.

You cannot have a "circular" relationship path. You don't have to worry, because FileMaker will tell you if you try.

Because Scenes_Movies is connected to the Join file, none of the other TO's can be. Because to do so would create a circle. So both Crew and Talent tables need a "tail" TO off of Join in order to show their names there.

[Example file moved to later post.]

[rivet]

When you have a join file is there a clean way to perform a 'go to related record' from one outside file to the other outside file?

[RalphL]

Yes, Use the TO of the other fle.

[KurtW]

Fenton:

Apologies, I haven't yet looked over the very last attachment. My project at work is moving very fast right now.

I don't have FM at work so I am not looking at your solution, but I have another question: In one (or more) of your JOIN tables I noticed related fileds of peripgeral data were brought in, like say the Name of an actor rather then just their ID.

I imagine you did this to allow that peripheral data to show through - but isn't that what you accomplished with the 'tail' tables? Wouldn't you normally try to keep your JOIN tables as lean as possible?

Kurt

[Fenton]

Are you dissing my tables :-? No, there are no "name" data fields in the join files. The name data you see is being read from its original table. There is no redundancy in the names (that I see).

There is some in the IDs however. The MovieID is implied by the SceneID; in fact that is how the movie name is shown in the Join table. But, you would likely want to break and summarize a report by movie, hence it is needed there.

The LocationID was not being used, but I imagine you'd have a use for it. I think that the Location name field in Scenes actually belongs in a Locations table; you'll likely have several scenes in the same location.

MovieJoins.zip

[KurtW]

No dissing - I swear

I must be remembering seeing related fields. That makes sense.

Thanks - I can't wait to have some time to look this over in more detail.

Kurt

[bobearl]

Hi Kurt,

I have a very silly question, but it would help me tremendously: where can I find the relationships White Paper. I've looked all over the FM web site and can't find it. Thanks very much!