Accessing data from distantly related files

[carlyle]

I am wondering if there is a way to display data in one file from a file that is 2 or more relationships away. For example, I have 4 files:

PEOPLE(storing contact information about the various film makers, distributors, etc who are associated with each film)

LODGING (storing information about where people are staying while they're here)

FILMS (storing information about film--name, length, description, etc.)

SHOWINGS (storing information about the times, places, and dates that each film will be shown)

They are linked as follows:

LODGINGS=by person ID to=PEOPLE (one to many, since each person may have more than one lodging)

PEOPLE=by person ID to= LINK FILE(which creates a many to many relationship between people and films, since one person may have several films, and one film has several people associated with it--directors, actors, etc)

LINK FILE=by Film ID to=FILMS

FILMs=by Film ID to=SHOWINGS (since each film may be shown more than once)

The problem is that I would like to be able to display the information on any given person's films (INCLUDING the information about where and when it will be showing...from the "showings" database) in the PEOPLE database (and preferably in the lodgings database too...)

Is there a way??? Hmmm...at this point I should probably mention that I am using ancient version 4 of FileMaker . I'd love to find a way to do this in version 4, but I'd also be interested in hearing if there are ways to do it if I (gasp) upgrade...

Thanks in advance...

Carlyle

[Brian C]

There is a way to bring data over from a relationship in the way you describe.

This is done by creating a calculation field in each successive database to bring the data from the related table field into the current file.

example:

Calculation Field: = relationship::fieldname

In the next file over you can use the next relationship in line to do the exact same thing for the related data.

Because calculations that store related data are "unstored" calculations you will not be wasting any space.

[carlyle]

Thanks for the response! This works sometimes, but I'm having trouble figuring out the calculation that would bring over information from multiple records.

For example: I have a film that is being shown multiple places, so it has multiple records in the "showings" (table or file depending on which version you're using, storing data about where the film will be shown, what time, etc). If I make a calculation in the FILMS file, it would have to somehow capture the values from all of the related files in the showings database (i.e. the multiple times and multiple locations that the film would be shown). Is there a way to do this? Maybe by transfering the data from the related files into calculated repeating fields? I'd prefer to do this without a script since I want it to update automatically...

Thanks for the help!

[transpower]

Use portals.

[Ender]

Hi Carlyle,

In FM7 this is easy to do simply by creating a portal to the related file. Unfortunately, previous versions do not have this ability to automatically view data from many related hops away.

In order to get the Film (or Showing) file to relate directly to People, you would have to have all of a person's FilmIDs in the People file (and Lodging file, if you need to see Films and Showing from Lodging.) This kind of goes against normalized relational design, but I think you could do it if you leave the current structure in place and have a script build a multi-key of the Film IDs for the purposes of making the interface or report work. This scripted approach would certainly not be as fast as your other portals that are based on existing keys (unless you store every person's multi-key, but then you're having to update those every time you add or remove a Link.)

The idea with the mutli-key is to have a return separated list of the FilmIDs in a text field in People. Like I said, to build this use a script; probably a loop to go through each related Link record and append FilmID & "" to a text field in People.

Then you just relate the multi-key in People to Film or Showing (since both use FilmID.)

I've attached an ERD of the relationships you described to help illustrate why the other suggestions don't work with your model.

[transpower]

Ender: Thanks for the clarification. Version 4 really is way before my time, although I do have Nolan Hester's book for it (and I checked that portals were available in 4, although there was nothing about files more than a hop away).

[carlyle]

Ender: Thanks so much for this idea! I have never used multi-keys before, but I cruised around this site and found some helpful information, so I think I should be able to figure it out. If my office (a non-profit) had enough money to upgrade and transfer our old old old database up to version 7, it would be so much easier. But I think the change would end up costing more than we'd gain. Thanks again for helping me make this work in our current version!

Carlyle