Ranking values

[Lemur]

Assuming that I have a record with a portal. In the portal are values from 5 users -- their names and their scores (which means, 5 record in the join table).

Is there a way to have the values RANKED - i.e., I'd like to have another field; calculation, I assume, that would 'rank' the values in descending order. I know I can pull out a MAX and a MIN value, but I'm looking for something similar to the "LARGE" function in Excel (where I can 'rank' the values from 1-5, with 1 being the largest and 5 being the smallest).

Is there a way to do this without scripting (or, with a fairly easy, quick-running script -- I guess I wouldn't be opposed to entering the data and then hitting a button to 'rank' the values)

Thanks!

Chris

[Søren Dyhr]

Why not sort them and then put a @@ next to the fields in the portal??

--sd

[Lemur]

Actually, I *was* able to do this with a calculation - albeit a fairly lengthy one.

In my related field, where I had "score", I then added three more fields - Score2, Score3 and Score4.

I created a field in the portal file, for Max_Score, which I could find easily, as well as new fields for Second_Score, Third_Score and Fourth_Score.

Then, in the related field, essentially, it said, if the player's score = Max_Score, make the score 0 in the Score2 field, otherwise, copy the original Score.

In the Portal file, Second_Score was a Max value of all the Score2 scores - since the true Max Score was now 0, the max Score2 score WOULD be the second-highest score.

And so on, for Score3 and Score4.

Thus ranking became a Case function - if score = Max_Score, make the rank 1, if score = Second_Score, make the rank 2, etc.

Takes care of ties as well, automatically!

Chris

[CoZiMan]

Or you could see adifferent solution here . .

http://www.fmforums.com/forum/showtopic.php?tid/174750/post/197167/hl//

:)