Averaging multiple records

[acehkg]

I’ve just started using FMP, I’m teaching myself how to use it and I’ve hit a roadblock. I’m sure this is an easy question to answer, not sure why I can’t figure this out!

The database in question tracks results in races. The data is organized such that each runners performance in a race occupies one record. Each race has a reference# and each runner has a runnerID. What I want to do is have a script perform a calculation to see the average speed over the previous 3 races.

I created a new layout with runnerID and average speed fields. In my mind I envision that I need to write a script which when a runnerID is entered it will perform a find for that runners last 3 races. Is there any way to then pluck the data from the speed field of those 3 records, average them and then insert them in the average speed field?

Any hints or tips would be greatly appreciated!

[Fitch]

If you create a relationship of runner to race that is sorted descending, you could then make a calculation like this:

Average (

GetNthRecord( speed ; 1 ) ;

GetNthRecord( speed ; 2 ) ;

GetNthRecord( speed ; 3 )

)

[acehkg]

Thanks for that! I gave that a try. Is there no simple way to perform calculations across records? Maybe I'm just too use to excel where you can perform calculations across rows and columns....