6 Replies Latest reply on Jun 29, 2010 12:32 PM by Modulo

    Conditional Average with Variable Conditions



      Conditional Average with Variable Conditions




      I've got a database in FMP11 where a user can enter data on biochemical assays into a portal. The data includes the type of assay it is, the trial number, and the results (also a number). I want a portal on that layout to show the averages of the results for assays of the same type, but as far as I can tell there is no average-if function. Also, a complication is that the different types of assays are not set– that is, the user can add a completely new type of assays by typing its name into the "assay type" field.


      How would I go about setting this up? Thanks in advance.

        • 1. Re: Conditional Average with Variable Conditions

          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

          • 2. Re: Conditional Average with Variable Conditions

            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?

            • 3. Re: Conditional Average with Variable Conditions

              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?

              If so....


              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 )

              • 4. Re: Conditional Average with Variable Conditions

                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.

                • 5. Re: Conditional Average with Variable Conditions

                  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 )

                  • 6. Re: Conditional Average with Variable Conditions

                    Thank you! I've got it all set up and it's averaging correctly. Once again Phil, you've saved my life. :)