10 Replies Latest reply on Jan 3, 2017 1:38 AM by jdbbirder

    Help with reporting just unique records across multiple tables

    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.

      John

        • 1. Re: Help with reporting just unique records across multiple tables
          Malcolm

          Have you used summary reports yet? They will enable you to produce a report that does what you want.

           

          The Sightings table combines data from Places and from Taxonomy, so it is the place to begin work.

           

          The combination of Place and Taxonomy creates an ID for a species at a place, regardless of the sighting date.

           

          Create a new report layout. Include a subsummary part grouped when sorted by TaxonomyTableID and put the species name, etc, here. Include  a subsummary part grouped when sorted by PlaceTableID and put the Place name, etc, here. Include a header part and put the most important fields for searching the sightings table here. Don't include a body part.

           

          Now, in browse mode, search for a date range. Let's say you search the SightingDate field for "*/*/16" to get all sightings for 2016. Now sort by PlaceTableID and TaxonomyTableID. You'll now have a list each species sighted at each place in 2016.

           

          Malcolm

          • 2. Re: Help with reporting just unique records across multiple tables
            jdbbirder

            Hi Malcolm,

            thanks for your suggestions. Yes, I have tried reports but I can’t quite get them to do what I want. However, I may be doing something wrong. I think part of my problem is that I don’t want to use the TaxonomyTableID itself that is actually saved in the SightingsTable, but rather another field from the TaxonomyTable (either the ClementsTaxNumber or the BirdLifeTaxNumber). I will have another try and see if I can get closer.

            John

            • 3. Re: Help with reporting just unique records across multiple tables
              siplus

              In a nutshell,

               

               

              Investigate the power of ExecuteSQL("SELECT DISTINCT <yourcode>...

               

              Set a global gDistinctIDList to the result

               

              create a relationship based upon gDistinctIDList = code and enjoy the related records.

              • 4. Re: Help with reporting just unique records across multiple tables
                beverly

                jdb, you might wish to post your find criteria (script) used for your report(s). perhaps it's simply a matter of the correct search and/or sort. (a sort can make all the difference in the world!)

                 

                beverly

                • 5. Re: Help with reporting just unique records across multiple tables
                  Malcolm

                  You can base the sub-summary parts on any field that you like.

                   

                  Subsummary reports work by displaying the sub-summary part when you sort on that field, and only when you sort on that field. The effect is that it provides a leading and/or trailing summary of the group of records matching the data. This is most commonly used to count/total things within the group, such as the number of birds sighted in June 2016, the total number of birds sighted in 2016 or the different types of birds sighted.

                   

                  The key, for your needs, is that when you sort by a sub-summary field ( the "break" field ) all the records are grouped by the data in the field. Typically this looks like this:

                   

                  Sightings: Group by Month, Species, Date

                   

                  June 2016   <-- subsummary part
                  - Silver Gull   <-- subsummary part
                  - - 12th June, Bondi   <-- body part

                  - - 16th June, Tamarama

                  - - 18th June, Coogee

                  - Lorikeet

                  - - 12th June, Bondi

                  - - 16th June, Tamarama

                   

                   

                  However, when you copy the layout and delete the body part, the same search and sort gives you this,

                   

                  June 2016   <-- subsummary part
                  - Silver Gull   <-- subsummary part

                  - Lorikeet

                   

                  As you can see, it is now a list of the species, ie, a unique list.

                  • 6. Re: Help with reporting just unique records across multiple tables
                    jdbbirder

                    Thanks everyone,

                    yes Malcolm, that worked pretty well and I am embarrassed I couldn’t get that to work properly before. I was able to get something pretty close to what I want as below

                     

                    Country      ClementsTaxNum          ClementsCommName           CountofSightings

                    Australia

                                   58                    Cassowary                         1

                                   61                    Emu                                   53

                                   72                    Magpie Goose                         13

                                   74                    Plumed Whistling Duck               5

                                   ……...

                    Brazil

                                   209                     Brazilian Teal                         5

                                   458                    Rusty-margined Guan               3

                    etc, etc

                     

                    Now I would like to be able to count the number of unique species for each country to be added as some type of extra sub-summary line. I tried adding another calculation after CountofSightings which was like

                     

                    1/GetSummary ( CountofSightings ; ClementsTaxNum) * GetSummary ( CountofSightings ; ClementsTaxNum )

                     

                    which just gave a 1 for every species (every row) then I tried to total all of those but I just kept getting a count of all total sightings (e.g. in the data above (1 + 53 +13 + 5) rather than 4). Any ideas if it is possible to just number the rows or easily count number of occurrences in each sub-summary part?

                     

                    Also I take it you can’t search for a specific text within a report. I was hoping to be able to generate these lists then look for specific birds by search (as these lists by country are more than 1000 rows long).

                     

                    Thanks again for what has already been a great help.

                    John

                    • 7. Re: Help with reporting just unique records across multiple tables
                      jdbbirder

                      Hi siplus,

                      Yes I will give the SQL distinct a try although I haven’t really worked out how to get it done properly in filemaker and output a list as a result. The database I am using I originally made in MSAccess and I could do all the distinct queries fairly easily. Lots of things work more easily in filemaker (especially linking and manipulation of bird photos) but I haven’t been able to replicate the distinct sql queries to output a list result. Also the times I tried to do an SQL distinct calculation in filemaker it took more than a few hours to complete! These aren’t massive tables (main two tables have around 15,000 records) so I couldn’t work out why it would take that long.

                      John

                      • 8. Re: Help with reporting just unique records across multiple tables
                        Malcolm

                        Question One: number summary parts with a serial number

                         

                        It would be nice to have a more direct way of counting groups. Perhaps someone can improve on my method.

                         

                        generating serials for break groups.png

                         

                        In this example replace id_Application with ClementsTaxNum. Place Sum Total GetSummaryAsFractionOfTotal onto your subsummary part. This will give you a number for each group and the total will be the total number of groups. It's only going to work when you sort by id_application, so it's inflexible.

                         

                        Question Two: Search a report

                        As reports are usually generated by a search this is a circular question. Yes, you can search. It simply produces a different found set. You then run the same sort routine to reproduce the subsummary view.

                        • 9. Re: Help with reporting just unique records across multiple tables
                          erolst

                          jdbbirder wrote:

                           

                          Hi siplus,

                          Yes I will give the SQL distinct a try although I haven’t really worked out how to get it done properly in filemaker and output a list as a result.

                          Try this: create

                           

                          - a value list based on the field ClementsTaxNum

                          - a summary field of type List of: ClementsTaxNum

                           

                          A list of unique species is

                          FilterValues ( ValueListItems ( "" ; "NameOfTheValueList" ) ; summaryField )

                           

                          and the count is

                           

                          ValueCount ( FilterValues ( ValueListItems ( "" ; "NameOfTheValueList" ) ; summaryField ) )

                           

                          jdbbirder wrote:

                          Also the times I tried to do an SQL distinct calculation in filemaker it took more than a few hours to complete!

                          To do this via ExecuteSQL( ), you must constrain the query to the found set - which is easiest using the primary key and (again) a summary field List of: primary key:

                           

                          Let (

                            arrayOfCurrentPrimaryKeys = Substitute ( sListOfPrimaryKey ; Char(13) ; "','" ) ;

                           

                            ExecuteSQL ( "

                              COUNT DISTINCT ( ClementsTaxNum )

                              FROM Sightings

                              WHERE primaryKey IN ('" & arrayOfCurrentPrimaryKeys & "')

                              " ; "" ; ""

                            )

                          )

                           

                          which needs a slightly different syntax (ie no single quotes) if your primary key is numeric, rather than textual.

                          • 10. Re: Help with reporting just unique records across multiple tables
                            jdbbirder

                            Thanks Malcolm.

                            The summary parts with serial number works really well. For me the sort by ClementsTaxNum will always be the way I want it so the inflexibility is no problem in this instance. Great.

                            John