Filtered relationship showing only first related value

[davidnickerson]

I have a bird database with 2 tables:  The Species table has one record for every bird species in California.  The Observations table has one record for every observation of each species.

 

So, for example, there may be 27 observation records of the "Western Bluebird" species in 2013.

 

I need a relationship that will give me a subset of the first observation record for each unique species.

 

I have an existing relationship that results in a single record per species, visible from Observations table.  This allows me to see a list (in a portal) of each unique species observed, but because the unique species name (1 species per record) comes from the Species table, it does not show me (for example) the date and location of the observation.  These fields reside in the Observations table.

 

So I need a way to get just a single observation record (the first) for each unique species observed.

 

I had thought this would be fairly easy, but I built several test relationships, and none are able to provide this result.

 

Any ideas?  Thanks in advance for your help.

[doughemi]

You need a relationship between Species and a new TO of Observations  Species::ID =  NewObservations::SpeciesID .  Sort the relationship by Observations::date (increasing).  Then put the fields you desire from NewObservations on a  Species layout.  Only the first record which fits the relationship will be shown in these fields (which is the earliest, because of the sort).

 

Now, to see only species that have been observed, create a calculated field in the Species table ObsCount = Count(NewObservations::ID).  Do a find for ObsCount > 0 to see only the observed species.