4 Replies Latest reply on Nov 22, 2014 12:52 PM by kmclark210

# Calculating Standard Deviation based on 9 previous records

I am a software developer relatively new to Filemaker, using FM 12. I am building a solution that processes lab information, and one of my reports needs to "mimic" spreadsheet functionality in that it calculates Standard Deviation(Sr) and Control Limits for each record based on the average values of the previous 9 records. The formulas are straightfoward.

What I need help with is the best way to "grab" values from the previous 9 records in order to perform the calculation.

So far, I have considered:

1. A script trigger to "Find" last 9, calculate the Sr and insert the value

2. A duplicate table "Sr Reference" that is always updated to hold only the last 9 records for calculations

3. Value List (maybe same idea as #2)

4. Calculated Database value (but don't know how to get last 9 in expression)

I'm hoping there is a less clunky solution than what I have listed above, and I'd appreciate any ideas that FM developers are willing to share.

• ###### 1. Re: Calculating Standard Deviation based on 9 previous records

use getNthRecord ( Table::Field ; get (RecordNum) - 9 ) to fetch the 9th previoust record etc ..

1 of 1 people found this helpful
• ###### 2. Re: Calculating Standard Deviation based on 9 previous records

You could create a text calc field, unstored, where

Let (

i = Get ( RecordNumber ) ;

GetNthRecord ( primaryID ; i - 9 ) & ¶ &

GetNthRecord ( primaryID ; i - 8 ) & ¶ &

GetNthRecord ( primaryID ; i - 7 ) // etc.

/* Case ( haveFMAdvanced ; writeRecursiveCustomFunction )

does not work with List() */

)

)

and use that field for a self-join relationship (YourTable::cLastNineIDs = YourTable_lastNine::primaryID).

This gives you direct access to the previous 9 records, and thus any fields you wish to aggregate.

• ###### 3. Re: Calculating Standard Deviation based on 9 previous records

Thanks FileKraft.  That is the function I need.

• ###### 4. Re: Calculating Standard Deviation based on 9 previous records

Thank you erolst.  Very cool!