The easiest approach is to define an "average of" summary field. Since summary fields compute values over all the values in the found set (or all the values listed in a portal), you can perform a find to pull up just the records of just one type and the summary field will give you the average of just these materials. In filemaker 11 you can even set up a filtered portal to show just one type of assay and this same summary field will give you just the average of those records in the portal.
You can also create a summary report where you've sorted the records to group them by type. Then you can place this field in a sub-summary layout part and see a separate average for each group of records.
Here's a link to a simple tutorial on setting up summary reports that you may find useful:
Creating Filemaker Pro summary reports--Tutorial
Thank you for your reply. However, what I need is something that will account for the addition of new types of assays and display a single entry for each type of assay that applies to the compound along with the average of those related values in one portal. Is there a way to calculate an average for each separate type of assay in a portal, or would I have to make individual fields for the average of each type of assay?
Yes that should be possible, but it would help to know more about how you have structured your data.
Each new assay is recorded in a separate record?
Since you appear to want a portal such as:
Pb | Average of all PB Assays
Fe | Average of all Fe Assays
Au | Average of all Au assays
I'd suggest creating a table of all assay types the links by assay type to your individual assay records. Then the aggregate function: Average() can be used to compute your averages if you define this calculation field in this table of assay types. Average( Assays::ValueField )
Ah, I wish it were that simple. Let me explain. For each compound, a multitude of different types of assays can be performed. Multiple trials are usually done for each assay. So each new trial is a separate record with a field for what type of assay it is. What the portal needs to do is list the averages for each type of assay.
For example, the portal for Compound 1 would display:
Assay ABC | Average of all ABC trails on Compound 1
Assay DEF | Average of all DEF trails on Compound 1
Assay GHI | Average of all GHI trails on Compound 1
And there is not a fixed number of assay types, so I can't make table with fields for each assay.
The solution I gave you in my last post should work for you. You just have to establish a relationship that correctly matches the group of assay records you need to average.
In your example, you'd use two pairs of fields, compound and assay type so you average all trials of a given assay type and compound.
You'd have at least three fields in this table: AssayType, CompoundID, cAverage
Your relationship will be something like this:
AssayAvgs::AssayType = Assays::AssayType AND
AssayAvgs::CompoundID = Assays::CompoundID
cAverage would be defined as: Average ( Assays::AssayData )
Thank you! I've got it all set up and it's averaging correctly. Once again Phil, you've saved my life. :)