"Unique Values" relationship

[davidnickerson]

I had assumed this would be easy, so I must be missing something.

 

This is for a "birding" database (I'm a birder...make fun of me as you will).

 

I have an existing one-to-many with a  parent table ("dates") and a related table ("observations").

 

Each observation record tracks a sighting of a unique species at a specific date and location.  So, for example, I'll have the related portal showing 63 species sighted at a single date/location.

 

However, I'd like to also be able to show all the species seen at all the combined locations during a single day.  I've created a simple relationship that will show me all the DUPLICATE species records for that date, but can't seem to create one showing ONLY UNIQUE SPECIES for that date.

 

Make sense?

 

Here's a concrete example:

I visited 3 locations during a recent birding trip on a single day.  I have 3 records in "dates" that show each species seen at each of the 3 locations.  However, many species were seen at 2 or 3 locations, so my new related list will show, for example, "Great Egret, Great Egret, Mallard, Mallard, Mallard, Northern Mockingbird..." when I need the related records to be only "Great Egret, Mallard, Northern Mockingbird".

 

I can't use ValueListItems for this solution, because I need to be able to sort the related records various ways, and extract other data from them.

 

What am I missing here?

[comment]

Do you have a table of Species, where each species would be a unique record (and this would also be a source for your value list)?

[davidnickerson]

Yes

[comment]

Good, then make your portal (or List() function) look into this Species table (through the Observations table).

[davidnickerson]

Thanks.  I've done that.  However, if there are multiple records with the same species name in the Observations table, any relationship to the Species table still results in multiple records.

 

It seems that I would need to START from the Species table, where only I record exists per species.

 

I thought there would be a simpler way of achieving this.  Am I still missing something?

[comment]

It seems that I would need to START from the Species table, where only I record exists per species.

 

No, you need to END at the Species table. See the attached example:

SightedSpecies.fp7.zip