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

    Average generated for specific records

    k1ngf1sher

      Title

      Average generated for specific records

      Post

      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.

      K

        • 1. Re: Average generated for specific records
          Marx
            

          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
            k1ngf1sher
              

            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.

            K

            • 3. Re: Average generated for specific records
              Marx
                

              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
                k1ngf1sher
                   I'm using FM10 Pro.  Would the sample work on this version?
                • 5. Re: Average generated for specific records
                  Marx
                    

                  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
                    k1ngf1sher
                      

                    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
                      Marx
                        

                      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
                        k1ngf1sher
                          

                        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.

                         

                        Thanks,

                        K

                        • 9. Re: Average generated for specific records
                          Marx
                            

                          The If statement that contains:

                           

                          PatternCount ( $par1 ; "clear global fields" )