2 Replies Latest reply on Feb 27, 2017 3:38 PM by philmodjunk

    Score card


      Hi, I've been away from FM for quite some time and just returning to using it again, I'd forgotten just how much I love it, but the grey matter is a bit rusty. My questions is,


      I'm designing a database which needs to calculate a score from a weight field.


      It needs to sort the weight from highest to lowest and give a rank and then a score.


      So the biggest weight will be 1st with 100 points. Second biggest will be 2nd with 99 points, etc. Those not entering a weight will all get the rank after the smallest weight. They will get 20 points less than the smallest weight.


      Matching weights will get the same score and points, but the next weight will have to skip a place and a point.


      Hope that makes sense.



        • 1. Re: Score card

          Might be easier to help if you have a sample database/sample data to share.

          • 2. Re: Score card

            For the ranking, you can put each score in its own record and sort them. Then the following unstored calculation can return the rank;


            Let ( R = Get ( RecordNumber ) ;

                    Case ( R = 1 ; 1 ;

                               GetNthRecord ( R - 1 ; Weight ) = Weight ; GetNthRecord ( R - 1 ; Rank ) ;

                               Get ( RecordNumber )

                              ) // Case

                    )// Let


            For all but the "no weight" records, Score is 100 - Rank + 1