9 Replies Latest reply on Feb 13, 2010 12:25 PM by Marx

    Average generated for specific records



      Average generated for specific records


      Hi all, I'm trying to figure out how to calculate an average from specific records in a table and am having trouble determining how to solve this puzzle.  The situation is this: I have multiple nitrogen ("N") analysis results for the same livestock site ("SiteID"), but only want the average determined from specific records (and not all from a single site).  I created an "Include" field that would serve as a means of selecting the fields I wanted, created a self-join relationship, then wrote the following 'If' statement to determine my average:


      If (Manure Analysis Data 2::Include="Yes"; Average ( Manure Analysis Data 2::N );"")


      Currently, this calculation gives the me the average of all N values for the SiteID, which is not what I want.  I thought about limiting the records to only those that I want, but this database is for a client and I would prefer not to have to add another step to this process.


      For the record, I've done something similar to this using a summary report, but I need to be able to use the generated value in a portal; I believe will be especially important when I need to generate several N averages when there are multiple operations ("OperationID") within a "SiteID".


      Thoughts/shrieks of terror/dope-slaps are welcome.


        • 1. Re: Average generated for specific records

          If it were me, I would leverage the power of a summary fields (e.g. N_avg) by (as you have done) a self join - with the match field would be what we normally refer to as a multi-key (which is a text field to allow list of unique ids - usually a global field since it is for selecting records).


          Preparation - there is a unique id or serial number in every record, and you have built a self join from "multi-key field" (global) to the "unique id field" (which is a text field to allow list of unique ids) 


          method 1 - find the records you want to average, collect the unique id's in a list and set the global mult-key : all summary fields that have been created are then available for the found set as well as the self-joined relationships (you could have multiple groups of keys and all the various summary fields (sum, avg, min , max etc.) would report accurately.


          method 2 - user can toggle records in and out of the multi-key group by inserting or removing the record's unique id from the mult-key field list via button or whatever. 


          • 2. Re: Average generated for specific records

            Marx, Thank you for your reply.


            I'm doing my best to understand the multi-key concept, so could you let me know if I have it correct?  Here goes:


            Unique ID is a LabID that is associated with each record (that is, each SiteID can have many different LabIDs)


            Global key field: this is where I'm lost. Do I add a field to this table and identify the storage type as a global field?


            Establish a link between these variables through a self-join relationship.


            Once I've done this, I don't understand how to proceed.  I'm leaning toward your 'method 2' and would appreciate if you could add a little more detail to your previous message.


            Thanks again for your advice; I appreciate your contribution.


            • 3. Re: Average generated for specific records

              I can send a little sample file - do you have FileMaker advanced - I use custom functions and you would want to use them.  


              If not I can describe it more fully. 

              • 4. Re: Average generated for specific records
                   I'm using FM10 Pro.  Would the sample work on this version?
                • 5. Re: Average generated for specific records

                  Yes - I will make the quick version - and if you want to use the method - I can create a table based version of the custom functions that are used - or alternatively show you how to embed the custom function as a calc field in your database.


                  From where you are coming from you should consider upgrading to Advanced sometime in the future. 


                  I have to run to the store - it is snowing in TEXAS - I will upload a sample file later today. 

                  • 6. Re: Average generated for specific records

                    Thanks for the message.  I'm going to purchase advanced in the near future; working with Pro as a trial version (and like it so far, although I don't like that the sidebar is now anchored to the top of the screen...anyway...).


                    Send the file when you get the chance.  I'll be in the office until 17:00 CST and will check back throughout the day. Thanks for your help!


                    Have fun in the snow!

                    • 7. Re: Average generated for specific records

                      Example file can be downloaded from


                      Using MultiKey to Produce Summary Values Example File


                      Hope this shows the method and helps. 

                      • 8. Re: Average generated for specific records

                        Marx, Ok, I think I understand what you mean.  I'm going to try and implement the multi-key solution in a moment.


                        I had one other question for you: can you tell me which part of the lone script you have in this file is specifically for clearing the global field values?  I think I found which part is most likely related to the 'clear value' step that I want, but I want to be certain before proceeding with my own script.




                        • 9. Re: Average generated for specific records

                          The If statement that contains:


                          PatternCount ( $par1 ; "clear global fields" )