Go to related record in first database from third

[Ace]

Hello,

I have 3 databases, which have the following structure (image of the structure is attached as JPG to this post):

Database 1:

ID (primary key)

Data (some data)

Database 2:

ID (primary key)

Database1 (foreign key to Database 1)

Data (some data)

Database 3:

ID (primary key)

Database 2 (foreign key to Database 2)

Data (some data)

As you can see, I have a

one-to-many relationship between Database 1 and Database 2

one-to-many relationship between Database 2 and Database 3

Because of this structure, I should be able to go to the related record in Database 1 from Database 3.

How do I view the data from Database 1 in a layout in Database 3?

I know I could create a calculation field i Database 2 named "Database1Data" and then, in Database 3, display the related field Database 2::Database1Data, but it annoys me, that I have to create a calculation field every time.

If someone here is hardcore at SQL, I've tried written what I mean in SQL:

select Database1.Data

from Database1,Database2,Database3

where

Database1.nId = Database2.Database1 and

Database2.nId = Database3.Database2 and

Database3.nId = 1 (eg.)

As you can see, it's fairly easy to do with SQL and there is possible in most DBMS' ... why can't you do this in FM?

Regards,

Mads

[Kurt Knippel]

What you need in DB3 is a key to DB1. I would suggest the following:

rDatabase1 = DB2 by Database2::Database1

This will show the Database1 field that is in the related DB2 record and then you can setup a relationship to DB1 by the new rDatabase1 field.

By making it related it will always be up to date based upon what is in the related DB2 record.

[Ace]

Hi Kurt

Thank you for answering my question.

As far as I can see, you still have to add a field, right? This annoys me, because I'm not actually storing any extra data - I'm just taking advantage af a relational database. And what if I have to access 10 different databases this way? That would be 10 unnecessary fields ...

I tried sharing my 3 DB's with the ODBC-driver and executed my SQL-statement in an external program, and it worked fine! Is it really true, that you have to use an external program to take full advantage of the relational features of a FileMaker database?

I know I could just settle with the secondbest solution, but I'm going for high speed and flexibility with my databases.

Regards,

Mads