Jump to content
Sign in to follow this  
rdanders

Layers of Abstraction - Help me make FM useful

Recommended Posts

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.

SYSTEMS

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:

HUNTINGSPOTS

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,

Richard

Share this post


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.

Share this post


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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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