Jump to content

Accessing data from distantly related files


This topic is 6627 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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)


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 crazy.gif. 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...


Link to comment
Share on other sites

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.


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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 & "par.gif" 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.


Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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!


Link to comment
Share on other sites

This topic is 6627 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.