AnsweredAssumed Answered

Help with reporting just unique records across multiple tables

Question asked by jdbbirder on Dec 31, 2016
Latest reply on Jan 3, 2017 by jdbbirder

Hi everyone,

I am looking for help on how to get FilemakerPro 14 (14.0.6) to produce a list (find?) that shows only unique values when three different tables are combined.


Apologies if the following has too much or too little detail.


I use my database (solution) to record all my bird sightings and the solution has three different main tables.

The first table is the TaxonomyTable, which contains all the birds of the world with fields for Common and Scientific names and Taxonomy numbers for every bird. The complicating factor with this table (and its best feature ☺) is that it combines multiple world taxonomies. This means that two birds might be recognized as one species by one taxonomy (so get the same scientific name and number for that taxonomy) but be recognised as two different species by another taxonomy (so get different scientific names and numbers for that taxonomy). An example of this would be the Red-billed Gull and Silver Gull. The Clements world taxonomy would have these as different species with Clements_TaxNumbers of 2406 and 2407, respectively. However, the BirdLife international taxonomy would have these as the same species so would both be BirdLife_TaxNumbers of 2588. The importance of these same or different numbers is that it should allow me to count my bird lists by either taxonomy.


The second table is the PlacesTable, which contains all the places in the world where I have seen birds. This contains fields for Country, State, PlaceName, GPS references, maps and place description.


The third table is the SightingsTable, which contains all the individual bird sighting information. This has the following main fields; SightingDate SightingTime, SightingLattitude, SightingLongitude, TaxonomyTableID (as the linking number to the TaxonomyTable for that bird) and PlaceTableID (as the linking number to the PlacesTable for the place where I saw the bird).


Now I record all sightings of all birds I have seen in the database so most birds will be in the SightingsTable many times (sometimes 100s). However, one of the things I want to do is produce lists (where I can also count the number of rows) of just the unique species I have seen in the world, or in a particular country or state. This means I have to combine the data from the three tables above and only show unique TaxNumbers (depending on which taxonomy I choose to use).


For example I might want to produce a list of all of the bird species I have seen in Australia. Thus, I need to combine the above three tables, firstly making a list of all bird records (from the SightingsTable) where country = “Australia” in the PlacesTable, then pull out just the first record of each specific Taxonomy ID (e.g. sightings with unique Clements_TaxNumber) from the TaxonomyTable. I have been able to do this to some extent by doing a search for all birds with country = "Australia" and then output the data. This outputted data is then re-inputted into a new single table with the Clements_TaxNumber field property set at unique only. However, this strategy requires output and input again so is rather laborious (I guess it might be possible to make a script to do all this automatically but I haven’t worked out how to do this either yet). Still I would rather a more straightforward report/find that can list the unique value across the multiple tables.


Thanks very much for any input.

Apologies if this is straightforward. I am still fairly new to Filemaker.