Help with Portal Concepts
FM Pro 11, Windows XP
I know what I want from a portal, but can't figure out how to get there. I'm designing an addition to an existing system, so I have some flexibility in how these tables are structured but can't change everything.
Individual Metadata - Table of metadata on an individual (fish) (sex, weight, etc) - fixed
Accessions (genetic samples) - groups of *collected* fish (collection location, project, species, etc) -fixed
Metadata SETS - table to organize groups of individual data into experiments or similar - flexible
SETS_Individuals join - table to relate individual metatdata to sets of that data. - flexible
Individuals can only be related to one accession, but could be related to mulitple Metadata SETS
These are (currently) related as outlined in the picture; SETS <-> SETS_Ind <-> Ind Metadata <-> Accessions.
Currently, I have a layout based on SETS that tries to show and summarize what is contained in the current SET. On this layout I have a portal to 'Accessions' to show the different accessions that are related to the current SET. On this portal showing accessions, I want a count of related metadata entries, so I can see how many from each accession are included in the current SET.
I've figured out how to get a total count of metadata for the SET (not broken down by accession) or I can get a total count for that accession accross all SETs, but neither of these quite fits the bill.
I've tried a bunch of different self joins and count fields from different perspectives targeting different things but haven't figured it out. Let me know if any more info is needed.