Portal: display fields from child and grandchild?

[ohthetrees]

Hello all, I've searched all over the web for a solution, and I think I must be using the wrong search terms, so apologies if it is a simple solution.

I'm in oceanography and setting up a database to hold information about all our samples. My real database is quite complicated, but I think I've boiled it down to a super simplified seteup that illustrates my problem.

Hypothetical setup:

I have three tables. Lets say they are:

1)Expedition

2)Stations (aka location)

3)Samples

Expedition might hold info like the dates of the expedition, staff, name of the ship, start and end locations, etc. Each Expedition might have many Stations.

Stations might hold info like the Latitude and Longitude of the station, the weather conditions, etc. Each Station might have many Samples.

Samples might hold information like the depth the sample is from, temperature, salinity, and the kind of equipment used to capture the sample.

So a pretty simple parent-child-grandchild, where each relationship is one-to-many.

What I'd like to do is have a portal in the expedition layout (the parent) that displays a list of all samples (the grandchildren). That is easy, I create a portal, go to the portal setup panel, and select "Show records from: Samples". Then I select a few fields for the portal. That works fine, as long as all the fields are from the "Samples" table. But If I also put in a couple of fields from the "Stations" table, those fields do not display as I'd hoped.

For example, if I have records named:

Station1

SampleA

SampleB

Station2

SampleC

SampleD

Where SampleA and B belong to Station1 and SampleC and D belong to Station2, I want the portal to display something like:

Station1 SampleA

Station1 SampleB

Station2 SampleC

Station2 SampleD

but what it in fact displays is:

Station1 SampleA

Station1 SampleB

Station1 SampleC

Station1 SampleD

If anyone has any advice, it would be very appreciated.

[aldipalo]

I believe the portal would be the child table with fields from the grandchild table.

You would sort the portal by Station and sample.

hth

Edited December 18, 2008 by Guest

[ohthetrees]

If I do that, I get:

Station1 SampleA

Station2 SampleC

In other words, only the first grandchild from each child is displayed.

[comment]

You need another occurrence of Stations, to be placed BEHIND Samples (from the POV of Expeditions):

Expeditions -< Stations -< Samples >- Stations 2

Place the fields from Stations 2 in your portal to Samples.

[ohthetrees]

Perfect, thank you so much. Somehow this isn't intuitive behavior me. It seems like there should be an option in portals to always start traversing tables from the portal table, rather than from the layout table. Anyway, thanks again.

[comment]

It wasn't intuitive for me at first either, but now I shudder at your suggestion: relationships ALWAYS start from the table that requests the related records. It couldn't work any other way.

[ohthetrees]

I'll take your word for it :