4 Replies Latest reply on May 19, 2009 7:07 AM by NTH23

    Need help creating a report that shows all of the entries in a field for a record

    NTH23

      Title

      Need help creating a report that shows all of the entries in a field for a record

      Post

      I have been organizing data in FileMaker for some time now, but recently have run into analysis problems.  Any help would be much appreciated! Here is a quick summary of the database I am working on:

      I am looking at certain species of animals and where they are located.  One table contains information about every species in the database: Scientific name and a unique species ID number.  Another table contains the field "Geography," which lists which continents they are located on.  So there are these options: North America, South America, Australia, Africa, Eurasia.  This table also has a field that descibes whether or not that certain species is endemic to one continent only: the only options for that field are "yes" or "no."  

       

      I want to create a report that lists every species in the database, and under that, each continent they are found on.  I thought I was doing everything correctly by creating a self-joining relationship in "Geography," but the report I have been generating repeats entries rather than listing them all.  For example, species Sus scrofa has Africa, Eurasia and North America listed in the Geography table.  Under the endemism field, it is labeled as "no."

       

      The report I created, however (after making 3 self-joining relationships between the Geography field) just lists Sus scrofa: Africa, North America, North America.  Why is it repeating one entry and not listing the other? How can I fix the relationships between tables so that I can generate a report that shows every unique scientific name and all of the continents it is found on under it?

      Thanks!

       

        • 1. Re: Need help creating a report that shows all of the entries in a field for a record
          philmodjunk
            

          Since one species record can be in multiple geographic locations and one geographic location can have multiple species, you need to implement a many to many relationship and that will require a join table. Basing your report on your join table will give you the report you have described.

           

          Species::SpeciesID--=--Species_Geo::SpeciesID   Where Species_Geo is your join table.

           

          To complete the many to many relationship you also need:

           

          Species_Geo::GeoID--=--Geo::GeoID.

           

          Use your join table as the reference table for your report table and you can also put all needed related data from the Species and Geo tables on the same layout. You can place the species name and other "specific" data in a sub summary heading and place the geographical data in the body.

          • 2. Re: Need help creating a report that shows all of the entries in a field for a record
            deltatango
               Can you email me a blank copy of the file? See my personal message for email.
            • 3. Re: Need help creating a report that shows all of the entries in a field for a record
              LaRetta_1
                

              You need a grouped report and not a relationship (if I understand correctly).  Create your report in the Geography table.  Create a columnar report with two fields:  SpeciesID and Continent.  Create one leading part based upon SpeciesID. Let FM create the report because it is important that it be sorted by Species.  After showing all records and sorting by the break field (SpeciesID), your report will look like this after going to preview mode:

               

              SpeciesID 001  SpeciesName <- leading part

               Australia <- body

               North America <- body

               South America <- body

               

              SpeciesID 002  SpecieName <- Leading part

               South America <- body

               

              ...etc.  The secret is to group by leading part based upon the SpeciesID in your Geography table, sorting by your leading part and then entering Preview Mode.  

               

              If you want this information in browse mode, you can easily accomplish it in your Species table by creating a calculation (result is text) with:

               

              List ( Geography::Continent) ... where the relationship is joined on SpeciesID

               

               

               

               

               

              • 4. Re: Need help creating a report that shows all of the entries in a field for a record
                NTH23
                   Thanks for all of the help, I will let you know if any of these solutions work!