I am tracking the number of eggs harvested at 2 fish hatcheries and 5 fish species at each on a database.

Hatchery A
Species 1 No. of eggs harvested =
Species 2 "
Species 3 "
Species 4 "
Species 5 "
Hatchery B
Species 1 "
Species 2 "
Species 3 "
Species 4 "
Species 5 "


I want to do the following: If hatchery 1 is chosen, I want all following data to be collected in a sub-database called "hatchery 1 data"; if hatchery 2, hatchery data 2, etc.

In each hatchery sub-database I want a total of eggs of each species and a complete total of eggs of all species combined.

Then, I want a function that shows a grand total of all species at all hatcheries.

Thanks so much to anyone that will take the time to help.

Quebecor

Quebecor wrote:I want to do the following: If hatchery 1 is chosen, I want all following data to be collected in a sub-database called "hatchery 1 data"; if hatchery 2, hatchery data 2, etc.

Just to kick things off, I'd suggest that you'll get the most power and flexibility from your system if you design it in such a way that all the "harvesting" records are in one database.

So, I can see such a "harvesting" database containing records for each day's (?) harvesting where each record has the following attributes:

Date
Species ID
Hatchery ID
No. of Eggs Harvested

With such a structure, you could create a single subsummary report that displays all the information you requested. You'd have 2 summary parts ( one for the Hatchery and one for the Species) and a trailing grand summary for your "all hatcheries" total. A single summary field defined to count the "No. of Eggs Harvested" could be placed in each summary part (and the grand summary) to show you the total harvested for each hatchery and species.

These summary reports work well. If you're not used to these, search for "subsummary" in FileMaker's built in help to get an overview of what is involved.

There are a lot of other ways to set this up, but I would venture to say that all of them would share a "backend" database similar to the harvesting database I've sketched above, so that can be a good place to start.

HTH.

John

John,

Thank you. Your set up proved easy to build and functional, however, two issues arose after setting it up.

1) In my local menu option, no more than 25 choices will show, even though the database of available fishes is larger than that; is there a way to increase this?

2) I am having great difficulty making the calculating link where it takes all data on eggs harvested and gives me a grand total and a grand total for each species. I keep getting an error that says, (I paraphrase), "Cannot execute command as it would create a circular reference."

Thanks again for any suggestions,

Quebecor

Hi, When it comes to calcs, sometimes it is just too hard to explain the context

... if you'd like to email me the files ([email protected]) I'd be glad to take a look and post some suggestions.

Best,

John