4 Replies Latest reply on Jul 6, 2010 4:31 PM by comment_1

Calculating the Best Five

Title

Calculating the Best Five

Post

I have a database that has up to 8 scores for every person.  I want to add up the best five scores for every person.  Does anyone know how to do this in FileMaker Pro 10?  I am not sure the script to make the calculations.

• 1. Re: Calculating the Best Five

How have you stored these 5 scores? In separate related records or separate fields in the same record?

While it's possible to construct a best of 5 calculation field for separate fields in the same record, putting the data in a set of related records is almost often the better approach due to the greater flexibility afforded through that design approach.

• 2. Re: Calculating the Best Five

My table is currently setup as follows:

Name, Tournament1, Tournament2, Tournament3, Tournament4, Tournament5, Tournament6, Tournament7, Tournament8, Best of 5 Score.

So it would be in the same record and a field for the best of 5.  Do you know how to make a script to calculate that?

Thank you for the help.

• 3. Re: Calculating the Best Five

Hi

try this calculation:

Let([
L =  Substitute ( List ( Tournament1 ; Tournament2 ; Tournament3 ; Tournament4 ; Tournament5 ; Tournament6 ; Tournament7 ; Tournament8 ) ; ¶ ; ";" ) ;
v1 = Evaluate ( "Max ( 0 ;" & L & ")" ) ;
L = Replace ( L ; Position ( L ; v1 ; 1 ; 1 ) ; Length ( v1 ) ; 0 ) ;
v2 = Evaluate ( "Max ( 0 ;" & L & ")" ) ;
L = Replace ( L ; Position ( L ; v2 ; 1 ; 1 ) ; Length ( v2 ) ; 0 ) ;
v3 = Evaluate ( "Max ( 0 ;" & L & ")" ) ;
L = Replace ( L ; Position ( L ; v3 ; 1 ; 1 ) ; Length ( v3 ) ; 0 ) ;
v4 = Evaluate ( "Max ( 0 ;" & L & ")" ) ;
L = Replace ( L ; Position ( L ; v4 ; 1 ; 1 ) ; Length ( v4 ) ; 0 ) ;
v5 = Evaluate ( "Max ( 0 ;" & L & ")" )
];
v1 + v2 + v3 + v4 + v5
)

N.B.: this calculation does not consider negative values and may fail with decimal values.

• 4. Re: Calculating the Best Five

> My table is currently setup as follows:

> Name, Tournament1, Tournament2, Tournament3, Tournament4,  Tournament5, Tournament6, Tournament7, Tournament8

That's not a good starting point; you should have a record in a related table for each score.