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

    Calculating the Best Five

    DiFi80211g

      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
          philmodjunk

          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
            DiFi80211g

            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
              raybaudi

              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
                comment_1

                > 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.