Jump to content

Layers of Abstraction - Help me make FM useful

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

Recommended Posts

  • Newbies

Greetings FM Gurus,

As I continue my journey in the deep and abiding mysteries of FileMaker, I find my traditional skills and experience challenged at every step. My question has to do with complex relationships and bringing in information from a "3rd layer" relationship into a form.

This example demonstrates how multiple tables can contribute to a central form that is displaying comprehensive information. In other words I need this form to display information from the other tables simply based on related fields. See the DB structures below:

There are 3 tables from which I need information in my 4th Table called HuntingSpots. This database is for a space game, so the universe is broken from larger units into smaller units; in this case SYSTEMS have many SECTORS, SECTORS, in turn have many LOCATIONS. LOCATIONS can be a planet, a navigation point, an asteroid, eceteras. Each table is important on its own merits, and now I have a need to create a table that details information at each LOCATIONS. Examine how I have set up these tables and the subsequent related fields.


system_name (text) SECTORS

system_id ---related----> system_id LOCATIONS

<more fields> sector_id -------------related--> sector_id

sector_name (text) location_id

<more fields> <more fields>

FileMaker has no problem with the 2nd layer relationship, but if you need to extrapolate from a 3rd or even deeper level, how do you do that? For example, consider the the 4th table that pulls information from all 3 tables. Here is it's structure:


location_id (relational link to LOCATIONS table)

Now from the link to LOCATIONS (location_id) I was hoping to pull information from SECTOR via the SECTOR -> LOCATION link, and finally from SYSTEMS, via the SECTOR -> SYSTEM link. I am sure this structure will be familiar, its just getting FileMaker to do what I need that is giving me a bit of a challenge. I hope this is enough to get some suggestions.

All respect,


Link to post
Share on other sites

It is possible to pipeline the data.

Make a calculated field "A keyfield" in file B that is B::A keyfield.

Then in file C do the same: C:??? keyfield. etc.

Pages 128-129 in Using FileMaker Pro 5 (Coulombre/Price) go into more detail.

Link to post
Share on other sites

tfitch has suggested a pipeline (sometimes also called 'tunnelling') method for accessing data from third (and further removed) layers.

You might also consider a matrix model. This is where the relationshipfrom file1 to file2 is used to bring the key field for the relationship from file2 to file3 into an unstored calculation field in file1. This is then used as the key field for a direct link from file1 to file3.

Then in turn, the relationship from file1 to file3 can be used to bring a key field from file3 to file4 back into file1 so that file1 can then also have a direct relationship to file4 and so on...

Link to post
Share on other sites

This topic is 6674 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.