I've attached a mock database where I am trying to use table of occurrences to display a subset of the data in a portal. I can't seem to get the ANIMAL field and the DATE fields (highlighted in yellow) to show the correct data for the new occurrence tables. Instead they show only one value, presumably the first value of the table. Why would the portal on the left show each individual animal and each corresponding date, while the portal on the right show only the first value?

Sizes.zip

You must zip a file first before it can be attached.

Make sure that those fields in the right portal are pointing to the same table occurrence as their portal. If not that then the fields might be too high for the portal. Try moving the fields down a px or two. But I'd bet it's the first ... wrong TO; happens to me sometimes too. :-)

Sorry about the file, it was zipped but had the .fm12 before the .zip, didn't like that.

LaRetta, you are correct, some of the fields in the right portal were pointing to a different table that is related through a join table (see below). Can one not display the related field through the joined relationship? Is there any way to do that? By moving the fields down a px or two, is this in case the field is positioned outside the portal, is that what you meant?

My 3 tables are related as follows:

SURVEYS < INDIVIDUALS SIGHTED > CATALOG

One survey can have many individuals sighted, and a unique individual in the catalog can be seen on many surveys. Sometimes when an individual is seen it is sized, but not always. I'm trying to show a subset of all sized individuals (new occurance of INDIVIDUALS SIGHTED) based on:

Status = Always Sized

The Status field will show "sized or "not sized", the Always Sized field will always show "sized". The portal reflecting this subset of data works fine for the size field but I can't seem to get it to reach back to the SURVEYS table and show the proper date when that individual was sized. The reason this is important is because I eventually want to calculate growth rates between sizes (growth change / time elapsed between sizes). How do I get the new occurrence portal to point back to the SURVEYS table to grab the proper Date record?

One simple fix is to create a new calculation field in the INDIVIDUALS SIGHTED table that pulls the date from the parent table (I called it DateCopy). I'm guessing that's not the best way to do it but it seems to work. I don't quite understand why the occurrence relationship can't drill up and pull it in the same way. Maybe it's because it has to drill through a one to many relationship to get back to the SURVEY table?

Sizes2.zip

Another option is to just duplicate your original portal and filter the portal which eliminates the excess clutter of additional relationship and calculation. Filtered portals work well if the number of records isn't large. Since the RELATIONSHIP is already filtered down to a single animal, I shouldn't think the maximum number of sightings would be over 300-400 in its lifetime? It is a trade-off between graph clutter and speed.

So now you have two choices. Filter on the portal would be: Animals Sighted::Status = "Sized" and be sure the portal and all fields are also pointing at Animals Sighted.

Edited... Changed word 'portal' to more accurate 'relationship' as capped above :-)

By the way, you are sorting your original portal on its primary numeric serial (ascending). That isn't necessary since the natural sort order of a table is creation order incrementing that serial. Removing that sort will save a tad-bit of speed. :^)

Thank you for pointing that out. I have a follow up question that is somewhat related here (hopefully) and deals with doing subsummary reports on a subset of data from a table. I like the idea of creating a new occurrence of the table via a self-join relationship and then simply doing reports and summaries based on that occurrence. Not that I've been able to get it to work, but it sounds good in theory. Is this the way it should be done or is it easier/better to create scripts to do finds on the original table and then do summaries/reports based on that found set?

... or is it easier/better to create scripts to do finds on the original table and then do summaries/reports based on that found set?

When you first said you created self joins, I thought ... why? Yes, performing find is most flexible and it is just as fast as GTRR, in fact faster if using GTRR (match found set). :^)

Really I spoke too soon here, my apology. I can't say finds are better ... using another table occurrence CAN come in handy for preserving found sets (or other techniques). If you are creating the table occurrence simply to save from performing a find then in general that would not be optimum but if the TO already exists and you are more comfortable that way then it would be neglegible.

Scripts are more flexible and easier/faster to modify. Speed comparisons between the two approaches change through the versions so YMMV per version/updater. And we can put scripts in folders (they can be hidden & have small footprint) ... the graph just increases, adding to the clutter of every pop-up throughout the file. Each has pluses and minuses.