3 Replies Latest reply on Nov 8, 2013 11:05 AM by Stephen Huston

    How do I hide duplicate entries in a report summarizing survey results?

    wlegard

      Hi. I'm a biologist and I maintain a database of wild plants found in various parks. I'm attempting to recreate this Microsoft Access database in FileMaker Pro 12 Advanced, while converting it from denormalized to normalized. I love many of the FileMaker features, but I'm having trouble understanding how to hide duplicate entries in a report summarizing survey results.

       

      I'll first briefly illustrate my database and data, in case that is a limiting factor. (Note that I currently use "_kp" = parent key, "_kf" = foreign or child key, and "_id" indicates a generated serial number). Feel free to suggest improvements to my database structure, naming conventions, and data importing that may work better in FileMaker.

       

      Here's a simplified version of my database:

      Relationship.png

      Plant lists from surveys are initially stored in separate Excel spreadsheets. Here is how they are processsed:

      1) The list of plants from a survey are stored in the second column (name_scientific) of an Excel spreadsheet;

      2) A new SURVEY record is created and specific details are entered (survey_name, project, etc);

      3) The generated serial number (_kp_survey_id) is pasted into the first column of the spreadsheet; and

      4) A script is used to convert the "name_scientific" values into the appropriate "_kp_plant_id" values and import the information into the RESULTS table. Here's the first few rows of an imported spreadsheet:

      Excel sample.png

      Each record of my RESULT table has a unique serial number (_kp_result_id), a survey id (_kf_survey_id), and a plant id (_kf_plant_id) associated with a specific "name_scientific".

       

      Typically, multiple plant surveys are done in each park, and common plants appear in almost every survey. When I try to create a simple report summarizing which plants have been found in one park (using a drop down list with Find), I inevitably have some plant names repeated on multiple rows, plus the park name is repeated as well:

      Report duplicates.png

       

      What I want to create is a report that looks more like this:

       

      Park Name: Black Diamond Mines

      ------------------------------------------------------------------------------------------

      Scientific Name Common Name

      -------------------------------------------------------------------------------------------

      Achillea millefolium Yarrow

      Acmispon brachycarpus Colchita / Woolly Trefoil

      .... ....

       

      I've tried putting the park name in a "Title Header" and the scientific name in a "Leading Subsummary Part", but then I can no longer use "Find" to select a park (since subsummary parts don't display in Browse mode), and the resulting report is blank or a mess.

       

      I'm sure the solution is very simple, but many years of using Microsoft Access have apparently blinded me to the obvious. I assume I've skipped over some essential FileMaker Pro concepts in my rush to find the equivalent of Access' report "grouping".

       

      Should I attach a script to button that will prompt me for a park name (in a drop down list) and generate the appropriate report, if I can figure out how to do that. Or would it be easier to modify one of the Starter Solutions?

       

      Thanks in advance for your patience and any suggestions.

        • 1. Re: How do I hide duplicate entries in a report summarizing survey results?
          Stephen Huston

          One idea to remove duplicate rows:

          Just as you sort on park name, and, I assume, use a sub-summary part in your report, you can also use a sub-summary part when sorted on plant name, leaving out the body part of the layout completely.

           

          This will require a sort on both Park Name and Plant Name (secondary), which will yield a single entry per park name, with a single entry for each different plant name in the sub-sum parts below each park. Taking out the Body part removes the duplication from the report, and the readers won't know they are seeing summarized data anyway.

          • 2. Re: How do I hide duplicate entries in a report summarizing survey results?
            wlegard

            Thank you! That does the trick!

             

            In order to create a report for just one park, I placed a copy of the Park Name field in the Header so I can run a manual "Find" on that field with a drop-down menu (even though it's location is almost invisible).

             

            One additional question:

             

            When I 'Find' a park, the results in the Browse mode are empty until I manually click "Sort" on the toolbar to apparently trigger the sub-summaries. Is there an easy way to automate this Sort with some sort of a script trigger? Or should I put a non-printable button on the header and attach a script to display a drop-down menu and run the Sort?  Can you display a drop-down menu with a script?

             

            Thanks in advance....

            • 3. Re: How do I hide duplicate entries in a report summarizing survey results?
              Stephen Huston

              Yes, the records do have to be sorted in the correct order for the sub-sum reporting parts to work.

               

              You can setup a script trigger in the layout Setup to automatically sort them  for the report, in the triggered script, set a sortOrder dialog to keep them sorted in the order required for the sub-sums to display, and set the trigger to run " onModeEnter [ Browse]" in the Layout Setup dialog.

               

              That will trigger the sorting script when the found set appears in browse mode after any find on that screen. No user interaction required, no buttons, just does it.