7 Replies Latest reply on May 21, 2013 4:18 PM by philmodjunk

    Average in scripts



      Average in scripts


           When I have a calculation field with code


           it actually calculates an average based on the relationship. This record is related another table occurance with multiple records (one to many)

           When opening a layout using this field it takes forever, so I want to make this calculation with a script instead. However, when running the script Average(field) returns just the content of the first record. Why?

           Does my explination make sense? how can I get the avarage of all related fields inside a script?

        • 1. Re: Average in scripts

               Two ways  that might help:-

               1) Instead of using Average(field) try using two seperate fields Sum(field) and count(field).

               2) Loop through the records via a script and create a running total and count of records.

               Not guaranteed - but they are things that I would try. Thanks





          • 2. Re: Average in scripts

                 Those methods work but they will take even longer to compute an average than: Average ( RelatedTable::Field )

                 You can look up aggregate functions in Help to learn why Average ( Field ) = Field

                 You could, however, define an "average" summary field. If you refer to it via the same relationship as you are using with the aggregate function, it will return the same value. Test it and let us know if that pops up your average more quickly. I've seen posts that claim that it is faster but haven't run any tests myself.

                 Another method would be to use Go To Related Records to pull up a found of these records first and then the Average summary field can return the same average.

            • 3. Re: Average in scripts

              When opening a layout using this field it takes forever


              This should depend on your computer speed, record count and your concept of "forever". 


              Looping throught a script is invoking FMP script interpreter and a Summary is at machine speeds.  In additon, careful on the Average calculation.  Its use is based on fields having a Value.  Test with empty fields to see if you obtain the Average Value expected.


              • 4. Re: Average in scripts

                     Intresting, thanks. I changed the script so the active layout is related to the records I want average on.

                     I also noted zero is the same as nil/null/not set, which is quite weird. Good thing pointing that out.

                     "Forever" this time was about two seconds. :) Now it runs in the background with no plopping progress window every time the layout is opened, so nobody notice anything.

                • 5. Re: Average in scripts

                       Actually, 0 should NOT be the same as Null values. Null values are omitted from these average calcaultions, 0 values are not.

                       Average ( 0 ; 1 ;2 ) = 1

                       Average ( null ; 1 ; 2 ) = 1.5

                       Note: My example is hypothetical not a valid syntax example.

                  • 6. Re: Average in scripts

                         BTW in my humble opinion, FMP and public in general, over uses the term AVERAGE and you must use it with caution for conclusions.  Here is an example.... I want the average age of my remedial reading class students

                         Records of each student's age....  7,9,8,40,10   = 14.8 surprise   OMG we have not taught students well. 15 yrs olds read at 8 yr old level.

                         Remove the 40 yr old from class and ... 7,9,8,10   = 8.5angel  Whew, not to bad....  the NULL case for the 40 year old.

                         You must use Standard Deviation to alert and inform.  Case 1 SD=14.3...    Case 2  SD=1.29 

                    Now throw in Phil's NULL point.

                         If you put a value of Zero [0]  for the 40 yr old since obviously he is "skewing" the results.  7,9,8,0,10   Avg= 6.8  SD=3.96

                         OK all.... which Average is correct?crying


                         PS:They all are!! However using Stand Deviations to show how wide spread your data is and the Distribution is key to conclusions.

                    • 7. Re: Average in scripts

                           Good point about standard deviation.

                           And what we are calling "average" is what mathematicians/statisticians call the "mean" there is also the mode, the median and mid-range values--all different ways to look at your values in aggregate.

                           And then there are "weighted averages" that FileMaker can also compute...

                           But in a very large number of cases, simply using the Average function or an Average summary field to compute the mean is all that we really need so long as we understand the difference between 0 and null in how the result is computed.