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.
Thanks in advance!