AnsweredAssumed Answered

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

Question asked by wlegard on Nov 5, 2013
Latest reply on Nov 8, 2013 by Stephen Huston

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.

Outcomes